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!
Figure 1: Basic table creation
Figure 2: Pivot table data selection
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://uk.pcmag.com/office-suites/35723/22-excel-tips-for-becoming-a-spreadsheet-pro