Tuesday, April 7, 2015

Introduction to Microsoft Excel

By Unknown 4:46 AM


WHAT IS A SPREADSHEET

A spreadsheet is used to carry out everything from simple addition to complex financial and statistical analyses.  Spreadsheet programs can also help you create charts and graphs based on the data you’ve entered.

 

Brand Names of Spreadsheet Software

  • Microsoft Excel
  • Microsoft Works Spreadsheet
  • Lotus 1-2-3

BASIC EXCEL SKILLS


Starting Up Microsoft Excel

  1. Click on START, choose PROGRAMS and click on Microsoft Excel
  2. You will see a white grid in front of you with rows of menus and icons at the top of the screen.

The Key Components of a Spreadsheet

  1. Title Bar: The bar that displays the name of the spreadsheet you currently have open.
  2. Name Box: The box that displays the address of the cell that you are presently on.
  3. Formula Box: The bar where the contents of the cell are displayed.
  4. Worksheet Tabs: These tabs allow you to switch from one worksheet to another. The standard spreadsheet contains three worksheets.


 





Name Box
 

A Cell
 





Entering Data into a Spreadsheet

  1. To enter data into a cell, simply click into the cell and type.
  2. ACTIVITY: 
    1. Start in cell A1, and type “Day of the Week”.  Press Enter, or click your mouse into cell A2 and type “Monday”.  Continue down Column A until you’ve typed Monday through Sunday.
    2. Click on cell B1 and type “Customers”.  Proceed to cells B2-B8 begin typing in the numbers: 20, 10, 30, 50, 60, 100, 70.

Entering a Formula into a Spreadsheet

1.  What is a formula?  It is a command that instructs Excel to carry out a calculation.  Addition, subtraction and averaging are all examples.
  1. ACTIVITY:
    1. In cell A9, type “Total”, then,
    2. Move to cell B9 and type “=SUM(B2:B8)” and then press ENTER.  You should see a total appear in cell B9.

Calculating Four Basic Math Operations


Addition:
1.  Use the SUM command, which is    =SUM(beginning cell address:ending cell address) and press ENTER.  OR,
  1. If the numbers are not adjacent to each other, you can use the “+” operator as follows:
      = first cell to be added + second cell to be added + third cell to be added, and so on.  Then press ENTER.
    

Subtraction

1.  Use the – operator     = first cell – second cell and then press ENTER

Multiplication

1.  Use the * operator      = first cell * second cell and then press ENTER

Division

1.  Use the / operator      = first cell / second cell and then press ENTER.

Making Changes to the Contents of a Spreadsheet Cell

ACTIVITY:  We made a mistake calculating Tuesday’s customers.  There were actually 16 customers, not 10!
  1. Click on the cell whose contents you would like to change
  2. See the present contents of the cell appear in the FORMULA Bar and change the data.
  3. Notice that the TOTAL was automatically recalculated (It now reads 346 instead of 340!)

Saving Your Spreadsheet

  1. Click on FILE and choose SAVE AS if you are saving the spreadsheet for the first time, otherwise, choose SAVE.
  2. Choose what location you would like to save your spreadsheet in and click ok.

Printing a Spreadsheet

  1. Click on File and choose PRINT. 
  2. If you’d like to only print certain pages then use the “Print Range” section of the Print dialog box and choose which pages you would like to print. 

Fitting a Spreadsheet onto One Printed Page

  1. If you want your spreadsheet printed on one page instead of spanning two pages, follow these steps:
  2. Highlight all the cells of your spreadsheet then click on FILE and choose PRINT AREA. (in Excel 2007, you would select PAGE LAYOUT from the main menu, then select PRINT AREA)
  3. Click on SET PRINT AREA and see that a dotted line appears around your spreadsheet cells.
  4. Click on FILE and PRINT PREVIEW.
  5. Click on SETUP and click on the radio button beside (under the heading SCALING) FIT TO 1 PAGE WIDE BY 1 PAGE TALL. (in Excel 2007, click the small arrow in the bottom right corner of the SCALE TO FIT shortcut box to access this option)
