Advanced Excel Tips

Conditional Formatting

Making sense of a sea of data can be difficult. Conditional Formatting brings a level of sophistication. Used well, it can bring your spreadsheet to life. Say you want to know who your top salespeople are; within 3 clicks you can reveal your top 10%.

PivotTables

It can take around 4 hours to get skilled. You may be put off learning PivotTables, but don’t be. You can use them to sort, count, total and average data stored in one big spreadsheet and display them in a new table. If you want to look only at sales for certain dates, products or marketing channels, it’s easy. But there’s an important warning: Ensure your data is clean!

Paste Special

The most common ‘paste special’ function is where you want to change the formatting. The data you need has its own styling. It’s annoying and ugly to plonk down formatting from elsewhere. Just copy the values and you’ll get the text or number. The shortcut after copying the cell (Ctrl C) is Alt E S V – easier to do than it sounds. The other big one is Transpose. This flips rows and columns around in seconds. Shortcut Alt E S E.

Print Optimisation

Imagine if what you printed was always what you intended. It IS possible. But there are a few variables to manage: print preview, fit to one page, adjust margins, print select, print headers, portrait vs landscape and of course, design. Invest time and get comfortable with it. You’ll be carrying out this task many, many times throughout your working life.

Extend Formula Across/Down

The true beauty of Excel is its scalability. Get the formula right once and Excel will churn out the right calculation a million times over. The + crosshair is handy. Double click will take it all the way down if you have continuous data. Occasionally a copy and paste (either regular paste or paste formulas) will be much faster for you.