Neo Diano
The World Of Diano is Here

- Advertisement -

Excel Tricks


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.

How to:

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.

3.Goal Seek

- Advertisement -

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.

How to:

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 .

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 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))”


- Advertisement -

- Advertisement -

- Advertisement -

Leave A Reply

Your email address will not be published.