35 shortcuts & tricks to make you an #AWESOME Data Analyst

Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.

So today let me share with you 35 shortcuts, productivity hacks and tricks to help you be even more awesome.

Write better, faster formulas

Writing formulas is a big part of analyst life. Use below tricks to cut the time you spend writing Excel formulas.

1. Use F2 key to edit any cell with formulas. This will put the cursor right the end of the formula.
2. Exploit intellisense:  Whenever you are typing a formula, Excel shows a list of possible functions / names that start with the same few letters you have already typed. Once the list is small enough, you can use arrow keys (up / down) to pick the function or name you want and press TAB to let Excel type the thing for you. This will dramatically speed up your formula writing process.
3. Corner click to auto-fill: Once you have a formula, chances are you want to fill down that formula for rest of the table / range. To do this, just select the formula cell, double-click at bottom-right corner of selection. Bingo, Excel will auto-fill the formula all the way down (as long as there are values in adjacent columns).
4. CTRL+Enter to type same formula in a bunch of cells: If you want to have same formula applied to a bunch of different cells, just select them all and type the formula. This will place the formula in top-left cell of the selection. Now, instead of pressing enter, press Ctrl+Enter. Excel will place the formula (and adjust any relative references) in all the cells.
5. Debug portions of the formula with F9 key: When working with long formulas, often we come across situations when the result doesn’t make any sense. You can debug portions of such long formulas using F9 key. Just select the formula portion and press F9 to see the corresponding result. Once you are sure about the result, press ESC to revert to original formula.
6. Write plain English formulas with structural references: Use tables and structural referencing to turn your =A2+ B2*C2 + D2 to [@[Fixed Cost]] + [@Units]*[@[Variable Cost]] + [@Commission] and make your workbooks readable (and maintainable). To convert your data to a table, press CTRL+T. Read more about structural referencing in Excel.
7. Setup calculations top-down: If you have a big workbook with heaps of calculations, then set up your formulas from top down, such that formulas below refer to cells / calculations above. This speeds up workbook calculations and makes it easy to maintain.

Related: More formula shortcuts

Pivot table productivity tricks

Pivot tables are a big part of analyst’s life. Use below tricks to work faster with Pivot tables.

8. Use ALT+N V to insert a pivot table quickly. Or you can use the old school shortcut (from Excel 2003 days) – ALT + D P
9. Double click any value to drill down: When looking at pivot tables, if you want to know which records correspond to a particular total, just double-click on the number. This will show a new sheet with only data for that number.
10. Rearrange your pivot table items by drag & drop: Want to see a particular product name on top? Want to see the department list in a certain order? No problem. Simple select the items and drag and drop them in any order you want. This will re-arrange the pivot report the way you want.
11. Sort quickly with right-click: Sort your pivot reports by simply right clicking on the value field and choosing sort option.
    
12. Make Pivots presentable by renaming fields: Do you know that you can type over the column / row headings in pivot tables? This makes them presentable and tidy. Give it a try. Read more about the tip here.
13. Master GETPIVOTDATA to make better reports: Pivots are very powerful, but they carry a lot of baggage. If you want to harness the calculation might of pivots, but still use a friendly format in the output sheets, use GETPIVOTDATA. This can lookup in to pivot tables and give values you want. See below demo and  Learn all about GETPIVOTDATA today.

Related: Pivot table tricks to make you a star at work

User interaction hacks

A good analyst must create user-friendly workbooks because a great deal of the job involves communicating with users. This is where ideas like data validation, form controls & slicers come handy. Here are few hacks to deal with such things faster.

14. Multi-select slicer items by dragging: To select multiple items on a slicer, simply drag from first item to last. If the items you want to select are not together, hold down CTRL key and click on one slicer button at a time.
    
    
15. Set up form control linked cells faster: To set up the linked cell for a form control, simply select the control, click on formula bar, press = and click on the cell you want to link. Done!
16. Cut and paste: When setting up a complex workbook model, usually all the calculations are done in a separate worksheet tab. To speed up the process of setting up user interaction elements (such as slicers or form controls), first set them up in the calculation sheet. Once everything is working as per plan, just cut and paste them to the output sheet.
17. Alt + Down arrow to pick items from a validation / filter list: Use ALT + down arrow key to pick items from a data validation drop down or filter cells.
18. Quickly clear filters with these shortcuts: On a table or list, use CTRL + Shift + L to clear the filters or toggle them. On a slicer use ALT + C to clear the filter (ie select all).
19. Use timelines to filter date values: Introduced in Excel 2016, timelines are a cool new way to filter date values. You can insert a timeline from Analyze ribbon or by right clicking on date columns in your pivot table filed view.
20. Set up hyperlinks to various parts of your report: If you have a big report with many tabs, consider adding some hyperlinks so users can navigate easily. You can create a hyperlink from a drawing shape or cell. Use shapes or images for best results. Set up your shape, then press CTRL+K to open insert link box and select the sheet or range name to which user should go. Your hyperlink will be ready. Read more about hyperlinks.

