Pivot Table Shortcuts
Here’s a couple of useful keyboard shortcuts for Pivot Tables.
Display/Hide the Pivot Table Field List – this list lets you create or change the Pivot Table.
Alt J T L – pressed in sequence, not held down.
To add Subtotals above the entries in an existing Pivot Table.
Alt J Y T T – again pressed in sequence, not held down.
Note sure why, but Pivot Tables are often seen a “hard” or “advanced”.
In the short video we see how easy they are.
Oops – I go over my one minute time limit by a few seconds because I format the Pivot Table as well.
This short video covers different ways to insert a drop down list into a cell.
I go over my one minute time limit by a couple of seconds, but I do cover three techniques.
Learn about Excel’s powerful Table feature that treats tables like little databases.
I ran this free live webinar in March 2021.
You can access the pdf manual and example file by clicking the button below the video.
In this short video I cover how to increment dates in long ranges.
It uses a little know dialog.
In my previous post I mentioned you should, as far as possible, keep data together in a single table rather than splitting it up between sheets. If you want to split it up for distribution purposes here is an easy way to do it.
In this short video I cover how to insert dates in long ranges.
Its simple and quick.
I have received a few questions recently relating to working with data spread across multiple sheets. In general, if the data is in the same layout, keep it in one table.
In this short video I cover how to insert automated subtotals.
Its a built-in feature, that is easy to use.
In previous videos I worked with duplicated entries.
In this video I demonstrate how to extract unique entries from a list.
Three different ways in 60 seconds.
Off we go!
Yes, you can create a cell drop down without Data Validation. It uses a built-in technique and is flexible.
Imported data often has missing entries you need to populate.
You can use Power Query, but that duplicates the table.
This technique works on the existing table and is quick and easy to apply once mastered.
Start the clock!
In the previous video I removed duplicates, in this video we identify duplicates using Conditional Formatting.
I identify the duplicates twice in a minute in this video.
The clock is ticking.
I have started a new series of short videos showcasing Excel’s features that work in less than a minute.
There is a countdown to see if I can do it.
In this first one I remove duplicates in less than a minute.
Sometimes Slicers seem to have a long memory and list entries that are no longer in the current data set. There is a setting to fix this.
When you transform data using Power Query it will often automatically promote the headers (use first rows headers) and guess the data types for each column. You can stop it doing this.
When working with some large data sets in Power Query it may be useful to default your Close and Load option to Connection Only rather than a Table. That way if you do strike an issue it won’t take ages to load to data.
Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post.
Slicers are a great filter interface. Sometimes, due to layout restrictions, you prefer the slicer to go across the sheet rather than down the sheet. Here’s how you do that.
Restart a Pivot Table in one go
If you want to clear all the fields from a Pivot Table you can use the clear all option.
On the PivotTable Analyze or Analyze tab click the Clear drop down and select Clear All.
This removes all the fields and allows you to start again from scratch.
Macro question – Slicer solution
Slicers are a very flexible filtering option and can control multiple pivots – even power pivots!
Always happy to help.
Excel has a right click Filter option that speeds up filtering by a single value. You can hack that shortcut to do a little bit more.
In March 2020 I presented a session on Excel’s Format as Table Feature. I covered it’s advantages and how to use it to improve your Excel files.
The detailed pdf manual and example file can be downloaded using the button below. Content is listed below 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.
In this session you will learn all about Excel’s formatted tables.
Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.
Formatted tables can allow you to create powerful reports like those in a relational databases.
advantages and limitations of formatted tables
using formatted tables with formulas
solutions to some of the limitations of formatted tables
using range names with formatted tables
using formatted tables with data validations
creating a running total
Relationships (Data tab)
As always I will share a few other tips.
In my previous post I showed how to have a flexible data validation and how to validate it. This post will look at adding conditional formats to inputs and validations.
The most common type of Data Validation in Excel is a drop down list. In the example below I allow the user to select a year, then a month (using a drop down) and then enter a valid day in the month.
The default setting is to have the grouping icon below the grouped rows. But you can switch things and have the icon at the top of the grouped rows.
Once you start to use Power Query you may find yourself with quite a few queries in the one file. To make it easier to control them you can use groups to keep similar queries together.
Did you know you can sort by colour in Excel? Did you know you can sort ascending or descending within that colour? I was asked a question in a recent webinar and in answering I found out that you can sort within a sort.