MS Microsoft Excel skills, Cheat Sheet of MS Microsoft Excel skills

Download MS Microsoft Excel skills and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity!

Microsoft Excel for Beginners

training@health.ufl.edu

Vocabulary Microsoft Excel is a spreadsheet program. We use it to create reports that need calculations and charts.

1. An Excel file is called a Workbook.
   • Default title is Book
2. Ribbon broken into Tabs (Home, Insert, Page Layout…)
   • Tabs broken into groups (Clipboard, Font, Alignment)
3. Name box (left) and formula bar (right)
   • Name box shows address of current cell
   • Formula bar shows contents of current cell
4. Columns Headings are Lettered, Rows Headings are Numbered
   • Columns of a building, rows of chairs
5. Worksheet navigation buttons, Worksheet tabs
   • Sheet
6. Status bar
   • Excel behaves differently depending on the current "mode"

Status Bar Modes ‐ Ready mode. This means nothing is being enter ed or edit ed on the spreadsheet.

‐ Enter mode. This mode is when you are doing data entry, just typing in the contents. ‐ Edit mode. Edit the contents of the current cell. Double‐click on a cell with data in it, or click inside the formula bar for this mode. ‐ Point mode. Used when linking to cell addresses within a formula or from an Excel dialog window.

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

Number

1. Number Format – Allows you to change the way numeric values are displayed on the spreadsheet. The drop down arrow gives you a list of the most common formats, including a More Number Formats option.
2. Currency Style – Sets the selected cell(s) to the Currency Style , this style keeps the dollar signs on the left side of the cell, and the number on the right side. The drop down arrow gives you a list of other currency formats, such as the Euro (€).
3. Percent Style – Sets the selected cell(s) to the Percent Style , this style has zero decimal places. Keyboard shortcut ‐ Ctrl‐Shift‐%. This button can be reset through Cell Styles on the Home Tab.
4. Comma Style – Sets the selected cell(s) to the Comma Style , this style has a comma for every thousand and two decimal places. This button can be reset through
5. Increase Decimal – Increases the number of decimal places showing to the right of the decimal.
6. Decrease Decimal – Decreases the number of decimal places showing to the right of the decimal.
7. More Options – This button will open the Format Cells dialog window to the Number Tab.

Cells Structures There are a set number of cells within a Microsoft Excel worksheet. In the Ribbon versions (2007 and later) there are 16,384 columns and 1,048,576 rows. As you insert and delete structures, you are not reducing the number of cells, merely shifting where your data lies on the defined worksheet. Think about moving a painting around on a wall. You're not changing the wall, just the position of the painting.

Inserting We use Insert to make new cells, columns, and rows.

Excel determines what you are trying to insert based on your selection. If a full column is selected, Excel will assume you mean a full column and it will skip the Insert window.

You can insert a cell, row, or column by doing one of the following:

 Press Shift ‐ Ctrl ‐ = on the keyboard (ctrl plus)  or from the Home tab, in the Cells group, choose Insert  or open the Right‐click menu and choose insert.

‐ To insert multiple at once, select the number of cells/rows/columns you would like to insert and follow the steps above. ‐ The size and format of the new space is determined by the previous row or column. ‐ This will push the existing cells, columns, or rows to the right or down to make room for the new cells.

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

Use the mouse to point to the cell address

The mouse and arrow keys are both "pointers". If you press the equal sign and then use the mouse to click on another cell, Excel will put you into a "POINT" mode, and place the address of the cell you clicked on in your equation.

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

A B C

1 1 2 =A

Mathematical Operations To let Excel know you expect it to "do math" you need start your cell with an equal sign (=). ‐ Addition, plus sign (+) = 5+2 result 7 ‐ Subtraction, hyphen (‐) = 5 ‐ 2 result 3 (also used for negative) = ‐ 5 result ‐ 5 ‐ Multiplication, asterisk () = 52 result 10 ‐ Division, slash (/) = 5/2 result 2. ‐ Exponent/Power, caret (^) = 5^2 result 25

AutoSum We can build equations to do math on a large number of cells, but there are functions built into Excel that can help us automate the most common ones: Sum, Average, Count, Maximum, Minimum. On the far right of the Home tab you'll find the sigma ( ∑ ).

When you click on the word AutoSum, you'll get a sum function. There is a dropdown list at the end of the button that will show more function options. The AutoSum button looks for numbers above or to the left of the cell to choose the range (the set) of numbers. Make sure to press enter or click the check to accept as soon as the function shows up. If you click outside the cell while you see the function, you may break the equation.

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 2: Quarter Total

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

Fill Handle

1. If needed, Move to Cell A a. Hover your mouse over the bottom right corner of the cell until it turns into a thin crosshair/plus sign. This is called the Fill Handle. b. Drag the Fill Handle down to the bottom of Row 5 c. Cells A1 through A5 now all say Quarter d. UNDO!

2. Move to Cell A a. In Cell A2 type: 1st Qtr b. Press Enter or the click the Check to accept i. If needed, return to Cell A c. Drag the Fill Handle for Cell A2 to the bottom of Row 5 i. 1st Qtr, 2nd Qtr, 3rd Qtr, 4th Qtr

Format

1. Select titles in Cell s A1 and B a. Bold b. Bottom Border c. Center

2. Select the numbers in Cell s B2, B3, B4, and B a. Comma Format b. Decrease Decimals to zero/none

Chart

1. Return to Cell A1 (Ctrl‐Home)

2. From the Insert tab, Recommended Charts a. Opens the Insert Chart window

3. Click OK to accept the Column Chart option

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.

Excel

Fundamentals

Microsoft Excel

• Updated: 1/16/
• 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
• Understanding Workbooks Contents
• Navigating in a File
• Typing Text or Numbers Into A Worksheet
• Typing Simple Formulas In A Worksheet
• Filling A Series
• Inserting And Deleting Worksheets
• Copying A Worksheet
• Renaming A Worksheet
• Moving or Copying A Sheet To Another Workbook
• Changing Worksheet Tab Colours
• Grouping Worksheets
• Freezing Rows And Columns
• Selecting Ranges
• Selecting Rows
• Selecting Columns
• Understanding Formatting.................................................................................................................................................
• Applying General Formatting
• Changing Fonts
• Changing Font Size
• Understanding Borders
• Applying A Border To A Range
• Wrapping And Merging Text
• PRACTICE EXERCISE
• PRACTICE EXERCISE
• PRACTICE EXERCISE
• Understanding Functions
• Using The SUM Function To Add
• Calculating An Average
• Finding A Minimum Value
• Common Error Messages
• PRACTICE EXERCISE
• Understanding Quick Analysis
• Quick Formatting
• Quick Charting
• Quick Totals
• Quick Sparklines
• Quick Tables
• Practice Exercise
• Printing A Worksheet
• The Charting Process St. George’s Information Services
• Choosing The Right Chart
• Using A Recommended Chart
• Creating A New Chart From Scratch
• Working With An Embedded Chart
• Resizing A Chart
• Repositioning A Chart
• Printing An Embedded Chart
• Creating A Chart Sheet....................................................................................................................................
• Changing The Chart Type
• Changing The Chart Layout
• Changing The Chart Style
• Printing A Chart Sheet
• Embedding A Chart Into A Worksheet
• Deleting A Chart
• PRACTICE EXERCISE
• PRACTICE EXERCISE SAMPLE

Microsoft Excel

St. George’s Information Services