Transcript
Table of Contents Getting Started with Excel 2007 ................................................................................................................... 5 Excel 2007 Application Window ............................................................................................................... 5 Excel 2007 Office Button ........................................................................................................................... 6 Quick Access Toolbar ................................................................................................................................ 6 Excel 2007 Ribbon ..................................................................................................................................... 9 Excel 2007 spreadsheet ............................................................................................................................ 9 Excel 2007 Formula Bar ........................................................................................................................... 10 Excel 2007 Status Bar .............................................................................................................................. 11 Live Preview and the Mini Toolbar ......................................................................................................... 12 Excel 2007 keyboard shortcuts ............................................................................................................... 13 Creating a Workbook .................................................................................................................................. 15 Creating a new workbook ....................................................................................................................... 15 Navigating a worksheet .......................................................................................................................... 16 Excel Mouse Pointers .............................................................................................................................. 16 Entering and Editing Data ....................................................................................................................... 17 Types of data ........................................................................................................................................... 17 Changing the width of a column ............................................................................................................. 18 Editing a cell ............................................................................................................................................ 18 Undo and Redo ....................................................................................................................................... 19 Selecting Cells ......................................................................................................................................... 19 Autofill ..................................................................................................................................................... 19 Custom Lists ............................................................................................................................................ 20 Saving the workbook ............................................................................................................................... 22 Saving in a Previous Version ................................................................................................................... 23 Opening an Existing Workbook ............................................................................................................... 25 Closing Excel and the Workbook ............................................................................................................. 26 Editing a Worksheet .................................................................................................................................... 27 Cut, Copy and Paste ................................................................................................................................ 27 Cut and Paste ...................................................................................................................................... 27
1
Copy and Paste .................................................................................................................................... 28 Move and copy cells using the mouse ................................................................................................ 28 The Office Clipboard ............................................................................................................................... 29 Cell Comments ........................................................................................................................................ 30 Inserting a comment ........................................................................................................................... 30 Editing a comment .............................................................................................................................. 31 Delete a comment ............................................................................................................................... 31 Paste Special ........................................................................................................................................... 31 Consolidation Options ......................................................................................................................... 33 Skip Blanks .......................................................................................................................................... 33 Transpose ............................................................................................................................................ 33 Paste Link ............................................................................................................................................ 33 Writing Formulae ........................................................................................................................................ 33 Using Formulas ........................................................................................................................................ 33 BODMAS .................................................................................................................................................. 34 Excel Sum Function ................................................................................................................................. 35 Functions ................................................................................................................................................. 36 Excel Statistical Functions ....................................................................................................................... 37 Count ................................................................................................................................................... 37 Average ............................................................................................................................................... 38 Min ...................................................................................................................................................... 38 Max ..................................................................................................................................................... 38 Entering statistical functions ............................................................................................................... 38 Relative and Absolute Cell References ................................................................................................... 39 Formatting a Worksheet ............................................................................................................................. 41 Formatting Labels ................................................................................................................................... 41 Formatting Values ................................................................................................................................... 42 Cell Alignment and Orientation .............................................................................................................. 44 Cell alignment ......................................................................................................................................... 44 Merge cells .............................................................................................................................................. 44 Cell orientation ....................................................................................................................................... 45 Wrap text ................................................................................................................................................ 46 2
Borders and Shading ............................................................................................................................... 46 Adding a border .................................................................................................................................. 46 Removing a border .............................................................................................................................. 48 Adding a fill colour .............................................................................................................................. 48 Adding a pattern or fill effect .............................................................................................................. 48 Cell Styles ................................................................................................................................................ 49 Applying a cell style ............................................................................................................................. 49 Removing a cell style ........................................................................................................................... 50 Modify a cell style ............................................................................................................................... 50 Creating a new style ............................................................................................................................ 51 Format Painter ........................................................................................................................................ 52 Clear Cells ................................................................................................................................................ 52 Managing Worksheets ................................................................................................................................ 53 Inserting and Deleting Rows, Columns and Cells .................................................................................... 53 Inserting rows on a worksheet ........................................................................................................... 53 Inserting columns on a worksheet ...................................................................................................... 53 Inserting cells on a worksheet ............................................................................................................ 54 Deleting rows, columns and cells ........................................................................................................ 54 Insert and Delete Worksheets ................................................................................................................ 55 Inserting a worksheet ......................................................................................................................... 55 Deleting a worksheet .......................................................................................................................... 56 Customizing Sheet Tabs .......................................................................................................................... 56 Moving and copying sheets ................................................................................................................ 56 Renaming a sheet tab ......................................................................................................................... 57 Format the sheet tab colour ............................................................................................................... 57 Splitting and Freezing Panes ................................................................................................................... 58 Splitting panes on a worksheet ........................................................................................................... 58 Freezing panes on a worksheet .............................................................................................................. 60 Printing a Worksheet .................................................................................................................................. 61 Print Preview ........................................................................................................................................... 61 Add a Header and Footer ........................................................................................................................ 62 Insert header and footer elements ..................................................................................................... 63 3
Use auto headers and footers............................................................................................................. 63 Adjusting Sheet Margins and Orientation .............................................................................................. 64 Adjusting the margins ......................................................................................................................... 64 Changing the page orientation ........................................................................................................... 65 Adjusting size and scale .......................................................................................................................... 66 Adjusting the paper size ...................................................................................................................... 66 Scaling to fit......................................................................................................................................... 67 Scaling to fit from Print Preview ......................................................................................................... 68 Set the Print Area .................................................................................................................................... 69 Clear the print area ............................................................................................................................. 69 Hiding columns and rows ........................................................................................................................ 69 Unhiding columns and rows ............................................................................................................... 70 Print Titles ............................................................................................................................................... 71 Page Breaks ............................................................................................................................................. 72 View and modify page breaks ............................................................................................................. 73 Inserting a page break ......................................................................................................................... 73 Removing a page break ....................................................................................................................... 74 Printing the worksheet ........................................................................................................................... 74 Quick print ........................................................................................................................................... 74 Print settings ....................................................................................................................................... 75 Printing a chart .................................................................................................................................... 76 Print multiple worksheets ................................................................................................................... 76
4
The guides are broken down by level of difficulty and version.
Getting Started with Excel 2007 Excel 2007 Application Window The Excel 2007 window can be quite overwhelming at first. Over the next few pages we hope to relieve any anxieties you may have, so that you are confident using the different elements of the Excel window.
The Title bar displays the name of the application running and the name of the file you have open. On the far right you will see the Minimise, Restore or Maximise, and Close buttons to control the window.
5
Other elements of the screen include the Office Button, Quick Access Toolbar and the Ribbon. We will cover these in detail over the next few pages.
Excel 2007 Office Button The Office Button replaces the File menu from previous versions of Excel. Common tools such as printing and saving are performed from here.
Recent documents can be seen on the right of the Office Button menu. This will speed up opening the spreadsheet that you often use. It is possible to pin documents that you use often to this menu by clicking the pin icon to the right of the document.
Click the pin again to unpin the workbook. Now that is cool!
Quick Access Toolbar The Quick Access Toolbar is one of only two toolbars supplied with Excel 2007. It can be customised to improve your productivity, and limit the clicking between tabs on the ribbon. By default it contains the Save, Undo and Redo icons. A good start, but you will probably find yourself adding to this list. 6
Let's add the Copy button on there. 1. Click the Customise Quick Access Toolbar arrow on the end of the toolbar. 2. A list of recommended additions appears. Copy is not in the list, so let's click More Commands and find it.
The Customise area of the Excel Options dialogue box appears. It currently shows popular commands that we may wish to add, however once again the Copy button is not in the list. 3. Click on Popular Commands and then the Home tab, as we know that the Copy button is on the Home tab of the ribbon.
7
4. Scroll down the list until you see Copy. Select Copy and click Add to add it to the bottom of the list on the right. If you wish to change the order of the Quick Access Toolbar buttons, you can do so with the two arrows to the right of the list.
5. Click Ok and you should see our new button on the Quick Access Toolbar. That will save us time when copying in future. 8
Excel 2007 Ribbon The ribbon replaces the toolbars from previous versions of Excel. It allows fast access to the majority of Excel commands via a tabular system. For those versed in the previous versions of Excel, the ribbon can be quite daunting and take some getting used to initially. Due to its tabular structure it is recommended to either customise the Quick Access Toolbar , or learn the keyboard shortcuts for tasks that you perform frequently e.g. copy and paste. Otherwise it will mean frequent trips to the Home tab.
Each tab is broken down into groups to assist with finding certain commands. Some of the groups include an arrow in the bottom right hand corner known as the Dialogue Box Launcher. As its name suggests, clicking this will open a dialogue box for the group of commands you were looking at i.e. the Font dialogue box. The ribbon can be hidden by double clicking one of the tabs. It will then function like the old Menu bar and will appear when a tab is clicked on, and hide itself when you select a command or click away. To return it to its permanent state, double click a tab again.
Excel 2007 spreadsheet The spreadsheet is divided into alphabetical columns and numbered rows. The intersection of a column and a row is a cell. A cell can be identified by its cell reference composed of the column letter followed by the row number e.g. C2. The active cell is the cell of the spreadsheet current selected. It can be identified by the black border around it, or by its cell reference in the Name Box. The column and row headers appear highlighted when a cell or range is selected to help the user identify the selected area.
9
When selecting more than one cell, this is known as a cell range. If you selected cell B2 to cell D7 it can be identified by its lilac colour and would be written B2:D7. The active cell remains white.
There are 16,384 columns in a spreadsheet ranging from A to Z and then AA to AZ, BA to BZ and so on. There are 1,048,576 rows in a spreadsheet. I don't think you will be needing extra cells anytime soon.
Excel 2007 Formula Bar The formula bar displays the contents of the active cell as it is, or was entered. The formula bar can be used to edit a cells content easily. When entering data into a cell in Excel your entry can be confirmed using either the Enter key or the green tick on the formula bar. To cancel an entry, for example a mistake in a formula, press the Esc key or the red X on the formula bar.
The formula bar also contains the Insert Function button used to guide the user through the creation of mathematical formulae.
10
The Name Box display different information depending on the current operation. It usually identifies the active cell in a spreadsheet. It can also be used to navigate the spreadsheet by entering a cell reference into the Name Box and pressing Enter.
Excel 2007 Status Bar The Status Bar displays information whilst you work. For example it will tell you if you're Caps Lock or Num Lock keys are turned on.
On the right of the Status Bar are 3 view buttons and the Zoom Slider. The Zoom Slider is a neat new tool that you can drag left or right to alter the zoom level. Alternatively you can click on the zoom level for some more standard options.
It is possible to customise the Status Bar. Click the bar with the right mouse button and a shortcut menu appears. Select or deselect the relevant options and click elsewhere on the screen to hide the shortcut menu again.
11
Live Preview and the Mini Toolbar Two new features to Excel 2007 include Live Preview and the Mini Toolbar. Live Preview occurs when you make a change to a selected item, such as changing the font of a cells contents, and Excel previews how it will look before you make your desired change. The Mini Toolbar appears when you select the contents of a cell. A transparent toolbar that may not be that obvious at first glance is displayed. Hover the mouse over the toolbar to bring it to life.
12
If you decide you do not like either of these features you can switch them off. Click the Office Button and then Excel Options. The Excel Options dialogue box is displayed.
With the Popular area selected the first two options are Show Mini Toolbar on selection and Enable Live Preview. Check or uncheck the options as necessary.
Excel 2007 keyboard shortcuts Shortcuts are the quickest way of getting tasks done in Excel. In Excel 2007 it is even more beneficial to have some worthwhile keyboard shortcuts under your belt to save time.
13
Standard
Selection and Navigation Ctrl + F – Find
Ctrl + N – New Workbook Ctrl + G – Go To Ctrl + O – Open Ctrl + H – Replace Ctrl + W – Close Window Ctrl + Arrow - End of Row or Column Alt + F4 – Quit Excel Ctrl + Home – Start of Worksheet Ctrl + S – Save Ctrl + End – End of Worksheet Ctrl + P – Print Ctrl + Page Up – Previous Worksheet Ctrl + Z – Undo Ctrl + Page Down – Next Worksheet Ctrl + Y – Redo Ctrl + Tab – Move to Next Open Workbook Ctrl + K – Hyperlink Ctrl + A - Select All Ctrl + ‘ – Copy Cell Above Ctrl + ; - Insert Today's Date Ctrl + + - Insert Row or Column
Ctrl + Shift + Arrow - Select Range to End of Row or Column Ctrl + Shift + Home – Select Range to Start of Worksheet
Shift + F11 – New Worksheet F1 – Help
Ctrl + Shift + End – Select Range to End of Worksheet
F2 – Edit Mode
Ctrl + Spacebar – Select Column
F5 – Go To
Shift + Spacebar – Select Row
F7 – Spell Check Alt + F11 – VB Editor Formatting
Moving and Copying
Ctrl + B – Bold
Ctrl + X – Cut
Ctrl + I – Italic
Ctrl + C – Copy
14
Ctrl + U – Underline
Ctrl + V – Paste
Ctrl + 1 – Format Cells
Click and Drag – Move Ctrl + Click and Drag - Copy
Creating a Workbook Creating a new workbook Excel 2007 you will be presented with a new blank workbook named Book1. The new workbook will contain 3 sheets identified by the sheet tabs in the bottom left of the worksheet. To create a new blank workbook, click the Office Button > New > Create or press Ctrl + N. It is recommended to customise the Quick Access Toolbar to include the New workbook icon to make creating blank workbooks easier in future. Every time a new blank workbook is created it is named Book1, Book2, Book3 and so on until saved under a more appropriate name. Excel 2007 also provide a variety of templates to use when creating new workbooks. To use one of these templates click the Office Button > New.
15
Click Installed Templates for a list of pre-installed templates, My templates to access any you may have created, or pick one from the categories of templates under Microsoft Office Online. There are lots to choose from which can help get you started or provide you with some useful ideas.
Navigating a worksheet There are many different ways to navigate the worksheet. Some of the best methods involve keyboard shortcuts. The most common way is to use the scroll bars. There is a horizontal and vertical scroll bar for navigating to the desired part of the worksheet. On the keyboard, you could use the cursor arrows to move amongst cells, or one of the many other keyboard shortcuts such as the Home and End keys to speed up navigation. Another method that is brilliant for large worksheets is the Name Box. Simply type the cell reference in the name box like below and press Enter.
Excel Mouse Pointers n Excel different mouse pointers perform different tasks.
Cross Pointer
Fill Handle
Arrow Pointer
Used for selecting ranges of cells Used to copy cell contents to adjacent cells Used to move cell contents
Resizing Arrows
Used to change column widths
Resizing Arrows
Used to change row heights
Cursor
Used to edit cell contents in Formula Bar 16
Entering and Editing Data To enter data into Excel, click on the cell, type in your data and press Enter. After pressing Enter the cell below the current one becomes the active cell. Other alternatives are:
Tab key – enters the data and the cell to the right of the current cell becomes the active cell Arrow keys – enters the data and the cell dependent on the direction of the arrow key pressed becomes the active cell Mouse click – enters the data and the cell clicked becomes the active cell Esc key – cancels the entry
Enter and Esc are the keyboard alternatives to the tick and cross icons found on the formula bar when entering or editing data.
Types of data Excel handles three types of data; labels, values and formulas. Labels are a term given to headings and names consisting of text and numbers. Labels are left aligned in a cell. Values are cells containing numbers and can be used in calculations. Dates are stored as values. Numbers are right aligned in a cell.
Formulas are Excel calculations written by the user and always start with =. When entering data try and keep it as simple as possible by omitting any currency symbols and commas and use slashes when entering dates. For example enter 40.60 instead of £40.60 and 20/01/2008 not 20.01.2008. The cells are then formatted to display the data they hold correctly.
17
Changing the width of a column Occasionally the cell contents may exceed the width of a column making it appear to overwrite the contents of the cell to the right.
If this happens and then data is entered into the cell on the right, the content becomes truncated.
To resize the width of the column so that you can see the full contents of the cell, point the mouse pointer over the divider of the two column labels. In the example above this would be the divider between columns B and C. When you see the resizing arrows (shown below), either click and drag the divider to the right to increase the width of the column, or double click the mouse to autofit the column width. The column will then hug the cell with the largest content in that column
Editing a cell If you select a cell that already holds data and start typing you will overwrite the existing cell content. To edit a cells content, you need to be in editing mode. To access editing mode, select the cell and press F2, double click or edit the cells content directly in the formula bar and press Enter
18
Undo and Redo If you make a mistake, do not fear, for Excel has an Undo button that will undo your last action. In fact it can undo a lot more than that. Simply click the Undo button on the Quick Access Toolbar or press Ctrl + Z and your last action is turned to dust. The drop down arrow to the right of the undo button will list your previous actions, which you can then click to undo. Naturally there is also a Redo button on the Quick Access Toolbar , or keyboard shortcut Ctrl + Y that will redo the action previously undone. This also has a drop down arrow to display a list of the actions you can redo.
Selecting Cells There are plenty of methods for selecting cells in Excel. To Select
Mouse
Keyboard
Single Cell
Click on cell
Move arrow keys to required cell
Range Of Cells
Click & drag through required cells; click 1 st cell, hold shift key, click last cell
Move to 1 st cell, hold shift key and use arrow keys to move to last cell
Row
Click on row number
Select any cell on required row; Press shift + spacebar
Several rows
Click & drag through required row numbers
Select range of cells extending through required rows; Press shift + spacebar
Column
Click column letter
Select any cell on required row; Press ctrl + spacebar
Several Columns
Click & drag through required column letters
Select range of cells extending through required columns; Press ctrl + spacebar
Entire Worksheet
Click on small rectangle at the intersection of rows & columns
Press ctrl+shift+spacebar
Multiple Selections
Hold ctrl key and click & drag required selections
Select 1 st cell range; Press shift + f8; select additional ranges; Press shift + f8
Autofill An amazing and often under utilised feature of Excel is the Autofill. Autofill is the use of the fill handle to copy data and sequences across a range of cells. Its name is often shortened to fill. To fill a range of cells: 19
1. Select the cell with the content you wish to fill 2. Point at the black square that appears in the bottom right corner of the cell, until your mouse pointer becomes the fill handle 3. Click and drag in the direction of the range you wish to fill.
Excel will copy the contents of the cell across the range that you select. You can fill a sequence by selecting the range of cells that contain the sequence, and then filling as before. This will also work with dates.
This feature becomes very powerful when used with cells containing formulas. A lot of time can be saved by copying formulas across a range of cells. This is covered in the relative and absolute cell references tutorial. ((VIDEO))
Custom Lists Custom lists are an excel feature that saves time when entering data.
20
In the example below “Monday” is entered in cell B4. Using the autofill feature explained previously to copy the cells contents, it fills the other cells with the remaining days of the week and then repeats.
This works because the days of the week are stored as a custom list. To view the custom lists click the Office Button > Excel Options and then the Edit Custom Lists button.
There are lists already setup for abbreviated days of the week and months too. It is possible to create your own custom list. Simple type the items of the list in the List entries box separating them by pressing Enter, or select a range of cells that contain the list entries and click Import. The image below shows both methods being used. 21
Saving the workbook It is good practice to save your workbook as soon as you can, and then to regularly save it to protect against data loss. To save your document click the Save icon on the Quick Access Toolbar or press Ctrl + S. If you are saving the workbook for the first time, the Save As dialogue box will appear.
Select a location to save the workbook in, and an appropriate name which will make recognising this workbook easy in the future, and click Save. The Title Bar now displays the name of the workbook. 22
From now on whenever you save the workbook, Excel will just update the workbook previously saved.
Saving in a Previous Version Excel 2007 workbooks use a different file format to previous versions of Excel. This can cause some compatibility issues when users of previous versions open Excel 2007 workbooks. It is possible to save the workbook in the 97-2003 file format so that it is more widely compatible. Click the Office Button > Save As > Excel 97-2003 Workbook
The Save As dialogue box will appear as before. Select a location to save the workbook in and an appropriate name and click Save. When saving a workbook in a previous format the Compatibility Checker will identify any potential problems.
23
In the example below it has identified the use of some conditional formatting within the workbook. It warns us that some of the conditional formatting is not supported and that another condition will not function fully.
You then have the option to either continue or to cancel your save. Once saved, the Compatibility Checker can be run at anytime. Click the Office Button, Prepare and then Run Compatibility Checker.
24
Opening an Existing Workbook If you have a workbook that you have already been working on, you can open it from Excel. You can do so in three ways;
Click Office Button > Open
Click the Open button (if it has been added to the Quick Access Toolbar) Press Ctrl + O
The Open dialogue box will appear. Navigate to the location of the saved workbook using either the Look in list at the top or the shortcuts on the left, select the workbook and click Open.
If the workbook you are looking for was used recently, it can be opened by clicking the Office Button and selecting it from the list of recent documents.
25
If the opened workbook was saved as a 97-2003 workbook, [Compatibility Mode] will appear in the Title Bar. Although using Excel 2007, compatibility mode tells you that not all the new Excel 2007 features are available for use, unless the workbook is saved under the new file format.
Closing Excel and the Workbook There are 3 ways to close a workbook:
Click the Office Button > Close Click the lower X in the top right corner of the window Or Press Ctrl + W
These methods will close the workbook and leave Excel open. There are also 3 ways to exit Excel:
Click Office Button > Exit Excel (shown below) Click the upper X in the top right corner of the window Or press Alt + F4
26
These methods will close Excel and all open workbooks. If you have any unsaved workbooks currently open, you will be prompted to save changes.
Editing a Worksheet Cut, Copy and Paste You can move or copy in Excel by cutting or copying the data and then pasting it to its new location. You can cut or copy a cell, range of cells or any other item such as a picture or a chart. Cut and Paste
Cutting and pasting moves the cell content or item. When you cut a cell it is removed from its location and placed on a clipboard (a temporary storage area). It can then be pasted to another location. 1. Select the cell(s) or item you wish to cut 2. Click the Home tab on the Ribbon and then the Cut button in the Clipboard group, or press Ctrl + X
3. A moving dashed border like running ants appears around the cut cell(s) 27
4. Select the destination cell and click the Paste button in the Clipboard group, or press Ctrl + V. If a range of cells were cut, select the cell in the top left corner of where you wish the range to be pasted Copy and Paste
Copying and pasting copies the cell content or item. When you copy a cell it remains in its location and is placed on a clipboard. It can then be pasted to another location. 1. Select the cell(s) or item you wish to copy 2. Click the Home tab on the Ribbon and then the Copy button in the Clipboard group, or press Ctrl + C 3. The running ants appears around the copied cell(s) 4. Select the destination cell and click the Paste button in the Clipboard group, or press Ctrl + V. If a range of cells were copied, select the cell in the top left corner of where you wish the range to be pasted
The clipboard can hold up to 24 items, so it is possible to collect and paste multiple items in Excel. When pasting, the Paste Options Smart tag will appear. Click this button to specify how the information is to be pasted. Options can include with source or destination formatting, whether to keep the column width and whether to maintain the value and number formatting.
Move and copy cells using the mouse
Using the mouse is faster and more streamline than using cut, copy and paste. 1. Select the cell(s) to move or copy 2. Point to the border of the cell or cell range 3. Click and drag the cell(s) to its new location. To copy the cell(s), hold down the Ctrl key whilst dragging ensuring to release the mouse button before the Ctrl key.
28
If you wish to move or copy the cell(s) to a new worksheet, hold down the Alt key to enable you to switch sheets.
The Office Clipboard The Clipboard enables the collection and pasting of multiple items across excel and other Office applications. 1. Click the Home tab of the Ribbon 2. Click the Dialogue Box Launcher arrow in the bottom right corner of the Clipboard group
3. The Clipboard Task Pane appears along the left side of the window. The Clipboard can hold up to 24 items 4. Cut and copy items as usual. You should see the items appear on the clipboard as you cut and copy them
5. Select the cell where you wish to paste the item 29
6. Click the item in the clipboard With the Clipboard open, you could collect items from other Office applications for pasting into Excel. To remove items from the Clipboard, click the items list arrow and select Delete. Click the Clear All button at the top of the Clipboard Task Pane to remove all items from the Clipboard.
Cell Comments Cell comments allow you to add notes to a worksheet for yourself and other users. They appear when you point to a cell containing a comment, and are recognised by a triangle in the corner of the cell. Inserting a comment 1. Select the cell 2. Click the Review tab on the Ribbon, and then the New Comment button from the Comments group
3. Enter the comment
30
4. Click outside the comment area. A triangle appears in the corner of the cell indicating the existence of a comment. The comment can be viewed by pointing to the cell
To permanently display the comment: 1. Select the cell 2. Click Show/Hide Comment from the Comments group on the Review tab 3. Click Show All Comments for all comments to be displayed
The Next and Previous buttons in the Comments group can be used to navigate comments within the workbook. Editing a comment 1. 2. 3. 4.
Select the cell containing the comment Click the Edit Comment button from the Comments group on the Review tab Edit the comment Click outside the comment area
Comment boxes can be resized using one of the eight sizing handles around the border of the comment. Delete a comment 1. Select the cell containing the comment to be deleted 2. Click the Delete button from the Comments group on the Review tab
Paste Special Paste Special is an extremely powerful tool allowing you to paste only certain aspects of your copied data such as the value without the underlying formula. It also provides the user with a means of consolidating data, creating links and transposing data tables.
31
1. Copy the cells as you normally would 2. Select the destination cell 3. Click the arrow below the Paste button in the Clipboard group of the Home tab
A menu providing different paste options appears
4. Select Paste Special
In the top half of the dialogue box are various aspects of the data that you may paste. For example, the value only or validation criteria only 32
5. Select the desired option and click Ok
Excel pastes the data according to your demands stripping out any irrelevant aspects Consolidation Options
In the second half of the Paste Special dialogue box are some consolidation options so that you can add/subtract/divide or multiply your copied value with the value of the cell you are pasting into Skip Blanks
Skip Blanks ensures that any blank cells in the range that you have copied are not included within the paste Transpose
Transpose is incredible. It switches your table of data from rows to columns or vice versa. Check the transpose checkbox and select any other paste options you want to transpose your data. Paste Link
Paste Link creates a link between the copied cell and the pasted cell. So if you copied cell D4 on Sheet1 and then clicked Paste Link button on cell F10 of Sheet2, the 2 cells are linked. If the content of cell D4 on Sheet1 changed, the content of cell F10 on Sheet2 is automatically updated.
Writing Formulae Using Formulas Knowing how to use formulas enable you to unleash the full power of Excel. This tutorial will take you through the fundamentals which are built upon as you progress. A formula is an instruction for Excel to operate. A formula always begins with an equals sign followed by either values or cell references and an operator. Example: =A4+A5 Excel calculates the result and displays it in the cell that the formula was entered. In the example above, if the contents of either A4 or A5 change, the formula will automatically update itself. 33
List of operators: Operator
Purpose
=
All formulas must start with an equals sign
+
Performs addition between values
-
Performs subtraction between values
*
Performs multiplication between values
/
Performs division between values
Some examples of formulas using different operators
When using different operators in one formula it is important to be aware of BODMAS. A formula can include the use of functions (built in formulas provided by Excel) and range names.
BODMAS When writing formulas it is important to have an understanding of the rule of BODMAS. Not understanding this rule could result in incorrect formulas. BODMAS stands for:
Brackets Over Division Multiplication Addition Subtraction 34
In a nutshell, that means that Excel will divide or multiply before it adds or subtracts unless specified by brackets. For example, the formula =5+3/2, we would expect the result to be 4. However Excel produces the result 6.5. Why? Because of BODMAS Excel calculated 3/2 and then that number was added to 5. To specify that we want Excel to add 5 and 3 and then divide the answer by 2 we enclose it in brackets. =(5+3)/2 As you can see if you are hoping to use add and subtract along with multiply and divide in a formula, you need to know BODMAS.
Excel Sum Function he most commonly used Excel function is Sum. The purpose of the Sum function is to add up the numbers in a range. 1. Select the cell where you wish the total to appear. 2. Click the Sum button 3. The Sum function appears in the cells and a dotted line appears around the cells Excel thinks you want to add. If the range is not correct, select a different range
4. Press Enter The total appears in the cell. If a value in the summed range changes at a later date, the total will automatically update itself. The Sum function can add more than one range if needed. To select a second range, enter a comma and select another range. 35
Functions A function is a built in formula. There are over 400 built in functions in Excel that can be accessed through the Insert Function button on the Formula Bar, or the Function Library on the Formulas tab.
After clicking the Insert Function button the Insert Function dialogue box appears. Type in the name of the function, or select the relevant category to find the function you are looking for. A description of the function is displayed when a function is selected. Click Ok.
The Function Arguments dialogue box appears. Arguments are the information that the function needs in order to do its job. The image below shows the arguments required for the IF() function. The argument in bold is mandatory, while the others are optional. When an arguments field is selected a description of what is required appears below.
36
As you become used to writing functions you may find it quicker and easier to write them directly into the cell. As you do so, Formula AutoComplete is displayed to assist you with finding the correct function and tooltips are displayed to prompt for the required arguments.
A function is made up of four parts:
Equals sign = Function name i.e. SUM Parentheses Arguments (each argument is separated by a comma)
Excel Statistical Functions
There are a number of Excel statistical formulas which can be used to analyse the data in your spreadsheet. These include, but are not limited to Count, Average, Min and Max. Count
Returns the number of values from a list. The syntax for the Count function is: 37
=COUNT(value1, [value2], ...) Average
Returns the average or arithmetic mean of a list of values. The syntax for the Average function is: =AVERAGE(number1, [number2], ...) Min
Returns the smallest number from a list of values. The syntax for the Min function is: =MIN(number1, [number2], ...) Max
Returns the largest number from a list of values. The syntax for the Max function is: =MAX(number1, [number2], ...) Entering statistical functions 1. Select the cell where you wish the result to be displayed. 2. Click the Home tab on the Ribbon, and then click the drop list arrow next to the AutoSum button in the Editing group.
You can also select the necessary function from the Function Library on the Formulas tab, or enter the function directly into the cell.
38
3. The function will appear and Excel will select a range it thinks you wish to calculate. If this is incorrect select the range you wish to use.
The example below uses the average function.
4. Press Enter. The result appears in the cell and the function can be seen in the formula bar.
These statistical functions follow the same syntax as the Sum function. It is possible to select more than one range of values to calculate. To do so, separate each cell range with a comma.
Relative and Absolute Cell References When copying cells containing formulas, any cell references will be changed in relation to its new position. For example, the formula =SUM (C4:C7) in cell C9 has been copied along to cells D9:H9.
39
As the formula was copied to the right, the cells it referred to, i.e. C4:C7, also moved right becoming D4:D7, E4:E7 and so on. This type of reference is known as a relative cell reference because the cells moved in relation to our movement.
This can sometimes be inconvenient. There are times when you require cells to be fixed. This type of cell reference is known as an absolute cell reference. Absolute cell references can be identified by $ symbols within the cell reference e.g. $B$5. In the example below cell D3 contains the formula =C3*D1. When the formula is copied down to cells D4:D7 we do not wish for cell D1 to move. To fix cell D1 we will make it absolute.
40
You can make a cell reference absolute in one of 2 ways:
Type $ symbols before the column letter and row number Select the cell reference in the formula bar and press F4
Now when the formula is copied, cell C3 moves as it is a relative cell reference, whilst cell D1 remains fixed because it is an absolute cell reference.
There are also mixed references. Mixed references are when only the column or row is fixed. For example, $B5 is a mixed reference with the column absolute and the row relative, and B$5 has the column relative and the row as an absolute reference. You can make a mixed reference by either typing the $ sign in the appropriate position or pressing the F4 key. The F4 key will toggle between the relative, absolute, mixed row and mixed column options.
Formatting a Worksheet Formatting Labels Character formatting in Excel is done using the Font group on the Home tab. 1. Select the cell(s) you wish to format 2. Click the Home tab, and click the formatting button you need from the Font group
41
Alternative ways of formatting labels include using keyboard shortcuts, the contextual menu that appears when right clicking on a cell, or the Font dialogue box. Click the Dialogue Box Launcher arrow in the bottom corner of the Font group to display the Font dialogue box.
Formatting Values Applying number formatting changes the appearance of the cell contents and not its value. This includes increasing or decreasing decimal places, formatting as a currency and formatting as a percentage.
42
Often Excel will apply the formatting for you. For example if I enter 20%, Excel will format the cell as a percentage. To format values: 1. Select the cell(s) you wish to format 2. Click the Home tab, and click the formatting button you need from the Number group
You can also format values by using the Format Cells dialogue box. Click the Dialogue Box Launcher arrow in the bottom corner of the group to display the Format Cells dialogue box, or press Ctrl + 1.
43
Cell Alignment and Orientation Cell alignment By default all cell contents are aligned to the bottom of the cell. Labels are aligned to the left of a cell and values are aligned to the right of a cell. To change the alignment of the contents of a cell: 1. Select the cell(s) to be aligned 2. Click the relevant horizontal and vertical alignment buttons from the Alignment group of the Home tab
Merge cells Cells content can be merged across multiple columns or rows making one large cell. To merge and centre cell content: 1. Select the cell(s) to be merged and centred 2. Click the Merge and Center button in the Alignment group of the Home tab
Click the Merge and Center button again to remove the formatting. It is advisable to leave a gap between cells that have been merged and other spreadsheet content, as merging cells breaks up a spreadsheets structure potentially causing problems with other Excel features. You can also merge cells without centring: 1. Select the cell(s) to merge 2. Click the Dialogue Box Launcher arrow in the bottom right corner of the Alignment group 3. Check the Merge cells box and click Ok
44
Cell orientation Cell content can be orientated to be read vertically or at an angle. This can then be combined with features mentioned earlier like merge cells and cell alignment to create effects like the below.
To orientate cell content: 1. Select the cell holding the text you wish to orient 2. Click the Orientation button in the Alignment group
45
3. Select the desired orientation option from the list
You can also orientate cell content using the Alignment tab of the Format Cells dialogue box. To orientate cell content either click and drag the red diamond to the required position or enter the intended degrees in the degreees field.
Wrap text If cell content becomes too large for the column or row, you can increase the row height or column width. An alternative is also to wrap the text. 1. Select the cell(s) 2. Click the Wrap Text button in the Alignment group
You can also wrap text whilst entering data into the cell by pressing Alt + Enter
Borders and Shading Borders and fill colours can be added to a cell or range of cells to make them more appealing and also to distinguish data such as totals. Adding a border 1. Select the cell(s) 2. Click the Borders button in the Font group of the Home tab
46
3. Select the type of border you desire from the list 4. The border is applied to the cells
To customise the border such as changing the line type or colour, select More Borders from the list. The Format Cells dialogue box opens on the Border tab.
47
Removing a border 1. Select the cell(s) with the border you wish to remove 2. Click the Borders button in the Font group of the Home tab 3. Select No Border from the list Adding a fill colour 1. Select the cell(s) you wish to add a background colour to 2. Click the Fill Color list arrow in the Font group of the Home tab
3. Select the fill colour of your choice from the list Adding a pattern or fill effect
It is also possible to add a pattern or fill effect to the background of a cell. 48
1. 2. 3. 4.
Select the cell(s) to add the pattern or fill effect to Click the Dialogue Box Launcher arrow in the bottom corner of the Font group of the Home tab Click the Fill tab Select a Pattern Color and Pattern Style, or click the Fill Effects button for options on setting gradients to colours
5. Click Ok and the pattern or fill effect is applied to the cell(s)
Cell Styles
Cell Styles contain preset formatting that can be applied to a cell or range of cells on the click of a button. This preset formatting can include the font, cell shading, alignment and more. Excel comes with several in-built cell styles for you to choose from. It is also possible to create your own or modify the existing styles. Applying a cell style 1. Select the cell(s) to format 2. Click the Cell Styles button in the Styles group of the Home tab
49
3. Select the cell style of your choice
As you hover over the styles, Live Preview will allow you to see how the cell(s) look before making your selection Removing a cell style 1. Select the cell(s) with the style you wish to remove 2. Click the Cell Styles button in the Styles group of the Home tab 3. Select Normal from the list of styles Modify a cell style 1. Click the Cell Styles button in the Styles group of the Home tab 2. Right click on the cell style you wish to modify and click Modify
3. Select the formatting options you want to use for the style
50
4. Click Format and change the formatting items as desired and click Ok Creating a new style 1. Click the Cell Styles button in the Styles group of the Home tab 2. Click New Cell Style 3. Enter a name for the new style and select the formatting options you want to use
51
4. Click Format and change the formatting items as desired and click Ok
Cell style can be deleted by right clicking on the cell style from the list and clicking Delete.
Format Painter Format Painter is a hidden gem inside all Office applications. It allows you to copy the formatting of one cell to another cell or range of cells. This can ensure consistency of formatting and save a lot of time. 1. Select the cell with the formatting you wish to copy 2. Click the Home tab and then click the Format Painter button from the Clipboard group
3. Select the cell(s) you wish to copy the formatting to
4. The formatting is applied and the Format Painter is turned off. To apply the formatting to more than one cell or range of cells: 1. 2. 3. 4.
Select the cell with the formatting you wish to copy Double click the Format Painter button Select the cell(s) you wish to copy the formatting to The Format Painter remains on after applying the formatting, so you may continue to apply the formatting to other cells 5. Click the Format Painter button, or press Esc to turn it off (((VIDEO))
Clear Cells When you select a cell in Excel and press the delete key it only removes the contents of the cell. Any comments or formatting applied remain. To clear a cell(s): 1. Select the cell(s) 2. Click the Home tab on the Ribbon 3. Click the Clear button in the Editing group 52
4. Select the desired option from the list
Managing Worksheets Inserting and Deleting Rows, Columns and Cells At times you will need to insert additional rows or columns into the worksheet, but you cannot exceed the maximum 1,048,576 rows and 16,384 columns per worksheet (mighty impressive, if you do). Inserting rows on a worksheet
To insert new a new row: 1. Select the row, or a cell in the row below where you want the inserted row to appear. For example, if you wanted to insert a row between rows 7 and 8, select row 8. 2. Click Insert > Insert Sheet Rows from the Cells group of the Home tab.
To insert more than one row: 1. Select the number of rows you would like to insert below where you want the inserted row to appear. For example, if you wanted to insert 3 rows between rows 7 and 8, select rows 8, 9 and 10. 2. Click Insert > Insert Sheet Rows from the Cells group of the Home tab. Inserting columns on a worksheet
To insert a new column: 53
1. Select the column, or a cell in the column to the right of where you want the inserted column to appear. For example, if you wanted to insert a column between columns C and D, select column D. 2. Click Insert > Insert Sheet Columns from the Cells group of the Home tab.
To insert more than one column: 1. Select the number of columns you would like to insert to the right of where you want the inserted columns to appear. For example, if you want to insert 2 columns between columns C and D, select columns D and E. 2. Click Insert > Insert Sheet Columns from the Cells group of the Home tab. Inserting cells on a worksheet
To insert cells onto a worksheet: 1. Select the cell, or the range of cells where you want to insert the new cells. Select the same number of cells as you would like to insert. 2. Click Insert > Insert Cells from the Cells group of the Home tab. 3. In the dialogue box that appears select the direction in which to shift the surrounding cells.
When you insert rows, columns or cells, any references to cells that are affected in the worksheet are adjusted accordingly. Deleting rows, columns and cells
To delete rows or columns: 1. Select the rows or columns to be deleted. 2. Click Delete > Delete Sheet Rows or Delete Sheet Columns from the Cells group of the Home tab. 3. The rows or columns are deleted and all other rows and columns are shifted up and to the left.
54
To delete cells on a worksheet: 1. Select the cell or cells to be deleted. 2. Click Delete > Delete Cells from the Cells group of the Home tab. 3. In the dialogue box that appears select the direction in which to shift the surrounding cells.
Pressing the delete key only removes the contents from the cells and will not delete the rows, columns or cells. When you delete rows, columns or cells, any references to cells that are affected in the worksheet are adjusted accordingly, unless it refers to a cell that has been deleted.
Insert and Delete Worksheets When you create a new workbook, by default you are provided with 3 worksheets. You can insert additional worksheets, or delete worksheets as necessary. Inserting a worksheet
To insert a new worksheet:
Select the worksheet after where you would like the new worksheet to appear. The new worksheet appears to the left of the currently selected worksheet. Click Insert > Insert Sheet from the Cells group of the Home tab.
55
The new worksheet is named according to the number of worksheets you currently have. For example if you have 3 sheets, then the new worksheet is named Book4.
If you wish to insert an additional worksheet at the end of the existing worksheets, click the Insert Worksheet icon or press Shift + F11.
Deleting a worksheet
To delete a worksheet:
Select the worksheet that you wish to delete. Click Delete > Delete Sheet from the Cells Group of the Home tab
The delete key will only delete the contents of a cell and will not delete worksheets.
Customizing Sheet Tabs The worksheet tabs that allow us to navigate the worksheets of our workbook can be customised for a more rewarding Excel experience. This includes moving, copying, renaming and even changing the sheet tab colour. Moving and copying sheets
To move a sheet , simply click and drag the tab to the desired location. Whilst in the process of dragging the tab, a black arrow appears to indicate its position when you release the mouse button.
56
Copying a sheet will allow you to use an existing worksheet as a template and can be a great time saver. To copy a sheet, simply click and drag the tab to the desired location whilst holding the Ctrl key down. Whilst dragging you should notice the + sign on the sheet icon indicating that it is duplicating the worksheet.
Renaming a sheet tab
More meaningful names can be assigned to sheet tabs to replace the existing Sheet1, Sheet2 and so on. To rename a worksheet, double click on the sheet tab. The name will highlight in black ready for you to enter a new name.
Type a more meaningful name for the sheet and press Enter. Format the sheet tab colour
It is even possible to change the tab colour. This may be done to make the sheet easier to identify. Right click on the sheet tab and a shortcut menu appears. Select Tab Color and then choose a colour from the list displayed.
57
Splitting and Freezing Panes Splitting or freezing panes allow you to hold sections of a worksheet in place so they are visible at all times whilst scrolling through the worksheet. This is especially useful for large worksheets because you can hold the column and row headings in place whilst you scroll through your data. Splitting panes on a worksheet
Splitting panes allows you to see multiple areas of a worksheet at once. So you can make changes to the data in cell D500 whilst viewing the data in cell D5. 1. Select the cell where you want to split the worksheet
The worksheet will be split above and to the left of the active cell creating four panes. 2. Click the View tab on the Ribbon 3. Click the Split button in the Window group
The worksheet is split into sections that can be navigated individually without moving the other sections.
58
Click and drag the panes to adjust the location of the split.
Click the Split button in the Window group on the Ribbon again to remove the split. Worksheets can also be split using the split buttons at the top and to the right of the worksheets scroll bars. To split the worksheet, drag the relevant split button onto the area of the worksheet where you wish to create the split.
59
Freezing panes on a worksheet Freezing panes is similar to splitting panes except that the panes are immobilised. Freeze panes is used to hold headers in place so that they can always been seen when scrolling through the worksheet. 1. Click the View tab on the Ribbon 2. Click the Freeze Panes button in the Window group
A list appears with three options
Freeze Panes: Freezes the worksheet above and to the left of the active cell.
Ensure that you select the required cell before clicking this option. Unlike split panes, frozen panes cannot be moved. You need to unfreeze the panes and then freeze again.
Freeze Top Row: Freezes the top row, keeping it visible whilst you scroll through the rest of the worksheet. Freeze First Column: Freezes the first column, keeping it visible whilst you scroll through the rest of the worksheet 60
3. Select the desired option from the list
The relevant panes are frozen and the worksheet can be navigated as required To unfreeze panes, click the Freeze Panes button in the Window group and select Unfreeze Panes ((VIDEOS))
Printing a Worksheet Print Preview
Generally the worksheet will appear on paper as it does on screen. However this is not always the case and worksheets can become very large. There are additional commands to affect the printout of a worksheet and adjust it to your needs. Print Preview allows you to check the appearance of your printout on screen before actually printing. To view the Print Preview: 1. Click the Office Button 2. Select Print and then click Print Preview
61
It's a good idea to add the Print Preview button to the Quick Access Toolbar to make viewing Print Preview just the click of a button in future. The example below shows the Print Preview of a large worksheet spanning 3 pages. The number of pages is displayed on the Status Bar at the bottom of the window.
Click the Next Page button in the Preview group of the Ribbon to advance to the other pages. Click Previous Page to go back. The printout is 3 pages wide and 1 page tall. Click Close to return to the worksheet.
Add a Header and Footer Data can be added to the top (header) or bottom (footer) of a page in the margin area to appear when printed. Any text or picture such as a logo can be added. Fields, information that updates automatically, such as the date or page number can also be added. 1. Click the Header and Footer button in the Text group of the Insert tab
The workbook switches to Page Layout View and the cursor appears in the header area, ready to add or edit some text. The Header and Footer Tools contextual tabs appear on the Ribbon providing commands for working with headers and footers.
62
To work with the footer, click the Go To Footer button in the Navigation group of the Design tab. 2. Enter the header or footer text and click outside of the area.
The header or footer is centre aligned by default. Select the areas to the left or right to apply the appropriate orientation to the header or footer. Insert header and footer elements
Individual elements such as page numbers, pictures and the date can be added to a header or footer 1. Click the Header and Footer button in the Text group of the Insert tab
The Header and Footer Tools contextual tabs appear. 2. Click the button in the Header and Footer Elements group of the Design tab for the element you want to insert. Use auto headers and footers
You can insert built-in header and footer text as an alternative to writing the text or inserting the appropriate field yourself. 1. Click the Header and Footer button in the Text group of the Insert tab 2. Click either the Header or Footer button in the Header and Footer group on the Design tab
A list of different types of page numbers, dates, titles and file paths appears. 3. Select the auto header of footer you want to use
The text is automatically inserted into the header or footer. Any text entered previously is replaced. 63
Adjusting Sheet Margins and Orientation Margins are the empty space between the worksheet data and the top, bottom, left and right edges of the page. These can be adjusted as necessary to create a larger or smaller gap between the worksheet data and the edges of the page. There are two different types of orientation of the page. Portrait (tall and thin), the default page orientation and landscape (short and wide), which is useful for spreadsheets with many columns.
Adjusting the margins
Margins in Excel 2007 are 1.9 cm (0.75 inches) from the top and bottom, and 1.8 cm (0.7 inches) to the left and right by default. 1. Click the Page Layout tab on the Ribbon 2. Click the Margins button from the Page Setup group
64
3. Select the desired page margin settings from the list or click Custom Margins and set the measurements for each margin by adjusting the necessary list box
Changing the page orientation
You can change the page orientation between portrait and landscape. 1. Click the Page Layout tab on the Ribbon 65
2. Click the Orientation button from the Page Setup group
3. Select the desired page orientation
Adjusting size and scale Excel worksheets can become very large. It is possible to select a different paper size in Excel to accommodate your spreadsheet. You can also adjust the scale of the printed worksheet so that it stretches or shrinks to your specifications. Adjusting the paper size 1. Click the Page Layout tab on the Ribbon 2. Click the Size button in the Page Setup group
66
3. Select the paper size you want to use from the list
The layout of the worksheet will adjust to the new page size settings. Scaling to fit
You can specify how many pages wide and tall you want the data to fit to when printed, or enter a percentage for the data to scale to. 1. Click the Page Layout tab on the Ribbon
There are 3 options to choose from in the Scale to Fit group
Width: Select the maximum width in number of pages for your printout Height: Select the maximum height in number of pages for your printout 67
Scale: Use the arrow buttons or enter a percentage to stretch or shrink the printout to a percentage of its actual size
2. Adjust the scale as required using the relevant scaling command
The worksheet is scaled to fit the new settings. It is recommended to check Print Preview after making such changes to see the printout on screen prior to printing. Scaling to fit from Print Preview
You can also adjust the scale of a printed worksheet from the Print Preview screen. 1. From the Print Preview screen click the Page Setup button from the Print group on the Ribbon 2. The Page Setup dialogue box opens
In the Scaling panel of the Page tab are the 3 scaling options. Adjust as necessary
In the example above, scale to fit options had already been set. By opening Page Setup I can see what percentage Excel had scaled the worksheet to after setting it to 1 page wide and 2 pages tall. 3. Click Ok
The worksheet is scaled to fit the new settings.
68
Set the Print Area The entire contents of a worksheet are printed by default, but it is possible to specify an area of a worksheet to print. To set the print area: 1. Select the range of the worksheet you would like to print 2. Click the Print Area button from the Page Setup group on the Page Layout tab
3. Select Set Print Area from the list Clear the print area
To clear the print area 1. Click the Print Area button from the Page Setup group on the Page Layout tab 2. Select Clear Print Area from the list
Hiding columns and rows It is possible to hide columns and rows on a spreadsheet to protect confidential information. This feature can also be helpful when printing, to fit large worksheets on fewer pages or hide unnecessary information. In the example below the companies contact details are to be hidden, so we will hide columns C to E.
1. Select columns C to E. Ensure you have the column select mouse cursor while selecting 2. From the Home tab, click the Format button in the Cells group 3. Select Hide & Unhide and click Hide Columns
69
The columns are hidden. This can be recognized by the missing column headers for columns C to E.
Although they do not appear when users open the workbook, they can be unhidden. If you want to ensure that columns and rows cannot be unhidden, you will need to protect the worksheet. Unhiding columns and rows
To unhide the columns: 1. Select the columns either side of the hidden columns. In this example that is columns B and F 2. From the Home tab, click the Format button in the Cells group 3. Select Hide ט Unhide and click Unhide Columns 70
The columns are visible again. To unhide rows, follow the steps above selecting the rows either side of the hidden row(s). If the option to unhide columns or rows is ghosted, then the worksheet is protected.
Print Titles Print Titles enables you to specify rows and columns to repeat on each printed page. This is especially useful for large documents spanning multiple pages. Print Titles can be used to repeat the headings at the top or left of each page. The reader can then easily identify what the data is referring to without looking at the first page for the headings. 1. Click the Page Layout tab on the Ribbon 2. Click the Print Titles button in the Page Setup group
71
The Page Setup dialogue box appears with the Sheet tab displayed. The Print titles panel provides 2 options. Rows to repeat at top or Columns to repeat at left. 3. Click the Rows to repeat at top or Columns to repeat at left cell reference button, and select the range of labels that you want to repeat on each page The $1:$1 shown in the example below means the labels in row 1 will be repeated on every printed page.
4. Click Ok Check Print Preview to see the selected range repeated on every page.
Page Breaks Page breaks can be used to separate pages for printing. They are inserted automatically when printing, dependent upon the worksheets page setup properties. After printing or print previewing a worksheet they are shown as dashed lines on the page. 72
Page breaks can be adjusted on the worksheet to better position them. Page breaks can also be inserted and removed. View and modify page breaks 1. Click the Page Break Preview button in the Workbook Views group of the View tab
The worksheet is displayed showing the print area with each page labeled. Dashed blue lines indicate automatic page breaks, and solid blue lines indicate manually created page breaks.
2. Point to a page break line
The mouse pointer changes to a double headed arrow. 3. Click and drag the page break line to a new position 4. Click the Normal button in the Workbook Views group of the View tab to end page break preview
Any automatic page breaks that were adjusted are converted to manually created page breaks. Moving page breaks to the right or down will automatically scale the print smaller to fit onto the page Inserting a page break
You need to be in Page Break Preview in order to insert page breaks. 1. Right click on the cell at the start of the column or row where the page break is to be inserted
73
A contextual menu and the Mini Toolbar will be displayed. 2. Select Insert Page Break from the contextual menu
The page break will be inserted above or to the left of the selected cell. If you select a cell in the middle of the worksheet a horizontal and vertical page break will be inserted. Removing a page break 1. Right click on the cell directly below a horizontal page break, or to the right of a vertical page break 2. Select Remove Page Break from the contextual menu that appears
Page breaks can also be removed by dragging the page break line off the page.
Printing the worksheet There are various print options available when printing including selected areas of a page, printing a chart, and setting the number of copies to print. Quick print
You can print all the worksheets of the workbook using its default settings by clicking the Quick Print button. This button is not displayed on the Quick Access Toolbar by default and needs to be added. To add the Quick Print button, click the Customise Quick Access Toolbar arrow and select Quick Print.
74
Print settings
The Print settings are changed in the Print dialogue box. To open the Print dialogue box, click the Office Button and then click Print or press Ctrl + P.
The default settings are to print one copy of every page for the current worksheet. If necessary you can: 1. Select a different printer from the Name: list box 2. Specify the pages that you wish to print by using the From: and To: spin boxes in the print range panel 3. From the Print what panel click Selection to only print the selected range on the worksheet, or Entire workbook to print every worksheet in the file 75
4. Set the number of copies from the Copies panel
After adjusting the settings as necessary, click Ok to print. Printing a chart 1. Select the chart on the worksheet 2. Click the Office Button and then click Print or press Ctrl + P 3. Ensure Selected Chart is selected in the Print what panel of the Print dialogue box
Adjust any settings as necessary 4. Click Ok Print multiple worksheets 1. Select the worksheets you want to print by clicking on each sheet tab whilst holding down the Ctrl key on the keyboard 2. Click the Office Button and then click Print or press Ctrl + P 3. Active sheet(s) should be selected in the Print what panel
Adjust any settings as necessary 4. Click Ok
76
