What are the best Excel tips and tricks?

We all use Excel, it’s one of the most well-known Microsoft programs that makes so many of our work processes more efficient and the organisation of data sets so simple.  We also all know that Excel is a beast, and the things it can do for you are almost limitless.  As long as you have the right formula, Excel will pretty much do anything for you automatically, making it easy to make sense of your data.  Here are five of our favourite formulas and tips and tricks to use in Excel.

Using Pivot Tables

Pivot Tables help you summarise and explore data interactively within a spreadsheet.  They can help you to sum up values and compare different data sets depending on what you’d like to look at.

Check out this blog to learn how to make your own Pivot Table.

Using filters to simplify data

Huge datasets can be very overwhelming to look at, and sometimes you only want to be looking at certain rows at the one time.  Excel allows you to apply filters to each column so you can then choose which cells you want to view at once.  Simply click the Data tab in Excel and select Filter to customise what you’d like to view.

Calculation formulas

Excel makes even the simplest of mathematics easier, and a few keyboard shortcuts can make the process incredibly quick and easy.

Some basics nobody should go without:

To add, use +

To subtract, use –

To multiply, use *

To divide, use /

Using VLOOKUP

The VLOOKUP function allows you to pull data from one area of a sheet to another.  This is perfect for when you have two sets of data on two different spreadsheets that you want to combine.  This is where the VLOOKUP formula, =VLOOKUP(lookup value, table array, column number, [range lookup]) becomes your greatest ally.

Check out this blog for VLOOKUP troubleshooting.

Adding drop-down menus

Excel spreadsheets are useful to track processes and other qualitative items.  Rather than writing ‘Yes’ and ‘No’ values repetitively, you can add a Drop Down list into your spreadsheet to quickly mark descriptive things about what you’re tracking.

Check out this blog to learn how to create your own Drop Down list.

To learn more about Excel and how to best use it for your business, check out our courses across Australia.