Microsoft Excel Basics: Formatting, Navigation, and Mathematical Operations, Exercises of MS Microsoft Excel skills

Download Microsoft Excel Basics: Formatting, Navigation, and Mathematical Operations and more Exercises MS Microsoft Excel skills in PDF only on Docsity!

Microsoft Excel for Beginners

Microsoft Excel for Beginners

2.0 hours

This is a basic computer workshop. Microsoft Excel is a spreadsheet program. We use it to

create reports that need calculations and charts. In this workshop we will learn how to move

• Vocabulary around and work inside the spreadsheet.
• Status Bar Modes
  • Keyboard Navigation
• Ribbon
  • Clipboard
  • Formatting Cells
• Cells Structures
  • Inserting
  • Deleting
  • Cell Size (Row Height/Column Width)
• Fill Handle
• Building an Equation
  • Type in the exact cell address
  • Use the mouse to point to the cell address
  • Mathematical Operations
  • AutoSum....................................................................................................................................................
• Exercise 1: Customers
  • Resizing Columns
  • Freeze Panes (Lock Titles to Top of Page)
  • Format
• Exercise 2: Quarter Total
  • Fill Handle................................................................................................................................................
  • Format
  • Chart
• Exercise 3: Items by Quarter
  • Insert Rows..............................................................................................................................................
  • Merged Title
  • Fill Handle Across
  • Total Row (AutoSum)
• Exercise 4: Sales Report
  • Format
  • Math
  • Grand Total

Keyboard Navigation

Key Ready Enter Edit Point Enter Move Down Accept changes and move down Shift‐Enter Move Up Accept changes and move up Tab Move Right Accept changes and move right Shift‐Tab Move Left Accept changes and move Left

Arrow Keys Moves to another cell Moves between characters in cell

Points to an address of a cell

Home Moves to first column Moves to the front

Type in the exact cell address

Points to cell in column A Ctrl‐Home Moves to the beginning cell of the worksheet (A1)

Points to the beginning of the worksheet

Ribbon The images of Excel in this packet were copied from a wide screen monitor. With the wide screen the ribbon is stretched across the window and I can see all the buttons. If you are working on a narrower window, Excel will try to clump the groups together and the layout may look a little different than the ones shown here, but all the buttons will be there.

Here we can see how the font group is now three buttons high, and how some of the buttons like Cut and Copy have lost their text labels.

Clipboard

Cut, Copy and Paste are clipboard features built into Windows. The clipboard is a temporary storage place for pictures and data. The Windows clipboard can only store one item at a time. Microsoft Office has a Multi‐Clipboard that can store 24 items, but the Paste button and the shortcuts for the Paste option only correspond to the most recently copied item. The clipboard pane must be displayed to be able to use this feature.

Cut – Copies selection to the clipboard. If the selection is text or an image, it will disappear. If it’s a cell, Excel waits until you paste it to delete the original cell.

Copy – Copies selection to the clipboard.

Paste – Retrieves most recent text/object on the clipboard.

Formatting Cells

The most formatting options are found on the Home Tab. All the options can be found in the Format Cells window. This contains several tabs to help us format the contents of our spreadsheet. This window can be opened by using the More Options button at the end of the Format , Alignment and Number groups. You can also use the Keyboard Shortcut – Ctrl‐ 1 or choose Format Cells… from the right‐click shortcut menu.

Font

1. Font – Sets the font of the selected cell(s). Fonts are different ways to show the same letters.
2. Font Size – Sets the size of the letters (the font). Larger numbers give larger fonts.
3. Increase Font – Increases the font size
4. Decrease Font – Decreases the font size
5. Bold – Makes the selected cell(s) Bold
6. Italic – Makes the selected cell(s) Italicized
7. Underline – Makes the selected cell(s) Underlined. The drop down has a double underline.
8. Borders – Adds and removes borders for the selected cell(s). The drop down has More Borders…
9. Fill Color – Changes the background color of the selected cell(s).
10. Font Color – Changes the color of the font of the selected cell(s).
11. More Options – This button will open the Format Cells dialog window.

