Regression analysis is a statistical technique to assess the relationship between two or more variables. In business, you can use it to help prove whether adjusting one variable is likely to impact another e.g. if we invest in more training will our staff become more productive? This article won’t go into the detailed mathematics behind the Read More …
Month: January 2016
Excel pivot table only showing top 10 values
You might want to pull in a select number of values from your pivot table into your dashboard report i.e. the top 5, top 10, bottom 10, etc. To do this as a one-off is simple, but to do this dynamically (so the dashboard updates the top 10 as the data changes in the background) Read More …
Why are my Excel columns showing as numbers and not letters?
This is a common query that comes up, when an Excel option has been tweaked or you’re using someone else’s computer and the column headers are set as 1, 2, 3 instead of A, B, C. This doesn’t do any harm, but does make writing formulas a bit more confusing. Assuming you want to change Read More …
How to remove duplicates in Excel
This is another one which is easy to do in Excel when you know how, you don’t need to write any formulas because there’s an in-built function for you to use (provided your installation is Excel 2007 or later). In this example you have a list of all the James Bond films with some Read More …
Excel VBA check whether multiple cells selected
There might be several reasons why you want to check whether multiple cells have been selected by your user, but here’s one example of a survey where we ask the employee which office they are based in. You can use VBA to check whether the user has clicked a cell in the tick Read More …
Stop Worksheet Activate code running in Excel
As you’re building together your workbook, you might be in a situation where you want a worksheet’s activate code (or any other worksheet code for that matter) to run in some cases but not others. For example, if you’re navigating from the main menu page you want it to run, but if you’re just briefly visiting the Read More …
Excel found unreadable content in the file
This is an error that may pop up after you’ve pasted or formatted some cells. It is usually because you have used up the maximum number of different formatting combinations Excel can handle, which probably means you’ve spent a long time building a very complex workbook. So you might find yourself in a bit of Read More …
Aligning objects in your Excel dashboard
Your dashboard will typically have a number of different objects, maybe some text boxes, shapes and charts, etc. As you’re building it up and making changes if you find it’s taking lots of manual time lining everything up, you may find this tip useful. In this simple example, we just have two charts we Read More …
Format Excel cells which contain specific text
This is a fairly easy one to do, because you can format cells to contain a specific set of characters or words using Excel’s in-built conditional formatting. Instructions 1) Highlight the relevant range of cells to format. 2) Go to the Home tab, then within the Styles section of the ribbon drop-down Conditional Formatting and select Read More …
Removing zeros from data labels in Excel charts
When you add data labels to your chart, zeros will show up by default. This can look quite ugly in some cases, particularly on pie charts. There are a couple of solutions to remove the zeros. Option 1 Replace the zeros from the source data with blanks If your source data is currently the Read More …