6.      Click on OK, Click on PRINT and Click OK.

INTERMEDIATE EXCEL SKILLS:  EDITING A SPREADSHEET

Inserting Columns

ACTIVITY:  The regional manager says we need to start tracking the number of visitors to our story each day.  She wants us to insert a new “Visitors” column BETWEEN the current “Days of the Week” column and the “Customers” column in order to track this data. 
  1. Click into any cell within the column that will become the first column to the right of the new column.  Which column is that for us??
  2. Now click on INSERT and choose COLUMN.  You will see a new column appear. (in Excel 2007, in the CELL quick menu, click the word INSERT, select INSERT SHEET COLUMNS)
ACTIVITY:  Starting in B2, type the following number of visitors for each day:  30, 32, 45, 90, 65, 200, 130.

Inserting Rows

  1. Click into any cell within the row that will become the first row below the new row.
  2. Click on INSERT and choose ROW.  (in Excel 2007, in the CELL quick menu, click the word INSERT, select INSERT SHEET ROWS)
  3. You will see a new row appear.

Inserting Cells

  1. Click into the cell where a new cell needs to be inserted. 
  2. Click on INSERT and click on CELLS (in Excel 2007, in the CELL quick menu, click the word INSERT, select INSERT CELLS)
  3. Choose one of the four choices that appear:
    1. SHIFT CELLS RIGHT means that when you insert the new cell, the existing cell will be shifted to the right.
    2. SHIFT CELLS DOWN means that when you insert the new cell, the existing cell will be shifted down.
    3. The other two choices, ENTIRE ROW, and ENTIRE column are alternative ways to inserting rows and columns.


Copy and Pasting

In Excel, you use the Copy and Paste feature when you want to duplicate a cell or block of cells in another location within the spreadsheet.  The original cell will remain in the original location, but a second copy is found at the new location.
ACTIVITY:  The regional manager wants a total number of visitors for the week.  We can copy the formula from C9 into box B9.
  1. Click on C9 (the source cell). 
  2. Click on EDIT and choose COPY.
  3. Click on cell B9 (the destination cell) and click on EDIT and choose PASTE. 
  4. See the total appear.
  5. *NOTE* Excel converts the formula’s column “C” references into column “B” references.  To prevent this automatic adjustment, which is called Relative Cell Referencing, place $ signs in front of the column and/or row reference that you don’t want Excel to adjust when that cell is copied to another location.  For example, if C9 contained “=SUM($C$2:$C$8)”, when it is copied to B9, it will still read “=SUM($C$2:$C$8)”.  When Excel’s relative cell referencing is blocked by $ signs, it is known as Absolute Cell Referencing. 
  6. With Excel 2007, use the CLIPBOARD quick menu to access copy and paste features. You may access the copy and paste options by clicking the right mouse button to bring up the quick menu.

Cutting and Pasting

Use this function when you want to move a cell or block of cells from one location to another.  No trace of the cell(s) will remain in the original location.
  1. Click on the cell(s) you would like to cut (the source cell).
  2. Click on EDIT and choose CUT.
  3. Click on the cell where you want this cell to reappear (the destination cell) and click on EDIT and PASTE. 
  4. See the cut-cell reappear in the new location.
  5. With Excel 2007, use the CLIPBOARD quick menu to access copy and paste features. You may access the copy and paste options by clicking the right mouse button to bring up the quick menu.


INTERMEDIATE EXCEL SKILLS:  FORMATTING A SPREADSHEET

