Excel Tricks You Should Be Using at Work
Knowledge of Microsoft Excel is practically a requirement for any job application. But do you know as much about the program as you think you do?
There are many features that barely see the light of day, features that can propel you to Excel extraordinaire in your office and boost your professional value. Or on a really basic level, they’ll simply save you a lot of time.
If you’re using Excel a lot in your workplace, these nifty tricks are essential to your tech repertoire.
- Flash Fill
This tool decreases the time taken to process and correct data entered into Excel cells. Flash fill recognises the patterns in your data processing and completes the work for you, such as extracting names from a list of emails or capitalising names.
To do this, simply click in the column alongside the original data and type how you want it to be. Then select your correction and the empty cells below and press CTRL+E to flash fill.
Ideal for managing mailing lists or tidying up database records.
- Pivot Tables
One of the most worthwhile Excel tools to master, a PivotTable allows you to quickly summarise large amounts of data into tables without writing a single formula.
Simply click in your table of data and go to ‘Insert > PivotTable’. Highlight everything you want to include and click OK. The pop-up bar on the right hand side allows you to sort the data and generate your PivotTable.
Perfect for reports and analysis.
- Goal Seek
This handy tool is for when you know the output of a formula but are unsure of the exact figures that when into it.
To make use of this mathematic service firstly select ‘Data > Data Tools > What-If Analysis > Goal Seek’. In the ‘Set Cell’ box select the cell with the formula you want to determine. In the ‘To Value’ box enter the output you need. Then, in the ‘By changing cell’ box select the input value that Excel can adjust, then click OK.
Use this for forecasting and setting targets.
- Conditional Formatting
Conditional formatting helps you to present the results of your spreadsheet quickly and articulately by highlighting the particular values you want people to look at.
A simple but effective tool, to do this go into ‘Home > Conditional Formatting’ and choose from the range of formats available. For example, to highlight all values lower than 100 simply select ‘Highlight Cells Rules > Less Than’ and enter 100.
Perfect for reports and analysis.
- Index and Match
This is essentially a more advanced version of VLOOKUP. Where VLOOKUP will only search the first column of your data table, using index and match will retrieve values from a more complex demand. It takes some learning, but once mastered offers more flexibility, speed and ease of use.
This tool requires your columns and rows to be labelled. Use MATCH to return the column that contains your search target and another MATCH to find the row that will contain your answer. Feed these two answers into INDEX and Excel can retrieve the value where the two intersect.
A smart way for retrieving data from a complex set of results.
- Waterfall Charts
A brilliant visual tool for showing net values, waterfall charts condense contributing data into a total and present it in a nifty chart. Very easy to do, and very effective.
Simply go to ‘Insert > Insert Waterfall or Stock Chart > Waterfall’.
A great way of demonstrating performance.
Forecasting is a necessity in any business. Excel’s forecasting tool consolidates historical databases and provides formulaic projections of future outcomes. It even considers upper and lower margins to compensate for any errors or seasonal changes.
To do this, select your two corresponding sets of data and go to ‘Data > Forecast > Forecast Sheet’. In the ‘Create Forecast Worksheet’ box, choose either a line chart or a column chart, pick an end date, and then go ‘Create’.
Essential for modeling performance expectations.
Even if you only adopt one or two of these tricks into your repertoire, you’ll improve your productivity and manage your data far more efficiently, increasing your value as an employee.