Excel 2013 lists

By Chris Dillon. Version 1, 16 February, 2013. Latest version at: www.quickanddirty.org.uk

These instructions are for Excel 2013. Most of the procedures are the same in earlier versions.

1. Widening a column

Place your cursor over the column headings (A, B, C etc.).
You can drag the edges of the column heading (when the cursor becomes <-|->) to widen the column.

If you double-click the edge of the column heading, the column widens to fit the widest piece of data in it.

2. Inserting a row or column

Go to the Insert group on the HOME tab and click Insert and then Insert Sheet Rows or Insert Sheet Columns.

Rows are inserted above your cursor. Columns are inserted to the left of your cursor.

3. Copying and moving data

Select the cells containing the data with the mouse or keyboard (hold down the Shift key and use the right and down arrow keys).
Go to the Clipboard group on the HOME tab and click Cut (or Ctrl+X) to move, or Copy (Ctrl+C) to copy the data.
Move your cursor to where you want the data to start. Click Paste in the Clipboard group
(Ctrl-V).
Going to another location and pressing Ctrl+V will copy the data there as well.
The last time you insert the data you can also use Enter instead of Ctrl+V.

4. Selecting a row or column

Select the row(s) or column(s) by clicking the numbers to the left and the letters above respectively.
Alternatively, with your cursor in a cell,

· select the current row with Shift+Spacebar, or

· select the current column with Ctrl+Spacebar.

5. Clearing the data from a row or column

Select the row or column.
Press the Delete key.

6. Deleting a row or column

Select the row(s) or column(s). Right-click and select Delete.

7. Simple calculations

Columns containing numbers for calculations should not contain anything else e.g. headings (except one at the top).

Click in the cell where you want the answer to appear.
Insert an = and then click cells or enter numbers and insert operands [+, -, * (multiplication) and / (division)].

There is a quick way to add — highlight the data to be added up and click the AutoSum icon (Σ) in the Editing group on the HOME tab.

8. Constants

References to cells in Excel are by default “relative references”. This means if you copy the cells somewhere else, any formulae in them will still work.

However, sometimes a formula contains a constant you do not want to change. In this case you need to use an “absolute reference” to the cell containing the constant. So, for example, if you had such a constant in cell A1 you would need to change the default relative reference in any formula, in this example $A$1, to an absolute reference — A1.

9. Series

Excel is good at expanding series automatically. Apart from simple numerical series (e.g. 1, 2, 3 ...), Excel knows the days of the week etc.

Type the first two members of the series in a column or row.
Select the cells and drag the small black square at the bottom left corner of the selection to the right or down respectively.

10. Applying a border to cells

Select the cells.
Find the Borders button in the Font group on the HOME tab. It has four squares.
Click the down-arrow next to them and select the border you want in the palette.

11. Printing in Excel

If your spreadsheet is wide, you may need to change to landscape printing by going to the PAGE LAYOUT tab and clicking Orientation and then Landscape.

Select the cells you wish to print.
Go to Print Area on the PAGE LAYOUT tab and select Set Print Area (otherwise everything will be printed!).

12. Getting a row to print at the top/side of every page

Go to the Sheet Options group on the PAGE LAYOUT tab.
Click the icon at the end of the Rows to repeat at top / Columns to repeat at left field to collapse the dialogue box.
Highlight the rows/columns in the spreadsheet.
Click the same icon to expand the dialogue box, and click OK.

13. Keeping columns and rows visible as you scroll

This is useful for keeping column and row labels etc. visible as you scroll around the data.

To “freeze” the top row(s), go to Freeze Panes in the Window group on the VIEW tab and click Freeze Top Row.
To freeze a column on the left, go to Freeze Panes in the Window group on the VIEW tab and click Freeze First Colum.
To unfreeze rows and columns, Freeze Panes in the Window group on the VIEW tab and click Unfreeze Panes.

14. Inserting, moving and renaming a Sheet

If you need a new Sheet (Microsoft-speak for spreadsheet) in your Workbook, click the + bottom left.
You can reorder Worksheets by dragging their tabs (bottom left).
You can rename a Worksheet by right-clicking its tab and selecting Rename.

15. Importing data

Open the Workbook into which you are going to pull the data.
There are useful options for importing data From Access, From Text and From Other Sources in the Get External Data group on the DATA tab.
To import data from other Excel workbooks, select them and use Ctrl+C (copy) and Ctrl+V (paste) to copy them to your current workbook.