Excel Tricks That Will Impress Your Boss
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 as our friends at STL have given us their most useful tips and tricks that will not only save you from an Excel nightmare, but also impress your boss!
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: 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. Pivot Tables
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: 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 our formulas to produce the result you are looking for.
How to: 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: 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: 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, lookup_array, 0))
Perfect for retrieving data from complex sets of records!
6. Waterfall Charts
One of the most popular visualization tools for showing net values, Waterfall charts break down flow and contributions to a total – ideal for financial professionals and statisticians.
How to: InExcel 2016, select your data and go to “Insert>Insert Waterfall or Stockchart>Waterfall”.
Perfect for reporting performance!
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: 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 – everything your boss will be looking for when it is time for promotions and salary increases!
STL seek to provide an unrivalled value and quality service, enabled by a strong working culture. STL’s 500+ courses span Microsoft Office (365, 2016 and earlier) training (including a comprehensive portfolio of Excel training), Professional and Management courses (Time Management to Leadership & Development), Technical to eLearning services. STL have instructor led London training centres and also deliver across the UK, EEC/Abroad.