There have been many new features built into Excel over the years and some of these are not obvious. If you are a regular user, and particularly if you undertake the same tasks a lot, some of these features will make you more efficient. A few tips follow.
1. Converting Calculations to Actual Values
You might have a worksheet that has calculations in various cells, with everything working correctly. If you want to send an extract of the results to someone, you can’t simply save a copy of the worksheet with the non-required rows and columns deleted because the calculations won’t work properly anymore and you’ll finish up with lots of errors.
The solution is to first save a separate copy of the full worksheet and then select the full contents by clicking the space in the top corner immediately above the row numbers and to the left of the column letters. Select Paste Special from the Edit menu, click Values under the ‘Paste’ heading (or Values and Numbers if you want to preserve currency signs and date formats) and click OK. The formulas are replaced by the actual values so you can then delete all rows and columns you don’t want without any fear of having error messages. Save the copy you want to send.
2. Creating Unique Entries
The problem with large worksheets, particularly those that have been developed over a long period, is that they often contain duplicate entries that you want to get rid of. This can be almost impossible to do manually.
If you simply want to see a list of unique entries for a column, select Data followed by AutoFilter, and then click the drop-down arrow button above any column to see a list of its unique entries. If you actually want to generate a unique list so you can make a copy of it, select Data then Filter and Advanced Filter. Click on any column letter to select that column, press Ctrl and C to take a copy, and then paste that data into an empty column.
With the new column still selected, choose Data, Filter and Advanced Filter again. If you choose to ‘Copy to another location’ and check Unique Record Only, you can then choose to create a column at an indicated location. This will have unique records in alphabetical order.
3. Applying Common Formatting
You can format individual cells by setting the font type and size, background color and other attributes. If you want to apply the same formatting to other cells, first select the formatted cell then click the Format Painter tool (yellow paintbrush symbol). Then click on another cell and drag the cursor in any direction to apply the formatting to selected cells.
4. Calculating Subtotals
Excel can calculate subtotals automatically rather than having to create a formula to do this.
In case you make any mistakes, take a copy of your worksheet and use that until you’re sure everything’s as you want it. Then, ensure that the column on which you want to sub-total is sorted into the correct order (all product As followed by all product Bs, for example).
Click anywhere in the worksheet and choose Data then Subtotals. Select the column you want to subtotal and the required function (Sum, Average and so on). Click OK to see the results.
5. Control Key Shortcuts
The control key has a number of uses to speed up worksheet processing. If you want to select multiple cells that aren’t necessarily next to each other, hold down the Ctrl key while clicking individual cells or dragging through adjoining cells.
You can also use the Ctrl key to move quickly around the worksheet. Hold it down while pressing an arrow key to go to the last populated cell in that direction. If you hold down the Shift key at the same time, all intervening cells are selected.
To enter the same data to a number of cells, first select all the required cells, then key in the data and press Ctrl and Enter together.
