WELCOME TO EXCEL
This handout provides step by step, easy to follow instructions on how to use Excel 2010 or Excel 2007. It doesn't matter if you are new to Excel and want to start from scratch or just want to use this Excel tutorial as a reference. It is kept simple!
To open the Excel work book
Go to the start button, click and select Ms Excel the work book will be displayed.
To save in MS Excel
Go to the office button at the top left of you page, click on save as a dialogue box will be displayed
Type in the desired file name and click on the save button your file will be saved.
Active Cell
The active cell is recognized by its black outline. Data is always entered into the active cell. Different cells can be made active by clicking on them with the mouse or by using the arrow keys on the keyboard.
File Tab
The File tab is new to Excel 2010 - Sort of. It is a replacement for the Office Button in Excel 2007 which was a replacement for the file menu in earlier versions of Excel.
Like the old file menu, the File tab options are mostly related to file management such as opening new or existing worksheet files, saving, printing, and a new feature - saving and sending Excel files in PDF format.
Formula Bar
Located above the worksheet, this area displays the contents of the active cell. It can also be used for entering or editing data and formulas.
Name Box
Located next to the formula bar, the Name Box displays the cell reference or the name of the active cell.
Column Letters
Columns run vertically on a worksheet and each one is identified by a letter in the column header.
Row Numbers
Rows run horizontally in a worksheet and are identified by a number in the row header.
Together a column letter and a row number create a cell reference. Each cell in the worksheet can be identified by this combination of letters and numbers such as A1, F456, or AA34.
Sheet Tabs
By default there are three worksheets in an Excel file.
The tab at the bottom of a worksheet tells you the name of the worksheet - such as Sheet1, Sheet2 etc.
Switching between worksheets can be done by clicking on the tab of the sheet you wish to access.
Renaming a worksheet or changing the tab color can make it easier to keep track of data in large spreadsheet files.
Worksheets
A worksheet is a collection of cells where you keep and manipulate the data. By default, each Excel workbook contains three worksheets.
Select a Worksheet
When you open Excel, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.
To select one of the other two worksheets, simply click on the sheet tab of Sheet2 or Sheet3.
Rename a Worksheet
By default, the worksheets are named Sheet1, Sheet2 and Sheet3. To give a worksheet a more specific name, execute the following steps.
Right click on the sheet tab of Sheet1.
Choose Rename.
3. For example, type Sales 2010.
Insert a Worksheet
You can insert as many worksheets as you want. To quickly insert a new worksheet, click the Insert Worksheet tab at the bottom of the document window.
Move a Worksheet
To move a worksheet, click on the sheet tab of the worksheet you want to move and drag it into the new position.
1. For example, click on the sheet tab of Sheet4 and drag it before Sheet2.
Delete a Worksheet
To delete a worksheet, right click on a sheet tab and choose Delete.
1. For example, delete Sheet4, Sheet2 and Sheet3.
Copy a Worksheet
Imagine, you have got the sales for 2010 ready and want to create the exact same sheet for 2011, but with different data. You can recreate the worksheet, but this is time-consuming. It's a lot easier to copy the entire worksheet and only change the numbers.
1. Right click on the sheet tab of Sales 2010.
2. Choose Move or Copy...
The 'Move or Copy' dialog box appears.
3. Select (move to end) and check Create a copy.
4. Click OK.
Zoom
In most cases, you can use the minus and plus symbols in the status bar to quickly zoom the document. Use the buttons on the View tab to zoom to a specific percentage and to zoom to a selection.
1. To quickly zoom the document, use the minus and plus symbols in the status bar
To zoom to a specific percentage, execute the following steps.
2. On the View tab, click Zoom.
3. Enter a number (between 10 and 400) and click OK.
To zoom to a selection, execute the following steps.
4. First, select a range of cells.
5. On the view tab, click Zoom to Selection.
Split
Split your worksheet to view multiple distant parts of your worksheet at once. To split your worksheet (window) into a upper and lower part (pane), execute the following steps.
1. Click the split box above the vertical scroll bar.
2. Drag it down to split your window.
3. Notice the two vertical scroll bars. For example, use the lower vertical scroll bar to move to row 49. As you can see, the first 6 rows remain visible.
4. To remove the split, double click the horizontal split bar that divides the panes
Freeze Panes
If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.
Freeze Top Row
To freeze the top row, execute the following steps.
1. On the View tab, click Freeze Panes, Freeze Top Row.
2. Scroll down to the rest of the worksheet.
Result. Excel automatically adds a black horizontal line to indicate that the top row is frozen.
Note: to keep the first column visible while scrolling through the right of the worksheet, click Freeze First Column.
Unfreeze Panes
To unlock all rows and columns, execute the following steps.
On the View tab, click Freeze Panes, Unfreeze Panes.
Freeze Panes
To freeze panes, execute the following steps.
1. Select row 3.
2. On the View tab, click Freeze Panes, Freeze Panes.
3. Scroll down to the rest of the worksheet.
4. Select cell C3 (unfreeze panes first).
5. On the View tab, click Freeze Panes, Freeze Panes.
Group Worksheets
You can group worksheets if you want to edit multiple worksheets at the same time. Our workbook contains 3 similar worksheets (North, Mid and South) and a blank fourth worksheet.
To group worksheets, hold down CTRL and click the sheet tabs of the sheets you want to group.
Release CTRL.
Now you can edit multiple worksheets at the same time.
For example, on the North sheet, change the value of cell B2 to $1000 and delete row 4.
Go to the other two worksheets and you'll see that these worksheets have been edited as well.
To ungroup, right click one of the sheet tabs and click Ungroup Sheets or click any sheet tab outside the group. For example, the sheet tab of Sheet4.
View Multiple Worksheets
If you want to view multiple Excel worksheets at the same time, execute the following steps.
1. Open a workbook.
2. On the View tab, click New Window.
Excel opens a new window containing another view of the document.
3. On the View tab, click Arrange All.
4. Select the desired arrange setting. For example, click Vertical.
5. Click OK.
The titles (view-multiple-worksheets:2 and view-multiple-worksheets:1) indicate that two windows of the same file are open.
SHORT CUT KEYS
Home key: moves the active cell highlight to column A without changing rows.
Ctrl + Home keys: moves the active cell highlight to cell A1.
Ctrl + End keys: moves the active cell highlight to the last cell of the spreadsheet containing data.
Ctrl + Down Arrow keys: moves the active cell highlight to the last row of the spreadsheet without changing columns.
Ctrl + Up Arrow keys: moves the active cell highlight to the first row of the spreadsheet without changing columns.
Ctrl + Page Down keys: moves the active cell highlight to the next sheet of the spreadsheet.
Ctrl + Page Up keys: moves the active cell highlight to the previous sheet of the spreadsheet.
Arrow keys Move one cell in the direction of the arrow
Page Up Page Up
Page down Page down
Tab Move one cell to the right
Alt tab Move one cell to the left
F1 Help
F2 Edit mode to edit a cell’s contents
F3 ???
F4 ???
F5 Go to
F6 Next pane
F7 Spell check
F8 ???
F9 Calculate all
F10 Display key tips
F11 Insert a chart
F12 Save as
F14 Comments
F15 Function arguments
F16 Move to the next available cell in the table
F17 Find and replace
F18 Key tips
F19 Research pane (search, thesaurus etc)
F20 ???
F21 Recalculate????
F22 Right click menu
F23 New worksheet
F24 Save as
Home Start of row
Alt ‘ (apostrophe) Style menu
Alt = Autosum
Alt F Backstage view
Alt H Display shortcuts
Alt N Insert Menu
Alt P Page Layout menu
Cell Reference
=( Start a formula
To create a formula that uses data in certain cells, you’ll need to refer to those cells. In Excel, cell references are given by supplying the column letter and row number. For example, cell C3 exists at the intersection of column C and row 3. Read more about cell reference in Excel.
A Simple Formula – AutoSum
B is called the Formula Bar and it displays the content of the active cell, which in this case is a formula.
C is the active cell and contains the formula.
You can see that the formula starts with a “=”. All formulas must start with “=”. The next part is SUM, which you can guess represents the SUM function. The contents of the brackets are called arguments and they define the range of cells that we are summing – in this case cells B2 to B7. This range is defined as B2:B7. Autosum is a quick and easy formula to use that automatically identifies its input variables.
Cell Basics
Introduction to Cells and Cell Content
The Cell
Each rectangle in a worksheet is called a cell. A cell is the intersection of a row and a column.
Each cell has a name, or a cell address based on which column and row it intersects. The cell address of a selected cell appears in the Name box.
To Select a Cell:
Click on a cell to select it. When a cell is selected you will notice that the borders of the cell appear bold and the column heading and row heading of the cell are highlighted.
Release your mouse. The cell will stay selected until you click on another cell in the worksheet.
You can also navigate through your worksheet and select a cell by using the arrow keys on your keyboard.
To Select Multiple Cells:
Click and drag your mouse until all of the adjoining cells you want are highlighted.
Release your mouse. The cells will stay selected until you click on another cell in the worksheet.
Protect
Encrypt an Excel file with a password so that it requires a password to open it.
1. Open a workbook.
2. On the green File tab, click Save As.
3. Click on the Tools button and click General Options.
4. In the Password to open box, enter a password and click OK.
5. Reenter the password and click OK.
Note: this feature also encrypts your Excel file. If you lose or forget the password, it cannot be recovered.
6. Enter a file name and click Save.
It requires a password to open this Excel file now. The password for the downloadable Excel file is "easy".
PRINT
Print a Worksheet
To print a worksheet in Excel 2010, execute the following steps.
1. On the File tab, click Print.
2. To preview the other pages that will be printed, click 'Next Page' or 'Previous Page' at the bottom of the window.
3. To print the worksheet, click the big Print button.
What to Print
Instead of printing the entire worksheet, you can also only print the current selection.
1. First, select the range of cells you want to print.
2. Next, under Settings, select Print Selection.
3. To print the selection, click the big Print button.
Note: you can also print the active sheets (first select the sheets by holding down CTRL and clicking the sheet tabs) or print the entire workbook. Use the boxes next to Pages (see first screenshot) to only print a few pages of your document. For example, 2 to 2 only prints the second page.
Multiple Copies
To print multiple copies, execute the following steps.
1. Use the arrows next to the Copies box.
2. If one copy contains multiple pages, you can switch between Collated and Uncollated. For example, if you print 6 copies, Collated prints the entire first copy, then the entire second copy, etc. Uncollated prints 6 copies of page 1, 6 copies of page 2, etc.
Orientation
You can switch between Portrait Orientation (more rows but fewer columns) and Landscape Orientation (more columns but fewer rows).
Page Margins
To adjust the page margins, execute the following steps.
1. Select one of the predefined margins (Normal, Wide or Narrow) from the Margins drop-down list.
2. Or click the 'Show Margins' icon at the bottom right of the window. Now you can drag the lines to manually change the page margins.
This example teaches you how to add information to the header (top of each printed page) or footer (bottom of each printed page) in Excel.
1. On the View tab, click Page Layout, to switch to Page Layout view.
2. Click to add header.
The Header & Tools contextual tab activates.
3. On the Design tab, click Current Date to add the current date (or add the current time, file name, sheet name, etc).
Note: Excel uses codes in order to automatically update the header or footer as you change the workbook.
4. You can also add information to the left and right part of the header. For example, click the left part to add the name of your company.
5. Click somewhere else on the sheet to see the header.
Note: on the Design tab, click Options for a different first page header/footer or a different header/footer for odd and even pages. In a similar way, you can add information to the footer.
6. On the View tab, click Normal, to switch back to Normal view.
Count and Sum Functions
The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.
Count
To count the number of cells that contain numbers, use the COUNT function.
Countif
To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.
Countifs
To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.
Sum
To sum a range of cells, use the SUM function.
Sumif
To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).
To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).
Sumifs
To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).
General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average cells based on one or multiple criteria.
Logical Functions
Learn how to use Excel's logical functions such as the IF, AND and OR function.
If Function
The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE.
1. Select cell C2 and enter the following function.
The IF function returns Correct because the value in cell A1 is higher than 10.
And Function
The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.
1. Select cell D2 and enter the following formula.
The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.
Or Function
The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.
1. Select cell E2 and enter the following formula.
The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct.
General note: the AND and OR function can check up to 255 conditions.
Cell References
Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.
Relative Reference
By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative.
1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.
Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and cell C5. In other words: each cell references its two neighbors on the left.
Sort
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.
One Column
To sort on one column, execute the following steps.
1. Click any cell in the column you want to sort.
2. To sort in ascending order, on the Data tab, click AZ.
Multiple Columns
To sort on multiple columns, execute the following steps.
1. On the Data tab, click Sort.
2. Select Last Name from the 'Sort by' drop-down list.
3. Click on Add Level.
4. Select Sales from the 'Then by' drop-down list.
5. Click OK.
Tables
Tables (or lists in Excel 2003) allow you to analyze your data quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table.
Insert a Table
To insert a table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, click Table.
3. Excel automatically selects the data for you. Check 'My table has headers' and click on OK.
WELCOME TO EXCEL
To open the Excel work book
Go to the start button, click and select Ms Excel the work book will be displayed.
To save in MS Excel
Go to the office button at the top left of you page, click on save as a dialogue box will be displayed
Type in the desired file name and click on the save button your file will be saved.
Active Cell
The active cell is recognized by its black outline. Data is always entered into the active cell. Different cells can be made active by clicking on them with the mouse or by using the arrow keys on the keyboard.
File Tab
The File tab is new to Excel 2010 - Sort of. It is a replacement for the Office Button in Excel 2007 which was a replacement for the file menu in earlier versions of Excel.
Like the old file menu, the File tab options are mostly related to file management such as opening new or existing worksheet files, saving, printing, and a new feature - saving and sending Excel files in PDF format.
Formula Bar
Located above the worksheet, this area displays the contents of the active cell. It can also be used for entering or editing data and formulas.
Name Box
Located next to the formula bar, the Name Box displays the cell reference or the name of the active cell.
Column Letters
Columns run vertically on a worksheet and each one is identified by a letter in the column header.
Row Numbers
Rows run horizontally in a worksheet and are identified by a number in the row header.
Together a column letter and a row number create a cell reference. Each cell in the worksheet can be identified by this combination of letters and numbers such as A1, F456, or AA34.
Sheet Tabs
By default there are three worksheets in an Excel file.
The tab at the bottom of a worksheet tells you the name of the worksheet - such as Sheet1, Sheet2 etc.
Switching between worksheets can be done by clicking on the tab of the sheet you wish to access.
Renaming a worksheet or changing the tab color can make it easier to keep track of data in large spreadsheet files.
Worksheets
A worksheet is a collection of cells where you keep and manipulate the data. By default, each Excel workbook contains three worksheets.
Select a Worksheet
When you open Excel, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.
To select one of the other two worksheets, simply click on the sheet tab of Sheet2 or Sheet3.
Rename a Worksheet
By default, the worksheets are named Sheet1, Sheet2 and Sheet3. To give a worksheet a more specific name, execute the following steps.
Right click on the sheet tab of Sheet1.
Choose Rename.
3. For example, type Sales 2010.
Insert a Worksheet
You can insert as many worksheets as you want. To quickly insert a new worksheet, click the Insert Worksheet tab at the bottom of the document window.
Move a Worksheet
To move a worksheet, click on the sheet tab of the worksheet you want to move and drag it into the new position.
1. For example, click on the sheet tab of Sheet4 and drag it before Sheet2.
Delete a Worksheet
To delete a worksheet, right click on a sheet tab and choose Delete.
1. For example, delete Sheet4, Sheet2 and Sheet3.
Copy a Worksheet
Imagine, you have got the sales for 2010 ready and want to create the exact same sheet for 2011, but with different data. You can recreate the worksheet, but this is time-consuming. It's a lot easier to copy the entire worksheet and only change the numbers.
1. Right click on the sheet tab of Sales 2010.
2. Choose Move or Copy...
The 'Move or Copy' dialog box appears.
3. Select (move to end) and check Create a copy.
4. Click OK.
Zoom
In most cases, you can use the minus and plus symbols in the status bar to quickly zoom the document. Use the buttons on the View tab to zoom to a specific percentage and to zoom to a selection.
1. To quickly zoom the document, use the minus and plus symbols in the status bar
To zoom to a specific percentage, execute the following steps.
2. On the View tab, click Zoom.
3. Enter a number (between 10 and 400) and click OK.
To zoom to a selection, execute the following steps.
4. First, select a range of cells.
5. On the view tab, click Zoom to Selection.
Split
Split your worksheet to view multiple distant parts of your worksheet at once. To split your worksheet (window) into a upper and lower part (pane), execute the following steps.
1. Click the split box above the vertical scroll bar.
2. Drag it down to split your window.
3. Notice the two vertical scroll bars. For example, use the lower vertical scroll bar to move to row 49. As you can see, the first 6 rows remain visible.
4. To remove the split, double click the horizontal split bar that divides the panes
Freeze Panes
If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.
Freeze Top Row
To freeze the top row, execute the following steps.
1. On the View tab, click Freeze Panes, Freeze Top Row.
2. Scroll down to the rest of the worksheet.
Result. Excel automatically adds a black horizontal line to indicate that the top row is frozen.
Note: to keep the first column visible while scrolling through the right of the worksheet, click Freeze First Column.
Unfreeze Panes
To unlock all rows and columns, execute the following steps.
On the View tab, click Freeze Panes, Unfreeze Panes.
Freeze Panes
To freeze panes, execute the following steps.
1. Select row 3.
2. On the View tab, click Freeze Panes, Freeze Panes.
3. Scroll down to the rest of the worksheet.
4. Select cell C3 (unfreeze panes first).
5. On the View tab, click Freeze Panes, Freeze Panes.
Group Worksheets
You can group worksheets if you want to edit multiple worksheets at the same time. Our workbook contains 3 similar worksheets (North, Mid and South) and a blank fourth worksheet.
To group worksheets, hold down CTRL and click the sheet tabs of the sheets you want to group.
Release CTRL.
Now you can edit multiple worksheets at the same time.
For example, on the North sheet, change the value of cell B2 to $1000 and delete row 4.
Go to the other two worksheets and you'll see that these worksheets have been edited as well.
To ungroup, right click one of the sheet tabs and click Ungroup Sheets or click any sheet tab outside the group. For example, the sheet tab of Sheet4.
View Multiple Worksheets
If you want to view multiple Excel worksheets at the same time, execute the following steps.
1. Open a workbook.
2. On the View tab, click New Window.
Excel opens a new window containing another view of the document.
3. On the View tab, click Arrange All.
4. Select the desired arrange setting. For example, click Vertical.
5. Click OK.
The titles (view-multiple-worksheets:2 and view-multiple-worksheets:1) indicate that two windows of the same file are open.
SHORT CUT KEYS
Home key: moves the active cell highlight to column A without changing rows.
Ctrl + Home keys: moves the active cell highlight to cell A1.
Ctrl + End keys: moves the active cell highlight to the last cell of the spreadsheet containing data.
Ctrl + Down Arrow keys: moves the active cell highlight to the last row of the spreadsheet without changing columns.
Ctrl + Up Arrow keys: moves the active cell highlight to the first row of the spreadsheet without changing columns.
Ctrl + Page Down keys: moves the active cell highlight to the next sheet of the spreadsheet.
Ctrl + Page Up keys: moves the active cell highlight to the previous sheet of the spreadsheet.
Arrow keys Move one cell in the direction of the arrow
Page Up Page Up
Page down Page down
Tab Move one cell to the right
Alt tab Move one cell to the left
F1 Help
F2 Edit mode to edit a cell’s contents
F3 ???
F4 ???
F5 Go to
F6 Next pane
F7 Spell check
F8 ???
F9 Calculate all
F10 Display key tips
F11 Insert a chart
F12 Save as
F14 Comments
F15 Function arguments
F16 Move to the next available cell in the table
F17 Find and replace
F18 Key tips
F19 Research pane (search, thesaurus etc)
F20 ???
F21 Recalculate????
F22 Right click menu
F23 New worksheet
F24 Save as
Home Start of row
Alt ‘ (apostrophe) Style menu
Alt = Autosum
Alt F Backstage view
Alt H Display shortcuts
Alt N Insert Menu
Alt P Page Layout menu
Cell Reference
=( Start a formula
To create a formula that uses data in certain cells, you’ll need to refer to those cells. In Excel, cell references are given by supplying the column letter and row number. For example, cell C3 exists at the intersection of column C and row 3. Read more about cell reference in Excel.
A Simple Formula – AutoSum
B is called the Formula Bar and it displays the content of the active cell, which in this case is a formula.
C is the active cell and contains the formula.
You can see that the formula starts with a “=”. All formulas must start with “=”. The next part is SUM, which you can guess represents the SUM function. The contents of the brackets are called arguments and they define the range of cells that we are summing – in this case cells B2 to B7. This range is defined as B2:B7. Autosum is a quick and easy formula to use that automatically identifies its input variables.
Cell Basics
Introduction to Cells and Cell Content
The Cell
Each rectangle in a worksheet is called a cell. A cell is the intersection of a row and a column.
Each cell has a name, or a cell address based on which column and row it intersects. The cell address of a selected cell appears in the Name box.
To Select a Cell:
Click on a cell to select it. When a cell is selected you will notice that the borders of the cell appear bold and the column heading and row heading of the cell are highlighted.
Release your mouse. The cell will stay selected until you click on another cell in the worksheet.
You can also navigate through your worksheet and select a cell by using the arrow keys on your keyboard.
To Select Multiple Cells:
Click and drag your mouse until all of the adjoining cells you want are highlighted.
Release your mouse. The cells will stay selected until you click on another cell in the worksheet.
Protect
Encrypt an Excel file with a password so that it requires a password to open it.
1. Open a workbook.
2. On the green File tab, click Save As.
3. Click on the Tools button and click General Options.
4. In the Password to open box, enter a password and click OK.
5. Reenter the password and click OK.
Note: this feature also encrypts your Excel file. If you lose or forget the password, it cannot be recovered.
6. Enter a file name and click Save.
It requires a password to open this Excel file now. The password for the downloadable Excel file is "easy".
PRINT
Print a Worksheet
To print a worksheet in Excel 2010, execute the following steps.
1. On the File tab, click Print.
2. To preview the other pages that will be printed, click 'Next Page' or 'Previous Page' at the bottom of the window.
3. To print the worksheet, click the big Print button.
What to Print
Instead of printing the entire worksheet, you can also only print the current selection.
1. First, select the range of cells you want to print.
2. Next, under Settings, select Print Selection.
3. To print the selection, click the big Print button.
Note: you can also print the active sheets (first select the sheets by holding down CTRL and clicking the sheet tabs) or print the entire workbook. Use the boxes next to Pages (see first screenshot) to only print a few pages of your document. For example, 2 to 2 only prints the second page.
Multiple Copies
To print multiple copies, execute the following steps.
1. Use the arrows next to the Copies box.
2. If one copy contains multiple pages, you can switch between Collated and Uncollated. For example, if you print 6 copies, Collated prints the entire first copy, then the entire second copy, etc. Uncollated prints 6 copies of page 1, 6 copies of page 2, etc.
Orientation
You can switch between Portrait Orientation (more rows but fewer columns) and Landscape Orientation (more columns but fewer rows).
Page Margins
To adjust the page margins, execute the following steps.
1. Select one of the predefined margins (Normal, Wide or Narrow) from the Margins drop-down list.
2. Or click the 'Show Margins' icon at the bottom right of the window. Now you can drag the lines to manually change the page margins.
This example teaches you how to add information to the header (top of each printed page) or footer (bottom of each printed page) in Excel.
1. On the View tab, click Page Layout, to switch to Page Layout view.
2. Click to add header.
The Header & Tools contextual tab activates.
3. On the Design tab, click Current Date to add the current date (or add the current time, file name, sheet name, etc).
Note: Excel uses codes in order to automatically update the header or footer as you change the workbook.
4. You can also add information to the left and right part of the header. For example, click the left part to add the name of your company.
5. Click somewhere else on the sheet to see the header.
Note: on the Design tab, click Options for a different first page header/footer or a different header/footer for odd and even pages. In a similar way, you can add information to the footer.
6. On the View tab, click Normal, to switch back to Normal view.
The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.
Count
To count the number of cells that contain numbers, use the COUNT function.
Countif
To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.
Countifs
To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.
Sum
To sum a range of cells, use the SUM function.
Sumif
To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).
To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).
Sumifs
To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).
General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average cells based on one or multiple criteria.
Logical Functions
Learn how to use Excel's logical functions such as the IF, AND and OR function.
If Function
The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE.
1. Select cell C2 and enter the following function.
The IF function returns Correct because the value in cell A1 is higher than 10.
And Function
The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.
1. Select cell D2 and enter the following formula.
The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.
Or Function
The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.
1. Select cell E2 and enter the following formula.
The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct.
General note: the AND and OR function can check up to 255 conditions.
Cell References
Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.
Relative Reference
By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative.
1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.
Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and cell C5. In other words: each cell references its two neighbors on the left.
Sort
You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.
One Column
To sort on one column, execute the following steps.
1. Click any cell in the column you want to sort.
2. To sort in ascending order, on the Data tab, click AZ.
Multiple Columns
To sort on multiple columns, execute the following steps.
1. On the Data tab, click Sort.
2. Select Last Name from the 'Sort by' drop-down list.
3. Click on Add Level.
4. Select Sales from the 'Then by' drop-down list.
5. Click OK.
Tables
Tables (or lists in Excel 2003) allow you to analyze your data quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table.
Insert a Table
To insert a table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, click Table.
3. Excel automatically selects the data for you. Check 'My table has headers' and click on OK.
No comments:
Post a Comment