Knowledgebase

Share to Facebook Share to Twitter Share to Google Plus Share to LinkedIn

Excel has a diverse range of features that can be utilised to make working more efficient and simple. For example, for every task there is a keyboard shortcut made to speed up production; some of these are shown below. Also included are a few other features worth knowing about in Part 2 of Microsoft Excel - Top Tips.

Shortcuts:

CTRL + F5 changes the size of the workbook
CTRL + SHIFT + $ Changes highlighted fields to currency with two decimal places. Shows the accurate currency symbol for the location set
CTRL + PAGE UP/PAGE DOWN Flicks through the various sheets within the spreadsheets
CTRL + Z

Undo the recent change

CTRL + F2 Opens print preview
CTRL + P Opens the printer dialog
ALT + M Go to the formula tab
CTRL + ; Adds the current date into the spreadsheet
CTRL + :

Adds the current time into the spreadsheet

CTRL + F1 Minimises/Maximises the page ribbon
ALT + F4 Closes the current workbook

Working with Formulas

One key feature comes into play when making a large number of calculations within the workbook, the use of formulas. Formulas mean that if the value is changed in one field then the calculation will always contain the most up to date value, thus no time needs to be taken to update all calculations when changes are made.

To initiate the creation of a formula, place an equals sign (=) within the cell and write out the formula using the desired cells. If you want to collate a range of figures from various sheets on the workbook, formulas will allow you to do this. This can be achieved by manually clicking on the sheets to select the relevant cell(s).

PivotTables

Pivot tables are there to extract significance from a large, detailed data set. To create a pivot table, you must select a cell within the worksheet. From there: click insert, select tables and then PivotTable. Excel will then request you enter a table/range, this is automatically allocated so you can just click ok. After that, all the all the user needs to do is select what cell categories they want in their table, allocate those into the relevant fields below, and click update!

Images placed below show the main steps as a visual aid!

Pivot table 1

Figure 1: Basic table creation                                

Pivot table 2 

Figure 2: Pivot table data selection

Pivot table 3

Figure 3: Created Pivot table

Conditional Formatting

When you have a spreadsheet full of figures and tables, a handy function that Excel has is conditional formatting. This applies set colours and styles to cells depending on their content. There are pre-set conditions for example a range of numbers will be given one of three colours, red, amber and green, depending on how high or low they are.

If there is not a current pre-set available that fits your needs, then you are able to create a new rule for the cells you want to format. This allows you to choose a rule type from a list and then to further tailor it.

Further Reading

For more information, please visit https://www.pcmag.com/feature/327311/20-excel-tips-for-becoming-a-spreadsheet-pro