If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Category Archives: Data
Running Total in a Formatted Table
Using the SUM function
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Advanced Filter Keyboard Shortcut
To open the Advanced Filter dialog use Alt A Q pressed in sequence, not held down.
Free Excel Webinar Recording – What If Techniques
Feedback score 92%
My free Excel webinar for September 2018 covered What If Techniques. Download the materials using the button below and watch the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Content applies to Excel 2010 and later versions. You will need to install the Solver Add-in – instructions in the manual and video.
- Goal Seek – simple what-if changes
- Solver – advanced what-if analysis
- Scenario Manager – handling different sets of inputs
- Data Tables – single and double variable sensitivity analysis
- NEW – Forecast sheet
Related Tables and PivotTables
Don't Panic!
When you are building a PivotTable based on two related tables you may see unusual layouts that don’t make sense. Don’t worry, when you add values to the table all will be fixed.
Power Query
Its always great to receive e-mails like this. Power Query is a game changer.
Import PDF Update
The Power Query engine keeps getting better.
Hopefully soon it will handle PDF documents.
As at August 2020 PDF import is live in the subscription version.
Copying Without Affecting Relative References
Works for Excel 2013 onwards
There is an easy technique to copy a single formula and paste it without affecting relative references but what if you wanted to paste lots of formulas?
An Easier Step Chart
A Line chart solution
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Sequential numbers in a filtered list
A formula solution
Let’s say you have a filtered list and in each of the filtered cells you want to enter a sequential number, but in the hidden rows you don’t want to enter anything. There is a way, but it takes a few steps.
Vote No for Gauge Charts
They take up too much space
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
Inserting a Blank Row Between Entries
Manual technique
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy.
Find the first and last dates in a filtered list
Super power to the rescue
Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you.
Entering Headings via a Macro in Excel
Handle a range in one go
Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
Easy Financial Year Formula
To get the Australian financial year from a date you usually use an IF function based on the month number.
I recently learned a new hack from Matt Allington of Exceleratorbi.
You can add 184 to the date and then use the YEAR function. See table and formulas below.
The formula in cell B2 is
=YEAR(A2)
The formula in cell C2 is
=YEAR(A2+184)
Both formulas have been copied down.
A simple solution to a frustrating issue. Thanks Matt.
PivotTable Combinations Trick
How to get a list of combinations
I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable.
Excel and Outliers
New functions make it easier to find them
Malcolm Gladwell’s book Outliers is a great read – I reviewed it here. Its premise is that some outliers (events that are far outside “normal” expectations) have causes and hence are worthy of investigation. Excel have some functions that can help identify outliers in your data.
PivotTable Grouping Doesn’t Work
Rogue data the reason
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Identify Milestone Years in Excel
MOD function to the rescue
Recently a client wanted help in summarising a large data list of employees. They wanted to identify the years of service in terms of 5, 10, 15 years and other milestone years based on a start date.
Selecting a column
To quickly select a column of data in a formatted table you have a couple of options.
Keyboard
Select a cell in the column and press Ctrl + Space Bar.
This will select the column of data. If you want the heading too, press it again.
You can also select multiple columns before using the shortcut.
Mouse
This technique can take practice if your headings are in row 1.
If the heading starts in row 2 or below it is easier. See image below.
If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.
When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.
The image below is the correct arrow – this will select the column in the table only.
In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.
Checking Rounded Totals in Excel
Another SUMPRODUCT technique
Rounded values in Excel can pose a few issues. There is a formula you can use that can round a range of values and then SUM the results. This can be used as a check total for rounded values.
Excel Protection Tips
Letting users make input entries
When you are setting up a protected sheet that allows users to make inputs, a few option changes can make the user’s experience a lot easier.
Power Query and Open Excel Files
Old school solution
Power Query can easily combine data from multiple Excel files. A problem can arise if one or more of the files is open. Power Query will generate an error and the import will fail. The solution involves an old school Excel feature.
Entering Date and Time in Excel
There are shortcuts to enter
- the current Date Ctr + ;
- the current Time Ctrl + Shift + :
There isn’t one to enter both.
You can use them in sequence to achieve a date and a time entry.
In sequence press
Ctrl + ;
Space bar
Ctrl + Shift + :
The space separates the date and time and Excel will recognise the entry as a date and time.
Another date solution
Formula to the rescue
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.
Creating a table of all combinations in Excel [Video]
Power Query solution
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.
Filtering blanks made easy
In Excel the “Blanks” option is usually at the bottom of the list. This slows down selecting it.
If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it – see image below.
But the word “Blanks” is searchable, so if you type b in the Search box – your work is done – no scrolling required – see image below.
If your column contains text you might need to type in bla.
Copying a Date Down a Long List in Excel
Two techniques make it quick and easy
There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.
Handling DR and CR at the end of numbers in Excel
Sorting out debits and credits
Some systems add DR and CR to the end of numbers when they export into Excel. This renders the values useless for normal calculations. You can use data cleansing techniques to remove the characters using formulas or Power Query. There is one function however that can perform calculations on these types of entries.
Hide and Seek with Excel Charts
Hiding rows and columns
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.