EXCEL’S SECRET SEARCH TOOLS
How did you cope without them?
OnExcel sheets that contain a lot of data, it can be time-consuming to find exactly what you’re looking for. We’ll show you how to quickly locate the data you need by applying
colour shading to values that are over a certain amount and by using Excel formulae. These handy tricks will help you filter and find the data you want in a matter of minutes.
STEP 1
In an expenses or budget sheet (see our example screenshot), it can be very useful to identify high-cost items. Excel’s Conditional Formatting tools can help speed up this process by letting you highlight cells above (or below) a particular value. First, click and drag to select all the data you want to analyse 1 , then click the Conditional Formatting dropdown menu 2 (in the Styles section of the Home tab) to see a range of options 3 .
STEP 2
Move your cursor to the Highlight Cell Rules option, then click Greater Than. Enter a value (for example, £100 1 ). Any cells with values above £100 will be shaded red 2 . To change this colour, click the ‘with’ dropdown menu, select the option you want 3 , then click OK.
STEP3
To customise how your highlighted cells appear, select Custom Format in the ‘with’ dropdown menu from Step 2. You can now change your font style 1 , font colour 2 and even add a strikethrough effect 3 . You can also add a border 4 and customise it. To colour the cells, click the Fill tab 5 , select a colour from the palette, choose a pattern from the Pattern Style dropdown menu, then click OK twice to see your changes.
STEP4
The Conditional Formatting dropdown menu has a few other options. For example, you can choose to highlight the top (or bottom) 10 numbers 1 in your selected data. Data Bars 2 add coloured bars to your cells, whose size correspond to the cell’s value 3 . Color Scales 4 add graded colours to your cells (higher values shown as red, midrange values yellow and lower values green). Icon Sets 5 lets you add icons (such as up or down arrows to represent high or low values).
STEP5
Excel’s useful COUNTIF function lets you find out how many cells within a column have exceeded a certain value. Using our monthly expenses chart, we’ll show you how to find out how often we spent more than £100 in May. First, click the cell where you want to display this answer 1 , then in the Function field at the top type =COUNTIF 2 . Next click the ‘range’ link 3 , then select the cells whose values you want to search (the May column in our example) 4 .
STEP6
Next, you need to specify the minimum value you’re looking for in the data, so type ,”>100” 1 at the end of your formula. Now press Enter to see how many times you spent over £100 in the month of May 2 . If you subsequently change any values in the May column, this figure will update itself accordingly.
STEP7
Another great Excel search function is VLOOKUP. This lets you instantly find out the value of a specific cell in a particular column. In our example, we’ll show you how to find how much we spent on a particular Expenses category (such as Kids or Fuel) 1 in March 2 . Once you get the hang of the function, you can use it to instantly find the value of any cell within a particular column in your spreadsheet. For our example, we’ll create two extra rows called Expenses and Price 3 .
STEP8
We’ll now create a formula that lets us display the expense value in the cell next to Price (B17 in our spreadsheet) when we type any expense category in the cell next to Expenses (B16). First, select cell B17, click inside the Function field at the top, then type =VLOOKUP( 1 . Next, click the ‘lookup value’ link 2 , then select cell B16 3 (because that’s where we’ll type an option from the Expenses category – such as Kids or Fuel).
STEP9
The VLOOKUP formula you need to create includes many variables, which are separated by commas. Type , (comma), click the ‘table_array’ link 1 and select your entire table (to specify the range of cells within the sheet). Type another comma (,), click the ‘col_index_num’ link, then press 4 (this represents the column number for March 2 in our data). Finally, type another comma, type FALSE (this displays the formula in cell B16), then type a closed bracket to complete the formula 3 .
STEP 10
Press Enter and you’ll see #N/A in cell B17. Don’t worry, this only appears because you’ve not yet specified a value in cell B16. Now type an Expense category into cell B16 (such as Kids or Fuel 1 ), then press Enter. Cell B17 will now display that category’s expenses 2 for the month of March 3 . Change the value in cell B16 to an alternative expense (such as Phones), then press Enter to see its cost in cell B17.