Formatting a Cell
1.      You must highlight the cell or block of cells you want to format.
2.      After you highlight the cells, choose FORMAT from the menu and select CELLS to see your choices.
    1. Number:  Allows you to select the format for the type of data within the cells.  For example, choose CURRENCY if you are using dollar values, and Excel will add a $ sign in front of the cells.
    2. Alignment:  Allows you to control the positioning of the text or number in your cell.  If you have a lot of text in one cell, and you don’t want to expand the cell’s width, you can choose WRAP TEXT. 
    3. Font: You can change the style and size of the highlighted text or numbers, including making them bold, underlined or italic.  This is the same as the Microsoft Word formatting. 
    4. Border:  Allows you to add vertical or horizontal lines to different parts of the worksheet. You can choose where you’d like the grid lines to print out, the line style, and even the line color. 
    5. Patterns: Allows you to control the background color of the highlighted cells and to add patterns.  Use this to visually isolate data.
    6. Protection: Allows you to lock cells and hide formulae from careless or unauthorized users.  Remember, if you password protect your spreadsheet, make sure you remember the password!
    7. In Excel 2007, you can access most of these features from the quick menus under the HOME tab. To access the features for border, pattern, and protection, click the small arrow from the FONT, ALIGNMENT, or NUMBER quick menus to access these and other formatting features.
ACTIVITY:  Give row A the following characteristics:  bold, underlined and 16 font. 

Format the Height of a Row

1.      Click on any cell in that row and click on FORMAT and choose ROW.
2.      Click on HEIGHT and type in a new number for the Row Height.
3.      In Excel 2007, select a row and right click to access the quick menu where you will find the row height feature or under the CELLS quick menu click the word FORMAT
      Note: The standard setting is 12.75.

Format the Width of a Column

1.      Click on any cell in that column and click on FORMAT and choose COLUMN.
2.      Click on WIDTH and type a new number for the Column Width. 
3.      In Excel 2007, select a row and right click to access the quick menu where you will find the column
width feature or under the CELLS quick menu click the word FORMAT
Note: The standard setting is 8.43.

ACTIVITY: Adjust the column width so that cell A1’s “Days of the Week” label is entirely viewable.  If you don’t want to guess at the appropriate Column Width, then select AUTOFIT SELECTION instead and Excel will decide for you. 

