If you’re running a startup or scaleup, likelihood is you’re using Excel spreadsheets to organise some form of data. If you’re not an Excel whizz, never fear, as Andrew Hewertson from Microsoft Office training provider STL is here to share his useful tips and tricks that will save you from spreadsheet nightmares.
The prospect of tackling Excel can fill many employees with dread. The software can be intimidating to spreadsheet novices and even those who use it on a day to day basis. Luckily, help is on hand with these top tips.
1. Flash fill
Tired of splitting first names from last names or sorting out UPPER and lower case entries by hand? Excel now looks for the pattern in your corrections and can complete the work on your behalf.
How to do it: In Excel 2013, click the column alongside the data to fix and type how it should look. Select your correction and the empty cells below and hit CTRL+E to flash.
Perfect for managing and fixing mailing lists and tidying database records!
2. PivotTables
One of the most powerful and rewarding Excel tools to master, PivotTables allow you to quickly summarise large amounts of data in lists and tables without writing a single formula.
How to do it: Click in your table of data and go to “insert>PivotTable”. Once you’ve highlighted everything you want to analyse, click OK. Use the right hand bar to generate your table by dragging the fields in the top half to their respective slots in the lower four boxes.
Perfect for reporting and analysis!
3. Goal seek
Sometimes you know the answer you want your report to show, you just need to know what inputs will produce that answer. Goal Seek does the legwork for you, telling you what figures to put in to formulas to produce the result you are looking for.
How to do it: In Excel 2013 and 2016, go to “Data>Data Tools>what-If Analysis>Goal Seek”.
In the “Set Cell2” box, select the cell with the formula you want to determine. In the “To Value” box, enter the answer you need. Lastly, use “By changing cell” to pick the input value that Excel can adjust, then click OK.
Perfect for forecasting and setting targets!
4. Conditional formatting
When you’re reviewing spreadsheets with your boss, the results should be obvious at a glance. With Conditional Formatting you can automatically highlight the values you want them to look at.
How to do it: Select your cell range by going to “Home>Conditional Formatting” and choose from the range of formats available. For example, to highlight all values lower than 100, choose “Highlight Cells Rules>Less than” and type in 100. Before clicking OK you can also choose the format that will apply to any matching values.
Perfect for reporting and analysis!
5. Index and match
VLOOKUPs only search in the first column of your table. If you’re looking to deep-dive into the table, then you’ll need Index and Match. After the initial learning curve, Index and Match offers much more flexibility, speed and ease of use.
How to do it: Ensure your data is formatted in a grid with headers and row labels. 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.
E.G.= INDEX (array, MATCH (Lookup_VALUE, Lookup_array,0) MATCH (lookup_value, lookuo_array, 0))
Perfect for: Retrieving data from complex sets of records.
6. Waterfall charts
One of the most popular visualisation tools for showing net values, Waterfall charts break down flow and contributions to a total – ideal for financial professionals and statisticians.
How to do it: In Excel 2016, select your data and go to “Insert>Insert Waterfall or Stockchart>Waterfall”.
Perfect for reporting performance!
7. Forecasting
Forecasting lets you project future outcomes from historical datasets. You can even calculate upper and lower confidence bands to include margins for error, or calculate for seasonality.
How to do it: In Excel 2016, select your two corresponding sets of data and go to “Data>Forecast>Forecast Sheet”. In “Create Forecast Worksheet” box, choose either a line chart or a column chart, pick an end date, and then click “create”.
Perfect for modelling performance expectations!
By adding one or more of these tricks to your repertoire, you’ll be able to effortlessly manage datasets, improve productivity and demonstrate value.