The Top 5 Underused Features in Excel

Posted on by

1. VLookups

The VLOOKUP function performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position.

The VLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the VLOOKUP function can be entered as part of a formula in a cell of a worksheet.

The VLOOKUP function is actually quite easy to use once you understand how it works! If you want to follow along with this tutorial, download the example spreadsheet.

Let’s explore how to use VLOOKUP as a worksheet function in Microsoft Excel.

Based on the Excel spreadsheet above, the following VLOOKUP examples would return:

2. Pivot Tables

  1. Open the worksheet that contains the table you want summarized by pivot table and select any cell in the table.Ensure that the table has no blank rows or columns and that each column has a header.
  2. Click the PivotTable button in the Tables group on the Insert tab.Click the top portion of the button; if you click the arrow, click PivotTable in the drop-down menu. Excel opens the Create PivotTable dialog box and selects all the table data, as indicated by a marquee around the cell range.
  3. If necessary, adjust the range in the Table/Range text box under the Select a Table or Range option button.If the data source for your pivot table is an external database table created with a separate program, such as Access, click the Use an External Data Source option button, click the Choose Connection button, and then click the name of the connection in the Existing Connections dialog box.
  4. Select the location for the pivot table.By default, Excel builds the pivot table on a new worksheet it adds to the workbook. If you want the pivot table to appear on the same worksheet, click the Existing Worksheet option button and then indicate the location of the first cell of the new table in the Location text box.
    Indicate the data source and pivot table location in the Create PivotTable dialog box.
  5. Click OK.Excel adds a blank grid for the new pivot table and displays a PivotTable Field List task pane on the right side of the worksheet area. The PivotTable Field List task pane is divided into two areas: the Choose Fields to Add to Report list box, with the names of all the fields in the source data for the pivot table and an area divided into four drop zones (Report Filter, Column Labels, Row Labels, and Values) at the bottom.

    New pivot table displaying the blank table grid and the PivotTable Field List task pane.
  6. To complete the pivot table, assign the fields in the PivotTable Field List task pane to the various parts of the table. You do this by dragging a field name from the Choose Fields to Add to Report list box and dropping it in one of the 4 areas of the Layout section using the mouse. Alternatively, click and hold the field name in the Field section, and then drag it to an area in the Layout section – this will remove the field from the current area in the Layout section and place it in the new area.

3. Conditional Formatting

To truly leverage the capabilities of conditional format in Excel, you have to learn how to create various rule types. This will help you make sense of whatever project you are currently working on.

Conditional formatting rules in Excel define 2 key things:

  • What cells the conditional formatting should be applied to, and
  • Which conditions should be met?

We are showing you how to apply conditional formatting in Excel 2010 because this seems to be the most popular version these days. However, the options are essentially the same in Excel 2007 and 2013, so you won’t have any problems with following these instructions, no matter which version is installed on your computer.

  1. In your Excel spreadsheet, select the cells you want to format.For this example, we’ve created a small table listing the monthly crude oil prices. What we want is to highlight every drop in price, i.e. all cells with negative numbers in the Change column, so we select the cells C2:C9.
  2. Go to the Home tab > Styles group and click Conditional Formatting. You will see a number of different formatting rules, including data bars, color scales and icon sets.
  3. Since we need to apply conditional formatting only to the numbers less than 0, we choose Highlight Cells Rules > Less Than…
     
  4. Of course, you can go ahead with any other rule type that is more appropriate for your data, such as:
    • Format values greater than, less than or equal to
    • Highlight text containing specified words or characters
    • Highlight duplicates
    • Format specific dates
  5. Enter the value in the box on the right-hand part of the window under “Format cells that are LESS THAN“; in our case we type 0. As soon as you have entered the value, Microsoft Excel will highlight the cells in the selected range that meet your condition.Select the format you want from the drop-down list. You can choose one of the pre-defined formats or click Custom Format… to set up your own formatting.
  6. In the Format Cells window, switch between the Font, Border and Fill tabs to choose the font style, border style and background color, respectively. On the Font and Fill tabs, you will immediately see a preview of your custom format.
  7. When done, click the OK button at the bottom of the window.

 

4. Data Validation – Drop-down Boxes

Creating drop-down lists with comma-separated values

This is the fastest 3-step way to create a drop-down box in all versions of Excel 2013, 2010, 2007 and 2003.

  1. Select a cell or range for your drop-down list.
    You start by selecting a cell or cells where you want a drop-down box to appear. This can be a single cell, a range of cells or the entire column. If you select the whole column, a drop-down menu will be created in each cell of that column, which is a real time-saver, for example, when you are creating a questionnaire.
    You can even select non-contiguous cells by pressing and holding the Ctrl key while selecting the cells with the mouse.
  2. Use Excel Data Validation to create a drop-down list.On the Excel ribbon, go to the Data tab > Data Tools group and click Data Validation.
  3. Enter the list items and choose the options.In the Data Validation window, on the Settings tab, do the following:
    • In the Allow box, select List.
    • In the Source box, type the items you want to appear in your drop-down menu separated by a comma (with or without spaces).
    • Make sure the In-cell drop-down box is checked.
    • Optionally, select Ignore blanks if you want to allow your users to leave the cell empty.
    • Click OK and you are done!

Now, Excel users simply click an arrow next to a cell containing a drop-down box, and then select the entry they want from the drop-down menu.

Your drop-down box is ready in under a minute. However, this is not the best way to create an Excel drop-down list, especially if you want the same list to appear in multiple cells, and here’s why…

If you want to edit your Excel drop-down list at some point in the future, you would have to change every cell that references the Data Validation list. This may not be a big deal if you have just a few such cells residing on the same sheet, but if you have dozens or hundreds scattered across different worksheets, it may take quite a while to locate and edit them all. Anyway, if you decide to stick with this easiest approach, see the detailed guidance on how to edit a comma-separated drop-down list.

 

5. Using the Fx button to determine which formula to use in Excel

It’s not always easy to figure out which formula to use in Excel; however, there is a feature that will help you determine the right formula. The FX button in Excel 2007/2010 is a very easy to use, effective search tool to find functions in Excel.

There may be situations where you don’t know the exact function to use, but if you know some keywords associated with the task you are trying to perform, then follow the below steps to find out if a relevant Function exists.

  1. Go to the Formulas ribbon and click on the Fx insert function button.
  2. The Insert Function wizard will open.
  3. Under the Search for a function text box, type the keyword you want to search.
  4. If you know the category, select it from the drop-down, or else select All in that.
  5. Press on Go, which will result in a list of functions under Select a function.

Tip: This is also a very handy tool if you come across a function in a spreadsheet which you are unsure of. Simply click the Fx button and it will tell you what the function does, and even break up the individual arguments it is taking in its present usage.

Click here for more information on customizing your training and choosing just the right courseware for you.

Contact us to help you organize and customize your training to get the most from Excel’s most underused features.
Posted in Excel and tagged , , , , , , , , , .