Excel is the most popular Excel spreadsheet package. Microsoft Excel is a spreadsheet program included in the Microsoft Office suite of applications. Spreadsheets present tables of values arranged in rows and columns that can be manipulated mathematically using both basic and complex arithmetic operations and functions.
This introduction will introduce you to Excel Basic features using the ribbon tabs to navigate. Excel is, however, is a tool with highly advanced features that I will not be covering in this tutorial, but will do so in a later tutorial.
Excel files are similar to Word documents in how they are created and saved. There is also a variety of Microsoft templates to choose from. Like Word, you can set up shortcuts in the top left of the sheet, including ‘undo’, ‘redo’ and ‘save’, with a dropdown icon to enable you add other shortcuts. Like Word, the ribbon at the top of the worksheet contains the features and commands to customise and formulate your sheet.
Excel workbooks comprise of columns, rows and cells. Which you click on a cell, the column and row location will appear in the overhead box and the column and row will be highlighted.
Parts of the Home tab in Excel is very similar to Word. Clipboard (left) gives you the options to Cut, Copy and Paste. Shortcuts for these commands are Ctrl + X (cut), Ctrl + C (Copy) and Ctrl + V (Paste). Next right, you can change the font style, size and bold, italic or underline cells. There is also a dropdown to add borders – by holding down left click, dragging to select columns & rows, clicking border dropdown and choosing a border style. There is also an option to draw a border around content. To the right if this you can choose background colours and font colours. Clicking the diagonal arrow in the bottom right of the ‘font’ box will give you the full available font customisations.
The ‘Alignment’ section is similar to Word also, where you can choose where to place content inside cells (left, right, centre). You can also drag cursor over a number of cells to merge them by using the dropdown beside alignment. Clicking the diagonal arrow on the bottom right gives full available customisations on font.
The ‘Number’ section allows you for format cells. This can be text, numeric, date, currency etc. You can also choose the number of decimal places to use in this section. Excel defaults show text appearing on the left side of a cell and numeric values on the left.
To illustrate some of what we covered, we can work on the spreadsheet below:
1. Totalling Columns and Rows
Total up the columns (Items A, B and C) and total up the rows for each person.
– Put Cursor on B8 and start typing =SUM (you will see a list of options) Choose SUM, type (B3:B7) – so the cell is formulated at =SUM(B3:B7). This gives the total of the column, allowing you to change values. Alternatively, you can type =SUM( and drag & hold the cursor over the range of values you want to total up, then close bracket.
Now, put the cursor in the formulated cell B8, and hold the keys Ctrl + C (copy), move the cursor to C8 and hold the keys Ctrl + V (paste). This will copy the formula over. Repeat for column D and E. Now complete totals for the five rows ie. =SUM(B3:D3) etc.
In the lower corner of each cell there is a small square. You can copy content from this cell to multiple cells (columns or rows) by holding left click on the square and dragging.
Your Worksheet now looks like:
2. Adding Borders
Underline the row between Carly and Total. We will also put a border around the full content.
Put cursor on A7 – Carly – and drag across to E7, so that row is highlighted. Click the “‘Borders’ dropdown in the Font section and choose “Thick Bottom Border”. Repeat this step for Row 2. Add a ‘Right border for column A and a Left border for column E. Now put cursor in A2, hold and drag to E8. Click on same ‘Borders’ dropdown and choose “Thick Outside Border”.
3. Aligning Text and Numbers
Centre everything in Columns B, C, D and E
Put cursor onto B2, hold and drag to E8 so that selection is highlighted. Click on ‘centre’ in alignment. The worksheet will now look like:
4. Inserting and Deleting Content
Insert a new person, Billy, with Item 1 – 5, Item 2 – 4, Item 3 – 4
Place cursor in the somewhere between Peter and Carly in column A and click the ‘insert’ dropdown in the ‘cells’ section. Click on ‘insert sheet rows’. Add Billy’s details. Provided that you put Billy somewhere between Peter and Carly (not before Peter or after Carly), the formulas in row 8 will change automatically to include Billy’s data. Copy and paste formula from a cell in column E into Billy’s total in column E.
5. Formatting Column Widths
We want to format the column wide to be narrower than it is on the full worksheet. The default width is 10.56 and we will reduce this to 8.
In the top left of the worksheet, there is a triangle in the axis between A and 1. Click on it to highlight the entire worksheet. Alternatively, click Crtl + A. Click on the ‘format’ dropdown in ‘Cells’ section. Click Column Width and change to 8.
If we then decide we would like Column A to be wider, we can do this by clicking on A to highlight the whole column, go to the format dropdown and change the column width to, say 10, just for that column
6. Using Calculation Functions
Illustrate some of the main functions of Excel. In Editing Section, click on the ‘Auto Sum’ Dropdown. Here you will see some main functions, Average, Count Number, Max and Min Values. Type these into column A, Rows 11 to 14.
Put cursor into 11B and Choose ‘Average’ in the Autosum dropdown. You will see Excel making an assumption of which range (overhead) you want to find the average of. As we do not want to include the ‘Total’, mover the cursor to B3, hold and drag to B8 and hit return. Copy and past across rows C, D and E.
Now, using AutoSum, calculate the Count Number, Max and Min values in the same way. Your Worksheet should look life:
6. General Formatting to Improve Worksheet Appearence
- In the ‘Average’ row, we do not want to go beyond two decimal points. Highlight the row by clicking on row 11. Click the arrow in the corner of the ‘Number’ section. This will bring up a window. The first tab, titled ‘Number’ gives you a range of formatting options. Choose ‘Number’ and type 2 into decimal places. Click OK.
- We want to put the people into alphabetical order. Click on Peter and drag the cursor down to Carly, so that the people are all highlighted. In the ‘Editing’ section of the Home tab, click the ‘Sort and Filter dropdown’ and choose ‘Sort A to Z’. You will be asked if you want to expand the selection to ensure the right data is kept in line with the same people. Ensure ‘Expand the selection’ is selected and click ‘Sort’
- Count Number has too many characters for the column. Instead of making the column wider, we can wrap the text to go to the next line. To do this, put the cursor on the Count Number text and in the alignment section, click ‘wrap text’.
- The numbers that correspond to ‘Count Number’ are at the bottom, right of their cells. Also the calculations are defaulting to the right of the cells. Highlight the cells by clicking on B11 and dragging to E14. In the alignment section click on both centre tabs to align.