Creating a drop-down list

By Neil Shorney

Creating a drop-down list: Quality of data in Excel is vital if you are to be able to pull useful results from data sets, and text can be a particular problem if you have repeated values that people choose to spell differently each time they're entered. On the Data tab on the Data Validation button, you can choose what data you will and will not allow.

To create a drop-down list for data entry, first create your list of allowed values in a range of cells, then click Data Validation, choose “List” from the drop-down box and define your source data as the range you've just created. We look at this further in Intermediate Microsoft Excel in the classroom, and in Working with data in Microsoft Excel webinar.

Keyboard shortcuts

By Neil Shorney

By Neil Shorney, Director, Naturally Sales Ltd.

To achieve things more quickly, keyboard shortcuts are an alternative to clicking the mouse. You might know that you can use Enter to move down a cell, and Tab to move to the right. Did you also know that Shift+Enter will move your selection up and Shift+Tab will move it left.

We look at this in more detail in Introduction to Microsoft Excel.

Pivot table tip

By Neil Shorney, Director, Naturally Sales Ltd.

When creating a Pivot Table in Excel 2007 or 2010, they can be opened in Excel 2003, but will be read-only. To create a pivot table which is editable in 2003, 2007 and 2010, create it with Excel 2003. You can then edit it with all versions.

We look at pivot tables in more detail in Intermediate Microsoft Excel.

Formula creation tip

By Neil Shorney, Director, Naturally Sales Ltd. When creating a formula, try to use the mouse as little as possible. Why? Because sometimes, clicking the mouse can confuse the formula. For example, when referencing another sheet using VLOOKUP or when using lists in Data Validation, clicking the mouse back to the other sheet at the wrong time will cause the formula to return an error value. As best practice, use the Return key (move down) or the Tab key (move right), and only click on another worksheet when it's absolutely necessary. We look more at how to create formulas in Intermediate Microsoft Excel.

Excel's crystal ball

By Neil Shorney, Director, Naturally Sales Ltd. Did you know that you can use Excel to gaze into the future...? If you only have partial data for a time period (say, for example, sales data part-way through the year), Excel can predict the rest of the period based on historical results. To do this, select data for the full period, including blank cells for that data which doesn't yet exist. Then create a 2D chart, and on the Layout contextual tab, select Trendlines to predict the future. Note this only works with 2D column or line graphs. We look at predicting future results more accurately in Advanced Microsoft Excel.

Performing backwards lookups in Microsoft Excel

By Neil Shorney, Director, Naturally Sales Ltd.

The most common ways of looking up data in Excel are the VLOOKUP and HLOOKUP functions. One drawback of using this method of finding data, is that they can only look up data from left-to-right (VLOOKUP) or top-to-bottom (HLOOKUP). In Advanced Microsoft Excel, we learn about backwards lookups, but as a work-around, you can copy and paste the column from which you'd like to return a value, to a blank column at the end of the data set, then do a normal forward lookup to return the data you need.

Click here to download an Excel file with step-by-step instructions.