Attend courses from home - All courses REMOTE from April 1 - April 30

Numbers on screen

What are the best Excel tips and tricks?

November 13, 2019

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.

What is Remote Training?

Valued by Individuals

 

4.8/5
Over 20105 Reviews
I am excited about PowerPoint after completing this course with John. I was advised by Ella to do this course and it was good advice. Thanks again John! -PowerPoint Level 2 Sydney
Paul - PowerPoint Level 2, .
Awesome class today John. Really informative and thoroughly enjoyed the topics especially the Video and Master Slides components, I will be able to use these for upcoming projects. -PowerPoint Level 2 Sydney
Dom - PowerPoint Level 2, .
Great course. John is an awesome trainer. Had a great learning. Thank you very much. -PowerPoint Level 2 Sydney
Manisha - PowerPoint Level 2, .
Great course very in depth, covered all basis, great templates -PowerPoint Level 2 Sydney
Shin - PowerPoint Level 2, .
john was extremely informative and made the day go quickly. helped me regain a lot of forgotten knowledge -PowerPoint Level 2 Sydney
Nathan - PowerPoint Level 2, .
Jagmohan presented well and explained in detail. I found the content to be a little basic for what i was expecting. -Word Intermediate Sydney
Shanay - Word Intermediate, .
Was really informative, easy to follow and Ken did a great job given a few technical issues beyond control -Teams for Collaboration Adelaide
Melissa - Teams for Collaboration, .
Lesson went really well - had some time left so Ken went through some additional apps at our request. Very insightful. -Teams for Collaboration Adelaide
Lucia - Teams for Collaboration, .
Great Trainer. Very impressed. Learned a lot. -Power BI Beginner Melbourne
Andrew - Power BI Beginner, .
Great course, highly recommend. -Excel Intermediate Canberra
Chandre - Excel Intermediate, .
Great course, very informative! Excited to apply what I have learnt at work. -Excel Intermediate Canberra
Polly - Excel Intermediate, .
Enjoyed course just couple of issues with remote connection in that I could hear and see course, but could not ask questions for a period of time. Terry was very helpful in sorting out issue during the lunch break.. -Excel Intermediate Canberra
Mary - Excel Intermediate, .
For a session that is originally designed for face to face delivery, the online learning (aside from a few technological issues) was informative and covered a broad range of topics in detail. Jag explained everything really well and made sure that all participants were following and always stopped to help if anyone fell behind or were having issues. Thank you! -Excel Intermediate Canberra
Katie - Excel Intermediate, .
Thank you Michael for a very comprehensive introduction to Power BI. I appreciated your explanations regarding why we do certain actions. You also re-emphasised points by repeating them as there is a lot to process through in one lesson. -Power BI Beginner Melbourne
Matthew - Power BI Beginner, .
Good -Excel Macros/VBA 2 Day Sydney
Qing - Excel Macros/VBA 2 Day, .
Course was very informative. Thoroughly enjoyed. -SharePoint Site Owner Perth
Shannon - SharePoint Site Owner, .
Great course - Dominic was a engaging instructor considering the limitations of online learning -SharePoint Site Owner Perth
Alyce - SharePoint Site Owner, .
Michael was an excellent demonstrator, and engaged us well and answered all our questions. Thank you Michael! -Power BI Beginner Melbourne
Vatsal - Power BI Beginner, .
Excellent - great pace and clear instructions - really useful! Will send others from my team. Cheers Michael Sean -Power BI Beginner Melbourne
Sean - Power BI Beginner, .
Very detailed. Might be good to have a shorter higher level course, i found this course to be a little too detailed, as I am already very familiar with SharePoint. More time on Flow would have been good and less time on other functions -SharePoint Site Owner Brisbane
Jodie - SharePoint Site Owner, .

Trusted by Business

Procured by Government

Map of Australia