Tutorial: Cut down the time you spend with your spreadsheets

Start speeding up the way you use Office Excel by setting it up to reflect your personal needs 70-640 Training .

 

Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com

 

Open Options from the Tools menu, File ribbon or Office button, then work your way through the options on offer. Set up how many worksheets you prefer new workbooks to contain from the General section, and choose a default format and location new files are saved to from the Save section.

When entering data, make use of the Fill Handle to dramatically speed up data entry – just type in a figure, then click and drag the fill handle down or to the right to duplicate the number or formula in each of the selected cells.

The Fill Handle can also speed up entering a row or column of consecutive dates, including days, months or years. Type the first entry as normal, then click and drag using the right mouse button – if you’ve typed Monday, for example, you can fill out weekdays or all seven days of the week as you drag by selecting the appropriate option.

The Fill Handle isn’t much use for entering the same figure in non-adjacent cells, but there’s a workaround. Select the cells you wish to include by Ctrl-clicking each, then type the cell reference, number or absolute formula before pressing Ctrl and Return.

Navigating a large spreadsheet can be a real hassle, so instead of manually scrolling to the cell in question, simply type it into the Name box and press Return to jump straight to it.

If you frequently use a specific figure, such as VAT, in your workbook calculations, save time by defining it as a constant – switch to the Formulas ribbon and choose Define Name. Type the term (VAT in our example) into the Name box and its amount (20) into the Refers to box before clicking on OK. Now just type VAT instead of 20 into your formulas, and when the VAT rate next changes, you can update the entire workbook to the new rate simply by changing the figure for VAT in the Name Manager.

The quickest way to enter an unknown formula is to click the fx button and then follow the wizard to choose the appropriate one for your needs. Office Excel 2007 and 2010 users will also find a host of formulas conveniently organised into categories on the Formulas tab.

When copying and pasting formulas from one cell to another, they’re treated as relative, which means the cell references are based on where the cells are in relation to the cell containing the formula. Quickly create absolute formula references using the $ character, so cell A1 becomes $A$1, for example.

Last, but not least, if you need to style your spreadsheet in Office Excel 2007 or 2010, make use of the Cell Styles function instead of manually highlighting or colouring cells. Select an already formatted cell, then click the Cell Styles button on the Home tab and choose New Style. Name it up and click OK – in future, click Cell Styles again to format your cells from the Custom section.

Quick-fire charts

The quickest and easiest way to create a chart in Office Excel isn’t Chart Wizard – just select the cells you wish to include in your chart and then press F11. The Chart Wizard is bypassed and a chart instantly appears on the screen.

Once that’s done, just right-click on the various elements to customise the chart.

Excel shortcuts

Alt and Return: Start a new line in the current cell instead of moving to the next one.

F11: Generate a chart from the currently selected data.

Shift/Ctrl and Space: Select the current row/column.

Alt and =: Total the selected cells.

Ctrl and Shift and $: Format selected cells as currency with two decimal places.

Ctrl and ;: Insert current date.

Ctrl and Shift and ‘: Paste value from the cell above.

Shift and F11: Insert a new worksheet.

Ctrl and PageDown/PageUp: Move between worksheets Microsoft Free MCTS Training and MCTS Online Training.

Ctrl and D/R: Fill cell with the contents of the cell either above (D) or to the left (R).