Alignment

1. Top Align – Vertically aligns to the top of the cell.
2. Middle Align – Vertically aligns to middle of the cell.
3. Bottom Align – Vertically aligns to the bottom of the cell.
4. Orientation – Rotates the contents of the cell to the currently displayed option.
5. Wrap Text – Displays contents on multiple lines within the cell's column width.
6. Align Text Left – Horizontally aligns the contents to the left side of the column.
7. Center – Horizontally aligns the contents to the center of the cell.
8. Align Text Right – Horizontally aligns the contents to the right side of the cell.
9. Decrease Indent – Decreases the space between the text and the cell border
10. Increase Indent – Increases the space between the text and the cell border
11. Merge and Center – Joins selected (adjacent) cells into one cell and centers the result. If there is data in more than one cell, Excel will only keep the information from the upper left cell.
12. More Options – This button will open the Format Cells dialog window to the Alignment Tab.

1 2 3 4 5

6 7 8 9 10 11 12

Deleting

We use Delete to remove cells, columns, and rows. Excel determines what you are trying to delete based on your selection. You can delete a cell, row, or column by doing one of the following:  Press Shift ‐ Ctrl ‐ ‐ on the keyboard (Ctrl Minus)  or from the Home tab, in the Cells group, choose Delete  or open the Right‐click menu and choose insert.

‐ To delete multiple at once, select the number of cells/rows/columns you would like to delete and follow the steps above.

‐ This will completely remove the structure, formatting and all, and the rows/columns/cells will shift into this place. If you only intended to delete the contents not the cells, undo and use the Clear Contents option instead.

Cell Size (Row Height/Column Width)

You cannot resize one cell; the structure is dependent on the entire row and column where it resides. The Row Height and Column Width settings can be found under the Format menu in the Cells group of the Home tab.

Adjusting with the Mouse When we resize we are growing away from the left. To resize the column, place your mouse cursor between the lines of the column headings. The current column heading is in a box; all you need to do is resize the box to make it wider. Put your mouse along the right side of the heading box until you see the resizing arrow pointing in two directions. Click and drag away from the column letter. When you let go of the mouse, the column will resize.

To resize the row, place your mouse cursor between the lines of the row headings. The current row heading is in a box; all you need to do is resize the box to make it wider. Put your mouse along the bottom side of the heading box until you see the resizing arrow pointing in two directions. Click and drag away from the row number. When you let go of the mouse, the row will resize.

Auto‐fitting You can use the option found on the Format menu, or place your mouse cursor between the headings, with the two‐way arrow to help resize, and double‐click. The row or column should AutoFit to the largest data length within its structure. ‐ To resize multiple at once, select the cells you would like to fit and follow the steps above. If you are using double‐click to auto‐fit, the entire column/row structures must be selected.

Fill Handle The Fill Handle is in the bottom right corner of the selected cell. When you place your mouse over this handle , it changes from a thick white cross, to a thin black cross. Once you see the thin cross (no arrows) you can click and drag the cell to fill its contents in a single direction (up, down, left or right). If you want to go in two directions, you must first complete one way, let go of the mouse and then drag the handle in the second direction. When you use the Fill Handle to pull down a single number or plain text, it will copy the data. When you use the Fill Handle to pull down a text with numbers, a date, a month or a weekday it will fill in a series.

Text 123 Exam 1 2/1/02 February Friday Text 123 Exam 2 2/2/02 March Saturday Text 123 Exam 3 2/3/02 April Sunday

When you select two or more numbers (including dates) and then use the Fill Handle , Excel will fill in the series, following the original pattern of the selected cells. It can only follow simple addition and subtraction patterns.

123 5 100 2/01/ 124 4 110 2/08/ 125 3 120 2/15/ 126 2 130 2/22/

Building an Equation