Charting done efficiently

A good chart may get you that hike. So it’s no wonder we, analysts spend a lot of time working on charts.

Here are few tricks to work with charts efficiently.

21. Use arrow keys or TAB to select individual chart elements: When working with charts, we have to select a chart element (bars, columns, titles, axes, legend etc.) before doing anything to it. To quickly select a chart element, simply activate the chart and use arrow keys.
    
22. Adjust chart’s source data with drag and drop: If you want to change a chart’s source data, simply use drag and drop. Select the chart series (for ex: in a line chart, select the line you want to change). This will highlight the source data range. Now using mouse pointer simply drag and drop the highlighted box to wherever you want. Done!
23. Use the select objects tool: When working with multiple charts, often you may want to adjust settings for all in one go. Wouldn’t it be great if you can draw a box containing all charts and everything gets selected, à la Power Point (or image editing software)? Well, you can do that in Excel too. Simply activate select objects tool from Home > Find & Select > Select Objects.
    In fact, I suggest adding this tool to quick access toolbar (right-click on the select objects tool and choose Add to quick access toolbar) so that you can fire it up when you want.
24. Link chart title etc. to cell value: Default chart titles can be lame and boring. Create awesome titles (subtitles, captions etc. too) by using formulas. Then link them to chart title by using this simple trick. Select the title (or any other element), click on formula bar, press = and click on the cell containing your new title. Bingo, your chart now sports a context-sensitive, smart title. (Related: Give descriptive titles to your charts for best results | smart chart legends – how to?)
25. Add data to charts with copy paste: Got a chart with sales trend for 3 products and want to add product 4 to it? Simple. Copy the data, select the chart, press CTRL+V. Tell Excel how you want this new data to be pasted and your chart is updated instantly.
26. Forecast with seasonality and trends easily: If you have seasonal data, forecasting it seems like a tricky thing. Now with newly introduced FORECAST.ETS() function, this is super easy. See all about forecasting here and then read how to build a complex forecast chart.
27. Format a chart quickly with styles and themes: Once you set up your charts, speed up the formatting process by using built-in themes and styles. Go to design tab to customize your charts in a jiffy.

Formatting / Presentation tricks

It’s no good if you are productive. Your presentation skills are equally (if not more) important.

Let’s see some powerful formatting / presentation tricks.

28. Format anything with CTRL + 1: Simple, select the cell / chart / image / drawing shape you want to format. Press Ctrl 1. Format as you want.
29. Use alignment tools, you must. Hmmm:
    
    If your report has multiple charts (or shapes), then align them all, you must. Having perfect alignment doesn’t mean you waste several minutes nudging each chart in to right position. Simple select them all (using the select objects tool, of course) and fire up alignment tools from either Page Layout or Format ribbon. Align and space objects in a consistent way. You can also hold ALT key when moving charts / shapes to align them with cell borders.
30. Repeat last actions with F4 key: Let’s say you are changing font color for various chart elements. You can do this step once on something like vertical axis, then select other items and simply press F4. This will repeat your last action (ie font change) on the new selection.
31. Format once, paint many times: Use format painter tool from Home ribbon to quickly apply format settings (including conditional formats) from one range to many. Works awesomely and saves you several precious minutes of formatting time. Double click on format painter to lock format painting mode. This way you can copy once and paste several times.
32. Add frequently used items to quick access toolbar: Formatting tends to be a time consuming activity. To reduce the amount of clicks, mouse travel & un-necessary ribbon navigation, simply add all the frequently used formatting options to quick access toolbar.
33. Turn-off grid lines: Get rid of them grid lines to instantly give your workbooks a professional & clean look. You can do this by going to View ribbon. While at it, consider turning-off formula bar & headings too if you find them intrusive.
34. Hide extra rows and columns: To create a clean and polished look for your dashboard, hideaway all the unused rows and columns. Say, your dashboard is in A1:S80, then select row 81, press CTRL + Down Arrow and hide all these rows. Repeat the process for columns T onwards.
35. Format for print too: Don’t forget that many users rely on print, pdf formats to consume information. So make sure your reports are formatted for printing. Start by setting up a print area. This ensures that only necessary information is printed. Also, disable print for screen features like form controls or slicers. You can’t select a slicer button on a print out. Read all about formatting for print.

Related: 8 tips to make you a formatting pro.

Want more? Join Excel School Program

If your job involves data analysis & story telling (to be honest all jobs need these skills), then you are going to love my Excel School Course. This end-to-end program enables you to use Excel and get things done faster at work.

We are now open for enrollments. Sign up for the program to become awesome. Click here to know more.