Regardless of the work we do in Excel, it’s a good idea to have some understanding of how to use some of these tools, since Excel seems to find its way into almost every job. So, whether you work in Finance and need to work with budgets, or in Marketing organising events and campaigns, or in any other department within your organisation, no matter what industry you are in, knowing some basic tips and tricks in Excel will help you to work more effectively and efficiently in your day to day work.
And, as a bonus, once you master these tricks, you might even start to love Excel.
Charts are a very powerful tool to present your information in a professional manner to your stakeholders.
Introduced in Excel 2013, the Recommended Charts tool offers basic visualisation, giving you the option to select the best type of chart from an array of suggestions. You can show the big picture of your data by placing different charts onto one sheet.
In an Excel worksheet. enter your data and column headers. Select the data then select
Insert > Charts > Recommended Charts
With this tool, you can easily highlight points of interest within your database. For example, you could be recording project efficiency and format cells below 80% to be highlighted in red. The options are endless and you can customise the rules and create your own criteria.
Select the cell or cell range you want to format and select
Home > Styles > Conditional Formatting > Select
This tool speeds up the process of working with small data sets, minimising the time spent on creating charts, summing data, applying formatting amongst other functionalities. After selecting your data, you can easily access tools like formatting, charts, tables, totals and sparklines.
Select your data then click on the Quick Analysis tab in the bottom right corner of your selected data to bring up the Quick Analysis menu.
This feature was introduced in 2013. It is an interactive data exploration and visualisation tool. Power View can quickly collate and analyse large data sets and create interactive presentations and reports allowing you to make better business decisions. Power View is compatible with PowerPoint, it’s an unbeatable aid for delivering reports.
In Excel 2013 and 2016, select Insert > Reports > Power View.
Some would say this is the most powerful of the Excel tools. PivotTables allow you to quickly summarise large amounts of data stored in databases and tables. Better still, it doesn’t require a single formula. In Excel 2013 the Recommended PivotTables command was introduced. This tool suggests a customised set of PivotTables that might best suit your data.
Select the data you want to use to create your PivotTable then select
Insert > Tables > Recommended PivotTables