You can directly type in values, but that data stays constant. If you want to have the answers to your equations update as you change your data, you should use the cell addresses. You will see the cell addresses change colors so you can tell which ones are used in your equation. Type in the exact cell address

Cells are labeled by their row and column headings. Rows are numbered and go horizontally across (rows of chairs) and columns are lettered and go vertically top to bottom (columns of a building). When we refer to the address of a cell, we use the column letter then the row number such as A1.

• Click in the cell where the answer will appear
• Press the Equal sign (=)
• Type in the cell address you want to use in your equation
• Accept the answer or press the next math operator (+, ‐, *, /, ^)

A B C 1 1 2 =a1+b 2

Exercise 1: Customers

Resizing Columns

1. Put your mouse on the line between any two Column letters. It will turn into a 2 ‐way arrow. a. Hold down the mouse button and drag to resize b. Double‐click between the headings to "AutoFit"

2. Select the entire worksheet by clicking on the triangle above the Row 1, left of the Column A a. Try to resize any Column ; all the selected columns will change b. Double‐click between the headings to have it "Auto fit"

Freeze Panes (Lock Titles to Top of Page)

1. Press Ctrl‐Home on the keyboard to return to Cell A

2. Turn to the View Tab in the Ribbon

3. Find the Option Freeze Panes a. Choose Freeze Top Row b. Scroll down through the worksheet to see the titles in Row 1 stay at the top

Format

1. Click on the Row Heading for Row 1 (click on the number 1 ) to select the entire row  From the Home Tab, or right‐click menu, choose B for bold
2. Click on the Column Heading for Column G to select the entire Column  From the Home Tab, choose $ for an accounting format  Adjust the Column width again
3. Find a street name with a "fruit" address and use the Fill button (the bucket ) to shade it a peach/orange color  Change a few of them to match
4. Find a street name with a "vegetable" address and use the Fill bucket to shade a greenish color  Change a few of them to match

Exercise 3: Items by Quarter

Turn to the next worksheet at the bottom of the window, Items by Quarter.

Insert Rows..............................................................................................................................................

1. Select Row 1 and Row 2  Click on the row heading 1 and drag to row heading 2
2. Right‐click Inside the selection  Choose Insert

Merged Title

1. In Cell A1 type: Quarterly Sales Report
2. Select Cells A1 through E  Click the Merge and Center button
3. Format: Bold, Fill, Border

Fill Handle Across

1. In Cell B3, delete Qtr 1, and type: 1st Qtr
2. Drag the fill handle for Cell B3 across to Cell E
3. Center and Bold the new titles

Total Row (AutoSum)

1. In Cell A8 type: TOTAL
2. In Cell B8 Click on the AutoSum button  =SUM(B4:B7)  Press Enter or click the check to accept (2079)
3. Drag the Fill handle in Cell B8 to Cell E8 to fill in the "sum" pattern for each quarter

The Fill Handle is the small square in the bottom right corner of a selected cell.

Exercise 4: Sales Report

Turn to the next worksheet at the bottom of the window, Sales Report.

Format

1. Row 1 ‐> Bold

2. Column B ‐> Accounting ($)

3. Column C ‐> Centered Aligned

4. Cell C5 ‐> Right Aligned

Math

Total for each line item will be the Price times the Quantity.

1. Go to Cell D

2. From the keyboard Type: =

3. With the mouse click on Cell B2 ($10.00)  Cell D2 should now have =B

4. From the keyboard type: *

5. With the mouse click on Cell C2 (5)  Cell D2 should now have =B2*C

6. Press Enter or click the check to accept  Answer: $50.  If needed return to Cell D

7. Drag the Fill Handle for Cell D2 to Cell D to fill in the pattern for the formula

Grand Total

1. Move to Cell D

2. From the Home tab click on the AutoSum ∑  =SUM(D2:D4)

3. Press Enter or click the check to accept  Answer: $600.

4. Change Cell B2 to $12.50 and press enter or click the check to accept  Grand Total should be $612.