Introduction To Excel: A Complete Guide
Everyone Knows How To Use Excel Right?
Excel is a complex spreadsheet developed by Microsoft. It’s used in most businesses, saving uncountable hours of number crunching. Therefore, you can find it on almost every PC and smartphone.
However, most people only use Excel to get a specific job done, leaving most of the territory unexplored. In the hands of a skilled Excel user, this program can be used to build an entire CRM system, make financial projections, and generate automated emails for a large number of people.
A relatively recent addition to Excel is Power BI, which allows you to manage and manipulate your data into easy-to-use dashboards, facilitating the visualisation of your data.
Although striving to ‘excel’ in Excel isn’t something everyone necessarily must do, learning the know-how of Excel can save a lot of your time every day in front of your PC. With that in mind, Acuity Training has created this Excel course to allow people to learn the basics of Excel.
The Excel Screen
If you are new to Excel or just new to Excel 2010, this section will show you what you will see upon opening the program. Our Excel Essentials training course assumes that you have no knowledge of the software as you start.
When you open Excel, there are a few key elements on the screen that are common to all 2010 Office applications – the Ribbon and the Backstage view.
Excel may not appear exactly as it looks in the picture above, as it depends on your screen size.
The top section of the screen is known as the Ribbon, and it may look different on different devices or Excel versions.
It contains all of the same information but displays it differently, i.e., the sizes of the icons differ, and the labels disappear on smaller screens. To find this information, click on the little downward-pointing 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 more choices.
The Backstage view looks like this:
You can access the Backstage view by clicking on the File tab of the Ribbon.
This is where you must go if you want to save, print, or share your Excel file.
As you can see, the Info section is highlighted in green; Excel selects this section by default whenever you go to Backstage view. To do any of the other tasks, simply click on the relevant item in the list.
To start using Excel, click on the Home tab.
The main working area is called a Worksheet. This is like an electronic piece of paper split into Cells using rows and columns.
Each cell has its own name. If you have ever played Battleships, you will be familiar with this. The cell’s name has two parts: a letter that indicates the column and a number that specifies the row. You can see the name of a cell in the left-hand window just beneath the Ribbon.
By default, there are three sheets in a Workbook. You can move between them at the bottom left-hand corner.
Want to navigate Excel 2010 without a mouse? This is possible by using KeyTip badges.
KeyTip badges are very simple to use: Press the Alt key and then the letter corresponding to the action you want Excel to take.
In the image above, I hit the Alt key and then the letter M to go to the Formulas tab.
To use the Autosum function, I hit the letter U on my keyboard. This gives me further choices that I can choose from.
However, a word of warning: Once you have inserted something using KeyTips and hit the Alt key again, you will have to start off again from scratch when selecting the letters.
Working With And Customising The QAT
Firstly, what is the QAT? It stands for Quick Access Toolbar and is used to help you quickly access commands you regularly use in Excel.
You can find the QAT in the topmost section of your Excel screen. This section usually has the Save and Undo icons.
You can add more commands to your QAT. When you click on the arrow, as seen in the picture below, a drop-down list appears with a number of commands you can choose from. If you want a different command, click on the More Commands option.
This will open another box where you can choose to add an extensive selection of commands. You can even reduce your search time by specifying the toolbars to select your commands from.
On the right, you can also choose whether you want the command to stay in your QAT for all your Excel documents or just the sheet you are using.
For this example, I have selected the Create Chart command.
Now, when I open Excel, I can see that the Create Chart command is always easily accessible to me.
Creating A Workbook
When you open a new file in Excel, it is called a workbook. Workbooks are made up of columns that are labelled with letters and rows that are labelled with numbers.
A column and row intersect to make a cell. We discussed in section 1 how cells are named in Excel. For example, a cell in column C and row 3 is called C3. When you click on it, the row and column label are highlighted, and the name appears in the white box as highlighted below.
Adding Information To 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, we have entered some text in column A, but as there is no text in column B, it flows over it, making it look like it also contains text.
When you click on a cell, you can see all the information it contains in the white box above (labelled fx).
When you add text, it automatically aligns to the left. Numbers automatically align to the right.
Dates – to enter a date for the current year, all you need to do is to add the date in this format: 08/06, and it will display it like this:
When you hit Enter, the full date, including the year, will appear in the fx box.
Unlike other members of the Office family, Excel doesn’t underline text that you have misspelled as you go. For this reason, you have to run a spell checker manually. To find the spell checker, click on Review > Spelling.
Renaming A Worksheet
In Excel 2010 there are a couple of methods to rename your worksheets.
- You can double click on the worksheet tab name, it will then highlight in black:
- When that happens you can then overtype the name that you want it to be
- The other method is to right-click the tab, a pick-list appears and you can click ‘rename’. That will then do the same black highlight and allow you to overtype the new name in.
There are various ways you can view a worksheet, the default is ‘Normal’.
To see the views you can 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 onto 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 make it fit.
As a little aside, text wrapping is REALLY useful. You know when you type in a cell and the text drifts over, looking like it’s in the next cell too, or the contents are hidden by the next cell? Click on ‘Wrap text’ and the overflowing text will automatically move 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 first.
Back to what we should be doing.
If you click on ‘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 resizing cells.
There are two variations of the line—a dotted and a solid blue line. The dotted lines show automatic page breaks, and the solid blue lines manual page breaks.
If you hover over a 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 change of plus/minus 10%, click on the – / + signs, and it will increase or decrease in 10% increments.
Viewing The Gridlines And The Ruler / Formula Bar / Headings
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. There, you will find several tick boxes where you can choose to show or hide items.
Inserting & Deleting Columns / Rows and Cells
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.
It’s possible to 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.
Switching Between Worksheets
An Excel spreadsheet can hold thousands of columns and over a million rows, which totals a massive amount of data. However, it is sometimes easier to separate data 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 its name.
Editing Your Data
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 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 dependent 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 the formatting will remain the same. You can do the same by right-clicking the cell and selecting the ‘Clear’ option. To choose 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.
Working With Ranges
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 click CTRL/SHIFT/END.
This will highlight all the filled-in cells both across and down.
Excel Formulas & Functions
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 it.
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 arithmetic 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 do 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 to 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 first; the same as you do with a formula.
How To Use The SUM Function To Add
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
- Type =sum(
- either type in the first cell name or click on the cell
- Click on or type in the next cell to sum
- Type ) and press ‘Enter’
- NB if you are typing the cell names in, you will need commas between each cell name.
Summing Non-Contiguous Ranges & Cells
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, you first need to click on cell B26 and then type in =sum(. Or you click on the sum button, hold the ‘CTRL’ 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 show up in the screenshot below.
Calculating An Average, Finding The Max And The Min Values
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 how it picks up the values from the selected ranges.
Relative Cell References In Excel Formulas
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 the bottom right-hand corner of a cell 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 the formula copied from F6 and pasted into F7. It now refers to cells in row 7.
Formula Auditing In Excel
Excel has a variety of powerful tools in its toolbox to help you see what is going on in a spreadsheet with formulas. 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 on 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 on cell F13 and then clicked ‘Trace Precedents’.
If you changed a cell, what other cells would it affect? 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 ‘Trace Dependants’.
In the picture below you can see that the ‘12%’ in cell G2 is used in the calculations in cells B3 – M3.
Showing Formulas & Cell Arguments
Excel will automatically colour code the arguments in a formula to help you identify their relationship with other cells.
Double click on the cell with the formula and its 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.
Common Error Messages
Excel will show you at the click of a button where your spreadsheet errors are. The example we have is a very small and simple worksheet, and it’s easy to see the errors, but imagine how useful this will be on large complicated spreadsheets.
With the spreadsheet that you want to check open, 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 ‘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 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 it 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 into 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 long-winded based on the explanation, but in practice, it’s a quick process.
Learn To Use The Watch Window
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’, then 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.
Dealing With Circular References
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 formula bar. It is not displaying a total because of the circular reference. Click in the formula bar and change E18 to E16 and everything will work correctly again.
Excel Font Formatting
Working With Live Preview and Changing Fonts
When you want to format any fonts in Excel, you can see in live preview what it will look like. 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 without 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.
Changing Font Size
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 for each click. To change your font size, highlight 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.
Changing The Appearance Of Text
The picture above shows where you can do certain changes:
- Make text bold
- Italicise text
- Fill cell colour/change background colour
- Change font colour
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 formatted 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 paintbrush 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
Excel number formatting makes numbers easier for the reader to see what type of number is in a cell, e.g., a percentage, currency, or 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 year 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 ‘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.
The Basics Of Printing
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 as 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 is 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 at 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.
Customising Page Layout
Page Layout is used to describe the group of commands that control how a spreadsheet will appear when printed. The most popular commands are:
- Repeated Titles
- Headers and Footers
- Orientation and Paper Size
Margins – Built-In
There is a choice of three built-in margin settings: normal, narrow and wide. To get here choose Page Layout/Margins.
Setting Custom Margins
If you take a look at the picture above, you will see that right at the bottom of the drop-down list there 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, either horizontally, vertically or both.
Changing Margins By Dragging
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 go of it, that is where the new margin will be. If you make a mistake, just hit CLTRL Z to undo it.
Centring On a Page, Paper Orientation & Setting Paper Size
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 the below.
You will also see in the top picture in this section that 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.
Setting The Print Area
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 and sorted. Ideal if you wanted to print a ‘top 10’ and have 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.
Inserting / Removing Page Breaks
Excel will create its own page breaks, based on the size of 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 the 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.
Setting a Background
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 them. As you can see below this logo is far too big and solid to be used.
To insert or delete a background go to Page Layout and then pick a background in the Page Setup group.
Setting Rows As Repeating Print Titles
When printing long spreadsheets in Excel, it 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 go to Page Layout/Print Titles. Excel will automatically put row 1 in there, but you can click in there and choose any row.
Printing Gridlines & Headings
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.