To stop users from unhiding sheets which they shouldn’t be accessing, hide them using VBA and they can only be unhidden using VBA. In this example we have a Summary sheet and a Calculations sheet. We don’t want users meddling with the Calculations sheet. You can right-click the sheet tab at the bottom Read More …
Month: February 2016
Reverse the axis order on an Excel bar chart
Here is a basic example of some sales figures by quarter and what happens when you insert the data into an Excel bar chart. You’ll notice that despite your data being ordered sequentially by quarter (Q1 to Q4), the chart has automatically reversed the order when it is being represented as a Read More …
Why is my Excel formula being treated as text?
Despite the = equals sign being at the beginning, is Excel still not reading your formula as a formula? This can happen occasionally. The reason is usually that the destination cell (in this case A3) has previously been explicitly formatted as text. Then when the formula is manually typed in, Excel continues to Read More …
Removing E+11 E+12 values in Excel
You may have seen numbers displayed as E+11, E+12, etc in an Excel cell, this is because Excel cannot display numbers with more than 12 characters. That includes fractions where the decimal places take it beyond 12 characters. With the cells in General format, these are displayed as scientific notation with the beginning of the Read More …
Date differences using the DATEDIF function
The DATEDIF function allows you to represent the difference between two dates in various formats. It has three arguments for you to enter; the start date, end date and units you are counting in. E.g. =DATEDIF(A1,B1,”D”) will return the difference in days between the values in A1 and B1. Here are some examples Read More …
Rounding up and down in Excel
There are some simple formulas in Excel for rounding your results to the nearest decimal, and I’ll also cover the functions which enable you to round to the nearest multiple. ROUND, ROUNDUP and ROUNDDOWN round to a specified number of decimal places. In the below example we are firstly rounding to the nearest whole number, then Read More …
How to create flow charts using Excel
If you want to do the really creative stuff you’ll need to buy a copy of Visio but PowerPoint, Word and Excel are more than capable for the majority of flow charts. Go to your Insert tab in the ribbon and select SmartArt from the Illustrations menu. You will be presented with the following menu: Read More …
Run Excel macros on specific dates days and times
There are a few fairly simple bits of code you can use to delay Excel VBA code from running, or choose for it to run only on particular days or particular times. Maybe you are kicking off the Excel macro as a Scheduled Task, or maybe it runs when a button is clicked. If someone Read More …
Using the Camera tool on your Excel dashboard
The Camera tool is a little known Excel function which can be helpful in several situations. Probably the most obvious is on your dashboard report. You’ve been asked to make some changes, add something new into the dashboard, but it’s a nightmare because you have to spend hours adjusting all the column widths and merging cells Read More …
Unpivot data using Excel Power Query tools
Here is an example of how you can use Power Query tools to ‘unpivot’ columns of data to make them easier to report from. This is the kind of thing that would have been quite time-consuming to do in Excel before the ‘Power BI’ era, either by manually repeating steps or having to spend time writing and Read More …