Excel speed up tricks – part two: analytics productivity

PivotTables

PivotTables are the quintessential analytics tool (select data > Insert tab > PivotTable and then drag and drop using the PivotTable report builder). If you’re new to PivotTables, then you can learn how to make them via the video below.

For this article, I’ll fast forward how to build Pivots and show some productivity tips.

1. View Pivot field and areas side by side: This two-click setting should be done by every laptop user. PivotTables were first launched for desktops – big screens meant screen real estate wasn’t important. But now we have laptops, this view is far superior.

2. Stop the columns resizing when you click refresh: Click on your PivotTable > Analyse tab > Options > Uncheck “Auto-fit column widths on update” (as per the image below).
3. Stop getting =GETPIVOTDATA: When you type = and click to link a cell in a PivotTable, you often get something like =GETPIVOTDATA("Sales",$A$2,"City","Glasgow","Sponsor","Apple"), instead of a normal cell reference. But this is a setting easily changed. Click File > Options > Formulas and uncheck the box that says “Use GetPivotData functions for PivotTable references”.
4. Save default Pivot layout: If you always find yourself creating a specific style of PivotTables (eg, no subtotals, green base, report layout with “repeat all rows”), then you can go to File > Options > Data > Edit Default Layout and fine tune. (This is available for Office 2024 or more recent.)

Quick PivotTable creation

5. Pivot suggestions: Select the data > Insert tab > Recommended PivotTables, and Excel suggests some ideas for Pivots you may want to create.

6. Analyse data with AI: By clicking Data tab > Analyse data, Excel will use AI to suggest ideas. They are better than “Recommended PivotTables”. This feature was released five years before Chat GPT and Copilot, so it’s less sophisticated. Still, it’s great to quickly get results. There are three types of charts that appear:

   1. Blue results will populate PivotTables.
   2. Orange results will have insights from PivotTables (eg, outliers, majorities etc.).
   3. Grey results will not be PivotTables, but frequency distribution through histograms.

   The search bar above can create Pivots from prompts.

7. Ask a question about your data via Analyse Data: Using the same command, you can ask questions such as “Top 5 cities by sales in Scotland in a bar chart”. This takes four instructions: to return a city by sales visual, that has filters for only Scotland, and for top five, and returns it in a bar chart.

This video shows how to ask questions with Analyse Data:

Charts

8. Paste Special in charts: When you have a preferred chart format, you can copy that chart then click on another chart and choose Paste dropdown > Paste Special and click “Formats”. You can alternatively Paste Special > Formulas, and maintain the source formats. One would expect this to work via the format painter, but it doesn’t. From my experience, it’s a bit buggy with what it pastes and what it doesn’t.

9. Save chart template: After adjusting a chart in a style you like, you can right-click it > Save as template. Retrieve your chart templates across files by clicking Insert tab > Recommended charts > Templates.

10. Press delete on your keyboard: Click an item you don’t want and remove it by clicking “Delete” on your keyboard.
11. Chart preset layouts: Click Chart Design and choose a style.
12. Alternate chart colour palette: Click Page Layout > Colours to amend.

The charting tips shown above (9-12) are showcased in this video:

13. Column chart shortcut: Create a vertical column chart by selecting your data and clicking F11. This creates a special chart page in your file.
14. Align and distribute multiple charts: Select Charts > Shape Format > Align, and explore the options.

15. Get charts (and objects) to not resize when adjusting column widths: Select charts, right-click and select “Don’t move or size with cells”. If you right-click one chart, select “Format chart area”, then click on the sizing icon on the top right before going to Properties.

Filtering

16. “Ctrl Shift L” to filter: This toggles on/off filters from anywhere in the table. It’s a very fast way to remove many filters. Here are companion shortcuts:
    1. Once the filters are on, press “Ctrl up arrow” to jump to the filter location.
    2. After you’re positioned in the filter cell, press “Alt down arrow” to open the filter.
    3. Press “E” to jump to the search box.
    4. Press “Spacebar” to check or uncheck a box.
    5. Press “Enter” from anywhere for “OK”.
    6. Use arrows to move the cursor (but note: with date filters, it acts funny sometimes).

17. Have entirely blank rows above your column headers: This ensures filters go in the right place. If you don’t have this, filters will jump to a higher row, usually row number 1.
18. Filter by a specific cell’s value: Right-click a value > Filter > Filter by Selected Cell’s Value. There are other less useful filter options on that menu, as well as sort options.

19. Use =FILTER: This is a new function that allows you to create a table that follow filter criteria.

In cell charts

20. Create in cell bar charts: Select the data > Home > Conditional formatting > Data bars.
21. Create in cell heat maps: Select the data > Home > Conditional formatting > Colour scales.
22. Create in cell icon sets: Select the data > Home > Conditional formatting > Icon sets. Please note: for these, you’d usually need to set the limits by clicking Home > Conditional formatting > Manage rules > Edit rule, and type in which numbers are the limits where the icons switch.
23. Create in cell charts with Sparklines: Select the data > Insert > Sparklines > Line. Select the cells your chart should appear in after. A few things to note:
    1. By default, Sparklines will compare just for that row. You can change this by clicking the Sparkline then Sparkline tab > Axis. But the default is usually what works for me.
    2. If the source data is hidden, you can click Sparkline tab > Edit data > Hidden and empty cells, and amend.
    3. Sparklines can be column, win/loss and line charts, but I personally find it works best with lines.
    4. You can choose to have markers at all points, highest or lowest etc.

This video showcases the types of in cell charts:

Conclusion

There are various ways to speed up your analytics processes – this series will return in May 2026 with another article on productivity tips.