To Empress Your Boss With Excel Tricks
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 correction and can complete the work on your behalf.
In Excel 2013, Click in 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.
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.
Click in your table of data and go to ‘Insert>PivotTables”. 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.
In Excel 2013 and 2016 , go to “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 answer you need. Lastly, use “By changing Cell” to pick the input value that Excel can adjust, then click OK
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.
Select your range by going 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.
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 need Match. After the initial learning curve, Index and Match offers much more flexibility, speed and ease of use .
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 answer into Index and Excel can Retrieve the value where the two Intersect.
“=INDEX ( array , MATCH (lookup_value, lookup_array ,0),MATCH(lookup_value,lookup_array,0))”