Format A Sheet
Formatting a sheet lets you do two things: 
1.      Change the name of the sheet:
a.       Click on the tab you would like to rename. 
b.      Click FORMAT select SHEET (in and click on RENAME.
c.       You will see the name of the sheet darken.  Type in a new name and press Enter.
d.      In Excel 2007, click FORMAT under the CELLS quick menu, then select RENAME SHEET
2.      Change the Background Image
a.       Click on the tab whose background you would like to change.
b.      Click FORMAT, select SHEET and click on BACKGROUND.
c.       Locate the image file and click INSERT.
d.      In Excel 207, select the PAGE LAYOUT tab, then click on the BACKGROUND option to access this feature

INTERMEDIATE EXCEL SKILLS:  CHARTS

ACTIVITY:  We want to impress our regional manager with graphically displayed data.
1.      Highlight your spreadsheet, including the column headers. (Days of the Week, Visitors, etc.)
2.      Click on INSERT and choose CHART. (In Excel 2007, click on the INSERT tab, and then select the type of chart you would like to create from the options provided – the rest of the instructions below are ONLY for Excel 2000 and 2003) – For Excel 2007, to make various chart edits it is easiest to right click on the portion of the chart you would like to change and right click
3.      Click on the Type of Chart you would like and click NEXT.
4.      You’ll be presented with a draft of what your new chart will look like.  Click on Next. 
5.      The dialog box that appears will allow you to add some text labels to your Chart, such as a Title, and labels for the X and Y axes.  Then click NEXT
6.      This dialog box asks you where you would like your chart to appear—in a new worksheet, or as an object in your current sheet. 
7.      Click on FINISH.

ADVANCED EXCEL SKILLS
Functions

Excel can perform a very large number of functions, which are formulas that perform a specific calculation beyond standard arithmetic.  If you go to INSERT and choose FUNCTIONS, you will see a large menu of functions. 
  1. If you want to use a certain function, first click in the cell on your spreadsheet you want that function performed in.  Then go to INSERT and choose FUNCTION and select the function you want to perform. 
  2. Specify the cell or block of cells that you want the function to consider.
  3. Click OK.
  4. To insert functions in Excel 2007, you would choose the FORMULAS tab, then select the function you would like to perform from the function library and follow steps 2 and 3.

ACTIVITY:  The AVERAGE function is useful.
  1. Click into cell A10 and type, “Daily Average”
  2. Click into cell B10 and click on INSERT and then FUNCTION
  3. Click on ALL, click on the AVERAGE function, and then click OK.
  4. In the “Number1” field, type in the cells you would like to average
*NOTE* If the dialog box is blocking your view of the spreadsheet and you can’t remember the location of the cells you’d like to average, click on the symbol at the end of the “Number1” field.  Your spreadsheet will pop up and you’ll get a chance to highlight the block of cells you’d like to average.  Then press ENTER.
5.  Click on OK.

ACTIVITY:  To practice functions, try to apply the MAX function to have Excel determine the highest daily Customer total. 

Adding and Deleting Comments to the Spreadsheet
  1. Click on the cell that you would like to add a comment to.  Click on INSERT and then COMMENT
  2. In Excel 2007, click on the cell where you want to add the comment and right click for the quick menu, then select INSERT COMMENT.
  3. Type your comment in the box that appears and then click in a cell outside the comment.
  4. A small red tag appears at the top right corner of the cell that contains the comment.

ACTIVITY:  Add a comment to cell B7 that says, “This was the Saturday before Christmas so sales were particularly strong that day”

  1. To delete a comment, click EDIT, the select CLEAR, and then select COMMENT.
  2. In Excel 2007, to perform these functions right click the cell again for the quick menu and access the option this way.

Sorting Data
ACTIVITY:  The regional manager would like us to sort our data in a way that ranks the “Days of the Week” from the busiest day to the least busy day based on the number of “Visitors”.
  1. Highlight the data, including the titles, but excluding the “Total” and “Daily Averages” rows.
  2. Click on DATA and then SORT.
  3. Click on the drop-down arrow below the “Sort By” header and select the criteria by which you want to re-sort your data.
  4. Click either “Ascending” or “Descending”.
  5. Click OK.
  6. For Excel 2007, click the DATA tab, and use the SORT quick menu options to perform various sort types
*NOTE* re-sorting the data by “Days of the Week” in ascending order will not return the data to a Monday – Friday order.  It will re-sort the data based on the alphabetical order!! To get it back to Monday-Sunday, click on EDIT and UNDO SORT.


To Keep Row and Column Labels Visible As You Scroll
  1. To freeze the tope horizontal pane, select the row below where you want the split to appear.
  2. To freeze the left vertical pane, select the column to the right of where you want the split to appear.
  3. Click on WINDOWS menu and choose FREEZE PANES.
  4. To UNDO, return to the WINDOWS menu and choose U NFREEZE PANES.
  5. For Excel 2007, to freeze panes you would choose the VIEW tab, and then click on word FREEZE PANES.

Filtering Out Data
ACTIVITY: The regional manager wants just the data for Saturdays.

  1. Click anywhere within your data.
  2. Click on DATA FILTER and then AUTOFILTER
  3. Click on one of the drop-down arrows and select the criteria you would like to filter the data through.
  4. Excel pulls out just the data you requested.
  5. To get rid of the filter,
    1. Click on DATA, click on FILTER and choose AUTOFILTER.
  6. With Excel 2007, the easiest way to perform filtering is by selecting the data to be filtered, clicking on the DATA tab, then click FILTER under the SORT and FILTER option

Unknown

Author & Editor

Has laoreet percipitur ad. Vide interesset in mei, no his legimus verterem. Et nostrum imperdiet appellantur usu, mnesarchum referrentur id vim.

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

 
biz.