Excel Training Courses – 5 Great Excel Tips and Tricks

Five useful tips to help overcome common excel issues. These tips include shortcuts for formatting numbers, absolute cell references, wrapping text in cells, adding dates to spreadsheets and widening columns.

1) Shortcuts for Formatting Numbers

Here are a few useful keys that can be used when formatting numbers in Microsoft Excel:

  • Select the cell (s) containing the numbers you wish to format.
  • To apply the Currency style, press CTRL SHIFT and $ on the keyboard
  • To apply the Comma style, press CTRL SHIFT and! on the keyboard
  • To apply the Percentage style, press CTRL SHIFT and% on the keyboard
  • To apply the General style, press CTRL SHIFT and # on the keyboard

2) Absolute Cell References

An absolute cell reference in a formula, such as $ E $ 1, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust.

By default, new formulas use relative references, and you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell C3 to cell C4, C5 and C6 it holds the same in all cells:

To make an absolute cell reference within a formula, either manually type in the dollar signs or use the shortcut key of F4.

3) Wrapping Text in Cells

If you want text to appear on multiple lines in a cell, you can format the cell so that the text wraps automatically, or you can enter a manual line break.

To wrap text automatically:

  • Select the cells that you want to format.
  • Within Excel 2007 – On the Home tab, in the Alignment group, click Wrap Text.
  • Within Excel 2003 or earlier – Click the Format Menu, select Cells, select the Alignment tab, select the Wrap Text check box.
  • Data in the cell wraps to fit the column width. When you change the column width, data wrapping adjustments automatically.

To enter a line break:

  • To start a new line of text at a specific point in a cell – double-click the cell, click the location where you want to break the line in the cell, and then press ALT + ENTER.

4) Adding dates to Spreadsheets

If you require dates to be displayed on a spreadsheet, using AutoFill saves you having to type in each individual date.

For example, let's say that you need every day in September 2009 …

  • Start by typing in the first date ie 01/09/2009
  • Move the mouse cursor to the bottom right corner of the cell until it changes to a black cross.

In addition to the above, Excel can increment the date by any amount of days / months / years. For example, you may require every Monday's date for a whole year starting from the 7th September 2009 …

  • Type the first Monday's date in one cell, and then the following Monday's date in the next cell.
  • Select both cells.
  • Move the mouse cursor to the bottom right corner of the cell until it changes to a black cross.

Note: Excel knows how many days there are in each month as well as when there is a leap year.

5) Widening Columns

Widening columns in Excel can be tricky especially if you have a lot of data and can not see it all on the screen. Applying AutoFit to columns is a very quick way of getting Excel to automatically widen the column (s) based on the longest cell entry. Here's how it's done:

  • Position the mouse cursor to the right of the column header until the mouse cursor changes.
  • Instead of dragging the column to widen it, double-click with the left mouse button.
  • This will apply AutoFit and the column should increase / decrease to fit the longest entry.

Note: to apply AutoFit to all columns, firstly press Ctrl & A on the keyboard which selects the whole sheet.


Comments are closed here.