10 Excel® Tips

To select only the constants in a range or just the formulae or all blank cells, make use of the Go To Special dialog box. Press F5 for the Go To box then press the Special button and select an option.
You can use Paste Special options when copying the contents of a cell to add, multiply, subtract or divide the copied values to the destination cell.
If you want the used range of your spreadsheet to stretch across the full width of the window or if your used range stretches outside the window and you want to be able to see all of it in the window, you can set the Zoom value to a selection. Select the columns you want to view in the window then select either the Zoom drop down button on the standard toolbar or the Zoom option from the View menu and choose Selection.
You can enter fractions in Excel by typing 0 followed by a space and then the fraction (e.g. 0 3/8). The cell will show 3/8 but the value of the cell will be the decimal (0.375) thereby allowing you to use the cell in formulas. This is very useful for entering stock market prices such as 700 3/16.
Custom number formats can be very useful. If you often have to type in numbers that have to be in a specific format, such as stock numbers, set up a custom number format. From the Format Cells dialog box (Format, Cells from the main menu bar), select the Number tab then the Custom option under Category. Type in your format in the Type box, e.g. ### ### ### and press OK. Now when entering your numbers, it's much quicker and easier to type them without any formatting. You can then apply the custom number format to the cells afterwards, or have the cell formats pre-set.
Another use for custom number formats is to include leading zeros. If certain figures are in the format 000467, Excel will normally omit the 0s in the cell. If you apply the custom number format 000### Excel will show them.
Yet another use for custom number formats is for adding text to a cell you want to include in calculations, e.g. you want the cell to say "Total Sales: 6000" but you want the 6000 to be the result of a calculation. If you add your formula to the cell (e.g."=sum(A1:A10)") then apply a custom number format of "Total Sales: " #", the text will be added to the cell but won't affect the calculation.
To quickly see a calculation of a range of cells, you don't need to enter a formula. Select the cells you want to calculate then look at the status bar in the bottom right-hand side of the screen. It will show you the sum of the selected cells. You can change the calculation method to Count, Average, Min, Max and Count Nums by hovering the cursor over the result in the status bar and using the right mouse button to display the options.
To create a chart quickly, select the data range and press F11. This will create a chart of your data using Excel's default chart options.
Custom Lists can be very useful for quick data entry. If you regularly enter lists of data such as sales areas, products or departments, add the list to the Custom Lists tab from the Tools, Options menu. Then when you type in one of the list members, you can drag the Fill Handle to copy out the rest of the list. You can also use the custom list as a sort key when sorting data. (From the Data, Sort menu press the Options button and select your list.)

Microsoft, Access, Excel, Visual Basic, Outlook and PowerPoint are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Terms and Conditions