Excel is a huge programme. It’s used everywhere, all day long in business, saving uncountable hours of number crunching.
As a consequnce it is ubiquitous and most people learn a way to do what they need to do and that’s it.
The issue lies in that it is capable of so much more. In the hands of a skilled user of Excel, it can be used for things as diverse as a full CRM system, financial forecasting and even for automating email large numbers of people.
Power BI is a relatively recent addition to Excel and allows you to manage and manipulate your data into easy to use dashboards so that you can visualise your data.
Not everyone wants or needs to be that skilled in Excel, but almost everyone could save some time every day. With that in mind Acuity Training has created this excel course to allow people to teach themselves the basics of Excel.
If you are new to Excel or just new to Excel 2010, this will show you what you will see upon opening up the program. Our Excel Essentials training course assumes no knowledge.
The opening Excel screen is made up of a couple of key elements that are common to all 2010 Office applications – the Ribbon and the Backstage view which is accessed on the ‘File’ tab of the Ribbon
Excel may not appear exactly as the picture above as it depends on the size of your screen. The top section called the Ribbon may look different you may see something like this:
It contains all of the same information but displays it differently, the icons get smaller and some of the labels saying what the icons do disappear. To find this information click on the little down arrows next to the icons/labels or at the bottom right-hand corner of the little box they are in:
They will open little pop-up windows or drop-down menus with lots more choices.
The Backstage looks like this:
This is where you need to go to do things like save, print, share.
As you can see, the ‘Info’ section is highlighted in green, this section is the default whenever you go to backstage. To do any of the other tasks, simply click on the relevant item in the list.
To start using Excel click back on to the ‘Home’ tab. The main working area is called a ‘Worksheet’. This is like an electronic piece of paper split into ‘Cells’ by rows and columns. Each cell has a name, if you have ever played Battleships, you will be familiar with this, the cell is named first by the column and then the row and the name of the cell appear in the left-hand window just beneath the ribbon.
By default, there are 3 sheets in a Workbook. You can move between them at the bottom left-hand corner.
Navigate Excel 2010 without a mouse? This is possible by using KeyTip badges. To find these press the ‘Alt’ key.
You then hit the Alt key and the corresponding letter of what you want to do.
I chose ‘Alt’ and M which will take me to the formulas tab, if I hit the ‘Alt’ key again it will show more choices
To use the Autosum function I hit ‘Alt’ and U, this will give me further choices, but you have the idea now!
One thing to watch out for. Once you have inserted what you want using this method, when you hit the ‘Alt’ key again you will have to start off by selecting the tab you want again, it won’t automatically give you the choices of the tab that you are on.
Firstly, what is the QAT? It stands for the Quick Access Toolbar and is used for quick access to commands you regularly use in Excel, it does what is says on the tin.
See picture below to see where it is. You can add multiple commands to this for all of the tasks you use most frequently. When you click on the arrow, there are a number of commands already on there you can choose from, if you want something else, click on the ‘More Commands’ option.
This will open another box where you can choose to add a very large selection of commands. Notice above the list you can choose which toolbars to select your commands from. On the right, you can also choose whether you want the command to be for all Excel documents or just the sheet you are using.
For this example, I have selected ‘Create chart’
Now when I open Excel, the button ‘Create chart’ will always be easily accessible.
When you open a new file in Excel it is called a workbook. Workbooks are made up of columns which are labelled with letters, and rows which are labelled with numbers. Where a column and row intersect is called a cell. E.g the cell which is in the 3rd column across and the 3rd row down is called C3. When you click on it the row and column label are highlighted and the name appears in the white box, seen highlighted with a pink box in the picture below.
Adding information into cells
You can add text, numbers or a mixture of both into cells. If you wish to perform calculations you can only use numbers. To type into a cell, click on it to highlight it and type away
In the picture below the text has been entered into column A but as there is no text in column B it flows over it making it look as if it also contains text. When you click on a cell you can see in the white box above (labelled fx) all the information it contains.
When you add text it automatically aligns to the left. Numbers automatically align right.
Dates – to enter a date for the current year, all you need to add is this the date in this format 08/06 and it will display it like this:
Again when you click on it, the full date including the year will appear in the fx box
Unlike other members of the Office family, Excel doesn’t underline text you have spelt incorrectly as you go, you have to manually run a spell checker. To find this click on the ‘Review’ tab and the spelling button is on the left.
In Excel 2010 there are a couple of methods to rename your worksheets.
There are various ways you can view a worksheet, the default is ‘Normal’.
To see the views to choose from, go to the view tab and look in the ‘Workbook views’ section.
Page Layout – this shows what the sheet will look like on paper. As you can see below, this worksheet will not fit onto a sheet of A4 in portrait, it drifts on to page two for the last 3 columns. I can either choose to print this in landscape or adjust the column widths to make it fit.
A minor adjustment to the column widths and wrapping the text in the cells and it will fit.
This is a little aside from this, but text wrapping is REALLY useful. You know how when you type in a cell it drifts over and looks like it’s in the next cell too, or the contents are hidden by the next cell? Click on ‘Wrap text’ and it will automatically move itself onto a new line within the same cell so that you can see it 🙂 As you resize the cell it will adjust accordingly however, you may need to make the row deeper at first.
Back to what we should be doing.
If you click back to the ‘Normal’ view from ‘Page Layout’ view, there will be dotted lines showing where the pages are:
Page Break Preview – Quite similar to the page layout view but in this one you can adjust the area that prints without reszing cells.
There are two variations of the line, a dotted and a solid blue line. The dotted line shows what will be printed on one page, the soldi blue line
If you hover over the blue dotted line a little double ended arrow will appear. You can grab this and move the dotted line so that the text fits in. The downside of this is the text will reduce in size when you print it!
Because of the nature of how Excel is used, pages can have huge amounts of data on them. To see a larger area of the data it is possible to zoom out. Obviously it is also possible to do the opposite if you want a closer look at data and want to zoom in. As with most things in Excel, there are a couple of ways to do this:
One way is to click on the ‘View’ tab and use the ‘Zoom’ panel.
Another way is to click on the 100% in the bottom right-hand corner, this will open the ‘Zoom’ pop-up. If you want a small changes of plus/minus 10%, click on the – / + signs and it will increase or decrease in 10% increments.
Most of the time the gridlines are useful to be seen to help you locate cells. Occassionally though, you may want to hide them. To do this use the ‘Show’ panel in the ‘View’ pane. In therethere are several tick boxes where you can choose to show or hide items.
When creating a worksheet from scratch or updating it you will undoubtedly need to add or remove columns and rows at some point.
To insert or delete a column, highlight it by clicking on the letter at the top of it then right-click. There you will see the options to insert or delete. If you insert a column it will be inserted to the left of the column you have highlighted.
If you insert a row in this way it will be added above the row you have highlighted.
You can insert cells, although personally I think this ought to be called shifting/moving cells rather than inserting as it doesn’t actually insert anything.
Highlight the cells you want to move, right-click and select ‘Insert’. You will then have the choice of shifting the cells either down or right.
It will then look like this:
Use this if you wish to remove the contents, formulas and formatting of a cell or range without removing the whole row/column.
Take the same steps as inserting a cell above but choose ‘Delete cells’ . This will open the delete box and you will get the choice to shift cells left or up. This moves any content either to the right or below into that space.
Excel has many 1000’s of columns and rows which can hold a massive amount of data, however, it is sometimes easier to separate certain data out into different worksheets. If you look in the bottom left-hand corner you can see tabs, just like you can get separator tabs in a ring binder. To view any of the sheets, just click on the name there.
There are three ways you can edit data in Excel – overwrite, edit and delete.
This is the easiest way to change existing data. Click on the cell you want to change and just type and press enter. It will automatically replace the contents with the newly typed data.
To edit a cell either double click on it or press F2 on the keyboard. Excel will show you that the cell can be edited in the bottom left-hand corner of the window.
When it is editable you either type directly into the cell or click on the cell contents bar above (see picture).
To save the changes press enter or click onto another cell.
If you have a spreadsheet of figures with functions and formulas in it, you may want to check what other information is dependant on the data you want to delete before you delete it.
Select the cell you want to delete the contents from and click delete on the keyboard. This will purely delete the contents, all of the formatting will remain the same. You can do the same by right-clicking the cell and selecting the ‘clear’ option.To have a choice of what you would like to clear from a cell use this option.
In the editing group on the home tab, there is a little eraser (rubber) icon with clear next to it. Click on that and you will get a drop-down list of options.
A range is a group of 2 or more cells selected/highlighted at the same time.
A range is named by using the name of the cell top left and the name of the cell bottom right.
E.g this would be called B2:D15
The cell name B2 showing up in the name box is the first cell I clicked on when I highlighted this range.
Ranges in one block are called a Contiguous range.
It is also possible to have non-contiguous ranges. To do this, select the first range as per normal, then click the Ctrl key and make the second selection still holding down the Ctrl key. My selection below would be written like this: B2:B16,D2:D16
When a range is selected you are able to see some simple calculations based on the selection. These are in the status bar along the bottom of the Excel window.
Selecting/Highlighting Contiguous Ranges
This almost sounds too simple to bother reading, but do. I had been using Excel for years before I discovered these shortcuts that make selecting ranges so much simpler, especially for larger ranges.
Select a range of cells in the row/column immediately next to your active cell in any direction. This will select all cells in the row/column until it reaches an empty cell.
Select a starting cell, press and hold SHIFT, END and the arrow key in the direction you want to highlight. I have highlighted the start cell in yellow below. This will also work right to left and bottom to top.
To select a very large area of data the easiest way to do it is:
Select the top left hand cell that you want included then CTRL/SHIFT/END
This will highlight all the filled in cells both across and down
Formula = a calculation you enter yourself
Function = a pre programmed formula, commonly shortened to ‘fx’
When you enter a formula into Excel you have to let Excel know that it is a formula and that it needs to do something. The way to do this is to put an = sign in the cell first, nothing will happen with it otherwise!
To perform a calculation you need to enter each figure into its own cell and then have a cell with the calculation in.
E.g. 5 + 6
Enter 5 into cell B2, enter 6 into cell B3
In cell B4 you can enter the calculation =B2+B3
Excel will automatically calculate the total. When you click on cell B4 you can see the formula in the formula bar, prefixed with fx.
This seems like a lot of work for a simple calculation but once you have done it, you can change the numbers in cells B2 & B3 and Excel will always give you the answer in B4
This format can be used for all calculations – addition, subtraction, multiplication and division.
You will need to know the symbols that Excel/computers use for multiplication and division as they look slightly different:
Multiplication * Division /
E.g. 20 divided by 5 looks like 20/5 and 3 multiplied by 7 looks like 3*7
For more complex calculations, you need to use the BODMAS rule of arithmatic as that is the order Excel uses.
A simple calculation can have two answers unless BODMAS is applied.
e.g. 5+ 2 * 10 could equal 70 or 25. The correct answer is 25
The multiplication is 2nd level so do that first and then do the level 3 addition
A slightly more complicated example: 3 – 4 /2 + 6*5
You have to to the multiplication and division BEFORE any addition and subtraction
4/2 = 2
6*5 = 30
So it now looks like this 3 – 2 + 30
Addition and subtraction are completed from left – right so the answer is 31.
If there are brackets in a calculation, they have to be worked out first, everything else still follows BODMAS
10+(10*2)/5 = 14
(10*2) = 20 – 1st level
20/5 = 4 – 2nd level
10+4 = 14 – 3rd level
Functions are there to make using large formulas easy. Imagine having to add up 100 cells, that’s an awful lot of typing and the chance of making an error increases.
To make life easier this and many, many more formulas covering a wide range of categories from statistics through to engineering have been added in Excel as automated functions.
The sum function, for instance, can reduce this:
Under the ‘Formulas’ tab in Excel you will find the ‘Function Library’ where you can choose from all of the available functions divided up into their categories.
From here you can click on insert function, the FX button, and this box will pop up:
Here you are able to search for a function to do what you want or search the list in the category section to find it. You can also list all available functions. I have just Googled how many functions there are in Excel 2010 and it says 400!
If you are familiar with the function you want to use you can just type it directly into the cell. You still need to put the ‘=’ sign in first, the same as you do with a formula.
This is the most commonly used function in Excel and there are so many ways to use this, I will show you the way I find easiest.
When you have clicked in the cell where you want the total to be, click on ‘AutoSum’ and then ‘Sum’. This will insert the function.
As you can see, it puts a dotted line (marquee) around the cells it thinks you want to add, if this is correct press the ‘Enter’ key. If it is not, if you hit ‘Backspace’ it will remove the cell references but keep the function. You can then either highlight the cells you want with your mouse, or enter the range manually.
To enter it manually
What is non-contiguous?? It means ranges and cells that are not next to each other, they can be spread all around your worksheet and you can sum as many of them as you wish.
To sum the cells in the picture above I first clicked in cell B26, type in =sum( , or click on the the sum button, hold the ‘CNTRL’ key down and click on B9, B14, B19 and B24 and press enter.
To save entering the almost identical formula into each column to total them, you can drag the formula across the cells and it will automatically adjust to sum the cells in the same rows but in the new columns. To do this click on the cell with the formula and hover over the bottom right-hand corner a little + sign will appear, when it does, hold down the left mouse button and drag the formula across as many cells as you need it.
Unfortunately the + sign doesn’t capture in the screen shot below.
The AVERAGE function allows you to calculate the average value of a range of cells. It is used in the same way as SUM
MAX and MIN both work in identical ways, I have highlighted the cells in blue and green to show it picks up the values from the selected ranges.
When you enter a formula into Excel, it recognises it as a pattern as opposed to numbers.
E.g. This simple addition formula is asking Excel to remember to add up the 4th cell to the left – B6, 3rd cell to the left – C6 , 2nd to the left – D6 and 1st to the left E6 relative to the cell the formula is in. The really good thing about this is, if you want the same formula anywhere else you can copy and paste it.
You can copy and paste formulas or just drag them (grab bottom right-hand corner of call and drag) into the cells you want them in. The cells the formula refers to will change to match where you have copied it to. See below, formula copied from F6 and pasted into F7. It now refers to cells in row 7.
Excel has a variety of powerful tools in it’s toolbox to help you see what is going on in a spreadsheet with formlas. Which cells are using the information in them and where the information within the cell comes from, they can even help you trace errors.
This shows where the cell is getting the information from that it is using in the formula
To see this click on the Formulas tab, then click into the cell you want to know about, I did two in the picture below – the first one was cell F7, then click ‘Trace Precedents’ .
This will add the blue boxes and the arrow showing which cells are being used in the formula in that cell. The formula itself can be seen in the function bar above.
For the second one I clicked into cell F13 and then clicked ‘Trace Precedents’.
If you changed a cell, what other cells would it effect? What cells are using this info?
To see this, click on the Formulas tab, click on the cell you want to know about and then click on the ‘Trace Dependants’
In the picture below you can see that the ‘12%’ in cell G2 is used in the calculations in cells B3 – M3
Excel will automatically colour code the arguments in a formula to help you identify its relationship with other cells.
Double click on the cell with the formula and it’s contents will show in the formula bar, different colours for different cells. If a cell is referenced twice it will keep the same colour in both places. You are able to drag and drop the colours to a different cell to change the formula.
Excel will show you at the click of a button where your spreadsheet errors are. The example we have is a very small simple worksheet and it’s easy to see the errors, but imagine how useful this will be on large complicated spreadsheets.
With the spreadsheet open that you want to check, click on the ‘Formulas’ tab and then ‘Error Checking’
This is what will open up, it will go to the 1st error on the sheet.
Click on the ‘Edit in formula bar’ and correct it. Whether you make any changes or not you will be given the option to ‘Resume’ in the error box and it will continue looking for errors.
This will help identify which cells are being used for the formula and which ones are causing the errors. When you use this, red arrows show the the linked cells where there is an error and blue cells show linked cells where there is no error. Here is where you find the button:
In the diagram below cell L7 has the error. By clicking on cell L7 and clicking ‘trace error’ It shows in blue the cells that are involved but do not have errors in them.
By clicking on cell L8 and clicking ‘trace error’ the red arrow shows that the error in that cell is caused by an error in cell L7.
You will find this button under the formulas tab in the formula auditing section.
It seems a bit confusing at first, but once you understand how it works, it’s a great tool.
This is the spreadsheet I will be using for this example:
We first click on cell F7 and click on evaluate formula, this is what appears:
It shows the two cells that are used in the formula in F7. See how E7 is underlined under the pink dot? By clicking evaluate it will show the value of that cell:
It automatically then moves over to the next cell in the formula, E19
Click evaluate again and it will show the value of E19, in this case zero.
Click evaluate again and is will show the error message from the cell and you have the option to restart.
After you click on restart ‘Step In’ will be highlighted. Think of stepping in and out as moving down and back up levels. Click on that and it will show you the formula that F7 has in it, which is E7/E19 click again and it will show you the formula of the first cell within the cell E7 which is D7*C7
Click step in (drill down to the next level) again and it will show you the value of that cell, the only option then is to ‘Step out’
Remember that the starting cell for all of this was F7
When you click Step Out (move back up a level) you will then have the option to step in to C7 or to ‘step out’ another level. I have ‘Stepped out’ back to where cell E19 is underlined and I have then ‘Stepped In’. You can see from the picture below that cell E19 is empty which is causing the #DIV/0! error as you cannot divide by 0. You can now correct the error and move on to the next one.
This may seem very long from reading this, but in practice is quick.
On a larger worksheet, you can use the watch window to see cells that are not in the main view so you don’t need to keep scrolling to see them or moving to different parts of the worksheet.
In this picture, the total is in cell C280 which cannot be seen at the top of the worksheet. By clicking it, then pressing the ‘Watch Window’ button, which is greyed out in this image as I have gone past that, it will open another window. Click ‘Add Watch’ and the cell that you had selected in the worksheet will be in the info bar. Click ‘Add’ the top little window will close and the info will appear in the main ‘Watch Window’.
If you want to you can dock this to the top of your workbook by double clicking the title bar of the watch window.
You will now be able to see the effect of any changes that are made to figures in column C
I have just discovered that if you open a worksheet with a circular reference in it, a warning message will pop up. This message explains exactly what a circular reference is:
Excel will display in the bottom left-hand corner that the sheet has this error and where it is:
As you can see cell E18 looks empty but when you click on it you can see the formula in the formular bar. It is not dislaying a total because of the circular reference. Click in the formula bar and change E18 to E16 and everything will work correctly again.
When you want to format any fonts in Excel you can see in live preview what it will look like before to choose. Highlight the cells you want to change, click on the font button and the pick list will appear, just scroll over them and your selection will change to that style of font withoug doing anything else. When you have decided which style you want, click on the font name and your choice will be applied. This will also work for changing font size, font colour and cell fill colour.
There are a couple of ways to do this. If you know the size of the font you want you can select it from the font size box. See picture below to see where.
If you are unsure of the size you would like, next to the drop down font size box there are two letter A’s. One larger with an up arrow next to it and one slightly smaller with a down arrow next to it. These buttons will change the font up and down one size each click. To change your font size, hightlight the cells and then either choose the font size from the drop down list or click on the relevant ‘A’ button until you reach the desired size.
The picture above shows where you can do certain things:
This is the tool I probably use most out of the Office suite as it’s the same in all the programs.
Once you have formated a cell exactly how you want it, you can copy all of the formatting and add it to a different cell, the contents remain the same, it’s only the formatting that changes. Click on the cell and then right click, look for the box with the little paint brush symbol and click on it.
Your cursor will turn into a little cross with a paintbrush next to it, when you click onto a cell the formats will be added to that cell
Excel number formatting makes numbers easier for the reader to see what type of number is in a cell, e.g. a percentage, currency, date. It doesn’t change the number that is in the cell.
Even a date is stored as a number – 3/07/2015 is stored as 42188, but Excel is clever enough to realise when a date is being entered and will automatically format it as a date. For a date in the current year you don’t even need to enter the year, by entering 03/07 Excel will add the 2015 (or whatever year you are in) automatically.
Below I have formatted the same number in each cell to show some of the commonly used quick number formats found in the number format drop down box.
Each cell has 3.5 typed into it, only the formatting is different. To format click on the the ‘General’ drop down list in the number format tool group and then select the type of formatting you want to apply.
You will see at the bottom of the drop down menu there is a ‘More number formats’ option
This box will open when you click on it:
From within here you can have far more flexibility about the formatting you are applying. Explore and see what you can find.
Excel has to be one of the trickier programs to print from. In others like Word, you are working on a certain size ‘piece of paper’ from the start. In Excel you can just keep adding as many columns and rows and you want and the view you see on screen will not necessarily bear any resemblance to what will print. Our lunches spreadsheet, for example, looks like this on screen:
To see what will print you can click on ‘File/print’ and it will open up this preview, as you can see, very little of the sheet will print on one page:
If it not showing what you would like it to, take a look here at the different options of changing what will print from within the worksheet.
Unless you want to print your whole worksheet you will need to specify what area you want printed, you do this by selecting a range. To do this simply highlight the cells you wish to print File/print/settings/print selection
As you can see, from this drop-down list you do get the option to print the whole workbook and only active sheets.
Above the printer choice, you have the option to specify how many copies you wish to print.
Page Layout is used to describe the group of commands that control how a spreadsheet will appear when printed. The most popular commands are:
There is a choice of three built-in margin settings: Normal, narrow and wide. To get here choose page layout/margins
If you take a look at the picture above, you will see that right at the bottom of the drop-down list is a ‘Custom Margins’ choice
If you click this, this pop-up box appears:
Here you are able to adjust the margins to your exact requirements. You are also able to centre your image on the page here, either horizontally, vertically or both.
To do this you need to go to the ‘Page ‘Layout’ view. There is a tab called Page Layout, DON’T USE THIS. Please see picture below for how to get to the correct place: View/Page layout
With your mouse you can then grab the margins and move them around. When you have got a hold of it, you will see a thin dotted line, wherever you let o of it, that is where the new margin will be. If you make a mistake, just hit CLTRL Z to undo it.
By default, Excel will print in the top left-hand corner of the page. If you want it to print elsewhere you need to tell it to do so. To centre it on the page click:
Page Layout / Margins this will open a little popout box and you need to click ‘Custom Margins’. This will open the box you see in the picture below. You will see on the lower left-hand side the options to centre horizontally and / or vertically.
When you have centred both, it will look similar to below.
You will also see in the top picture in this section you have the opportunity to decide which way round you want your paper.
There are a wide variety of standard paper sizes to choose from when you click the ‘Size’ button and you also have the opportunity to set your own custom size.
The default print area for Excel is ALL of the data. Unless you have set up your pages this can make for a very confusing pile of paper, especially if you have lots of columns.
Another way is to set the print area. To do this highlight the data you want to print, click on the page layout, print area, set print area.
The area will then have a dotted line around it. Once this is set the data can be filtered, sorted. Ideal if you wanted to print a ‘top 10’ and had to add new data each day / week.
To clear the print area, see the 1st picture in this section and you will see where to do this. It is right next to the command to set the print area.
Excel will create it’s own page breaks, based on the size paper you have selected. However, you can choose to insert your own.
In the picture below I have chosen cell A17 to print the Achieve the Gold Standard in Customer Service section on a separate page. To get there, select tab page layout, breaks, insert page break. You can see beneath the insert choice there is a remove page break and reset all Page Breaks choice too.
If you wish to see an overview of the breaks navigate to view/ page break preview. From here you are able to drag the thick blue lines with your mouse to reset the page breaks
The background is the area behind any data or charts that you add to a worksheet and is white by default. If you fancy a change you can insert graphics/ photographs / company logo pictures into the background. They are inserted at their default size and cannot be adjusted once they have been inserted. If you wish them to be more transparent you will need to have them ready like that before inserting. As you can see below this logo is far too big and solid to be used..
To insert or delete a background – Page Layout and then a background in the page set-up group.
When printing long spreadsheets Excel will only print column headings on the 1st page by default. If you have a long spreadsheet, trying to remember what all of the column headings are can be quite confusing.
To print them on every sheet do the following: Page Layout/ Print Titles Excel will automatically put row 1 in there but you can click in there and choose any row.
Gridlines make reading and making sense of a large amount of data far easier. They show on screen by default but are not always automatically printed.
Below you can see that if you navigate to Page Layout / Sheet Options you can choose whether to view and print your gridlines. Next to it, you will also see you have the same choices with headings. Headings are the letters across the tops of the columns and the numbers down the left-hand side of the rows.
From the picture above you can see that you are also able to scale your spreadsheet by a percentage to fit your paper. You can see the changes on your screen as the dotted lines will move each time you change the scale.