Excel tutorial

810 4 3 Excel Tut Part1

Introduction to Excel Part One

Purpose

Upon completion of this session you will be able to:

  1. Describe the general purposes and uses of an electronic spreadsheet
  2. Modify and format an existing Excel spreadsheet
  3. Output an excel spreadsheet to a flash drive, printer, and chart

Software Needed

Microsoft Excel. Please note that the screenshots are from a Windows machine, however, the same steps would apply to a Mac platform.

Prerequisite Knowledge and Skills

Before beginning this tutorial please make sure that you have completed all of the Word tutorials. You should have also completed all of the Basic Computing tutorials.

Overview

Lesson 1: Electronic Spreadsheet

Excel is an electronic spreadsheet. A spreadsheet program can store, manipulate, and create graphical representations of data. It can be integrated into the curriculum in lessons that have information that can be quantified.

Excel spreadsheet example – An empty spreadsheet with a task pane opened. Remember that a task pane has common tasks readily displayed.

ExcelSpreadsheet

Info
There may be an Excel icon on the desktop or in the status bar at the bottom of the screen. Clicking on the icon will launch the Excel program.

Activity 1: Opening Excel

In this activity you will be opening the spreadsheet program Microsoft Excel and entering text into an Excel document.

  1. Turn on your computer.
  2. Click on the Start button startthen click on All Programs> Microsoft Office> Microsoft Excel ExcelIcon. (For Macs go to the Finder Menu and click on Go then click on Applications. In the Application window click on the link to Microsoft Office. In the Microsoft Office window click Excel. )

Excel

Elements of importance:

  • Columns
  • Rows
  • Cells

We will discuss these elements further in this tutorial.

Info
Link for the Excel Demo spreadsheet
Right Click Here

Activity 2: Downloading, Saving and Opening an Existing File

This activity will use a file entitled demoXP.xls that has already been created and saved on the Edutech site. To download and save this file:

  1. Right click here
  2. Select Save Target As… (In FireFox you will select Save Link As.)
    A Save As dialogue box will open allowing you to change the file name and the location where the file is saved. You should include your initials in the file name and choose a folder location where your course files are stored on the hard drive. In previous tutorials you should have created a course work folder and a CEP 810 folder. Save this file in your CEP 810 folder.
  3. Click on Save and the demoXP.xls spreadsheet will be saved to your folder.

To Open the File:

  1. In the main Excel menu bar click once on the File menu, scroll down and click on Open. ( File> Open )
  2. Navigate through your folder directory and click on the demoXP file. (You should have added your initials to the file name.)

ExcelDemo

Lesson 2: Spreadsheet Window – Elements Common with Microsoft Word

In this next lesson you will learn about the main elements of the Excel window. The elements shown below are similar to elements found in Microsoft Word.

 

Title Bar

ExcelTitle
The Title Bar lists the name of the software program you have open and then lists the name of the specific document you are viewing. In this case, Microsoft Excel is open with the document name of Book1 showing. Microsoft and other software use a default naming system of Book1, Book2 etc., to automatically name files until you change the name to a descriptive word meaningful to you. Because you have opened the demoXP file, your title bar should show the name of that document.

 

Menu Bar

ExcelMenu
The terms (words) in the Menu Bar each represent a different submenu. To view the menu- mouse over the word in the menu bar and a pull down menu appears. The options in the pull down menu represent functions that are relevant to the term in the menu bar. If you see a downward pointing double arrow then you are viewing only the most frequently used options. Click on the double arrow to show all available options.  

 

Standard Toolbar

ExcelTool
The Standard Toolbar has icons for frequently used items. This bar will change to reflect recently used icons as you use the software. You can move any toolbar to other locations in the spreadsheet by clicking and holding on the four vertical dots on the left side of the toolbar.
The separator line, highlighted below, provides additional handles for moving the toolbar within the page.
ExcelSep

 

Formatting Toolbar

ExcelFormat
The Formatting Toolbar allows you to quickly make formatting changes to text and cells. Specifically, these buttons will allow you to make changes to the appearance of your document. The icons in this bar will vary as you use the program. If the icon you want to use does not appear in this menu, click on the toolbar options icon at the far right of this bar to select the option you would want to add.
ExcelFormat2

 

Scroll bar

ExcelScroll
The Scroll Bar allows the viewer to view different parts of the spreadsheet that may not be viewable because of the screen size. You can move the vertical scroll bar up or down and the horizontal scroll bar left or right.

 

Status Bar

ExcelStatus
The left side of the Status Bar shows the possible states you have for each cell; Ready, Enter, or Edit. The other cells show the result of formulas used in selected cells.

 

 [back to top]

Lesson 3: Spreadsheet Window – Unique Elements

These elements are unique to Excel and other electronic spreadsheets.

 

Formula Bar

ExcelFormula
The Formula Bar shows the selected cell on the left. The fx box on the right provides an area for entering data or formulas into the cell.

 

Column & Row Header

ExcelHeaders
In the Column & Row Header, each column is labeled with a letter and each row is associated with a number. The columns, which go from the top of the page to the bottom, just like a column on a building, begin with A and go through the alphabet repeatedly with a letter sequence of AA, AB, …IV until 256 columns have been identified. The rows are numbered from 1 – 65,536.

 

 

Column

ExcelColumn
Each Column can be selected by clicking on the corresponding letter on the spreadsheet. Numerous columns can be selected by clicking and dragging the cursor over the letters.

 

Row

ExcelRow
In turn, each Row can be selected by clicking on the corresponding number on the spreadsheet. Numerous rows can be selected by clicking and dragging the cursor over the numbers. A hint: When selecting many rows, start from the row furthest from the beginning and drag the mouse upward to control the “runaway mouse”.

 

Cell

ExcelCell
A Cell is the union of a column and row. The wide black line around the cell means that the cell has been selected using a single left click. A square box (AutoFill handle) appears on the lower right edge. We will talk more about the AutoFill in another lesson.

 

Tabs

ExcelTabs
The Sheet Tabs are located at the bottom of the spreadsheet and serve as navigation tools for the workbook. A sheet is a single spreadsheet. A workbook (excel file) may have multiple sheets. Three sheets are provided when you open the workbook but more can be added by selecting Insert > Worksheet from the standard toolbar. The current worksheet will have the white background and extra sheets will have the gray tab. To change from one worksheet to another, just click on the worksheet tab you want to view. Sheet names can be easily changed by double clicking on the text “Sheet1” and typing the new name when the original letters are highlighted with a black background.
The arrows serve as navigation tools also. The vertical line with the left pointing arrow ExcelLVAwill take you to the leftmost sheet in the workbook. Correspondingly, the right pointing arrow with the horizontal line ExcelRVAbrings up the rightmost worksheet. The individual arrows move to the previous ExcelLAor next ExcelRAworksheet.

 

Cursor

 ExcelPlusThe wide white Plus Symbol serves as a selecting cursor. With the cursor in this state, you can select a cell using a left click. The cell then becomes highlighted with the wide black rectangle.ExcelIBeam
The blinking I Beam or Insertion Point is visible when the cell is in the Ready state and you have either double clicked into the cell or have put data into the cell. Even though the I Beam is common in other programs, the difference is that an extra action (double clicking or typing) is necessary for this to appear.

 

Nomenclature

ExcelNomenclature
The cell is named by listing the column letter first followed by the row number. In this example; C1 refers to the cell in the third column of the first row. Remember that the formula bar displays the name of the highlighted cell. If more than one cell is selected, the initial cell will be displayed followed by a colon and then the name of the last cell (e.g., A1:D1).

 

Entry Bar

ExcelDeleteAccept
There are two ways to activate the enter or edit mode of the cell. You can double click in the cell or single click in the Formula Box located in the formula bar. When you are in this enter or edit mode, additional icons appear next to the entry bar. The ExcelXis used to delete content in the cell, the ExcelCheckis used to accept the entry you have made and the ExcelFXis used to insert a formula. Note that the black line surrounding the cell becomes thinner when the cell has been activated.

 

Selected Cell(s)

ExcelSelCells
The wide black line surrounding the cell or selection of cells indicates the Selected Cells. Any changes made in the program will occur in those cells. Cells need not be adjacent to one another to be selected. This will be covered in later lessons.

 

 [back to top]

Lesson 4: Spreadsheet Cells

For this next lesson you will be working with the demoXP file. Please make sure you have the file open. You will be exploring some of the elements in an existing spreadsheet. Therefore for many of the steps in these activities there will be an accompanying explanation.

Info
Remember – there are two levels when clicking in the cell – single click and double click

Activity 1: Values and Formulas

  1. Single click on cell B13 (You should see the data next to fx displayed like the image below.)

 
ExcelFormula
Result: Cell B13 shows the value of 24430 but the formula bar displays the underlying formula of one-half of the sum of cells B4 through B12.

 

Adding Values

When keying in numbers, the keypad at the right hand side of the desktop keyboard is helpful. Make sure the Num Lock key has been selected.

After entering data in the cell, you can press Enter, TAB or any arrow key to move to adjacent cells. This will confirm the cell’s data entry.

Edit mode allows you to edit a cell’s content. There are two ways to accomplish this task.

  • Double click on the cell and enter your data.
  • Click on the cell and then click on the formula bar to make changes. The enter, cancel and function arguments will appear allowing you to accept, cancel your entry or add a formula from the formula bar

To begin adding values:

  1. Click on cell C4
  2. Key in 15000. Press the Enter key or down arrow key to move to cell C5 Key in the remaining values in the rest of this column… (Remember to press the enter key or down arrow after each value has been entered.)
    5000
    0
    0
    160
    1500
    1000
    2500
    2500
  3. Click on File, then Save. Remember to periodically save your file.

 

 

Info
Remember – if you single click on a cell to change data, you may overwrite current data by mistake – always double click on the cell or use the formula bar.

Activity 2: Copying and Pasting Formulas

In this activity you will learn how to copy a formula from one cell and paste it into another.

  1. Click on cell B13
  2. In the Edit menu, select Copy (You should see a dotted line (marquee) around cell B13.)
  3. Click on cell C13
  4. In the Edit menu, select Paste
  5. Press Enter or Esc to stop the copying of subsequent cells (You will no longer see the dotted line around cell B13.) The number 13830 should appear in cell C13.

ExcelMarquee


Notice the marquee around cell B13 reminding you that the content of this cell has been copied.

 

 

Info
The formula box for the row and column labels is not case sensitive so typing b4 will result in B4 in the cell.

Activity 3: Entering Formulas

In this activity you will enter a formula that will add the columns B4 and C4

  1. Double click on cell D4 and type =sum(b4:c4)
  2. Press Enter to confirm the entry

Note: Using the sum function is more flexible than simply adding the two columns together since the sum function will allow you to easily add additional columns if you were to include a Phase 3 to the formula.
Example: [=(B13+C13+D13)] vs. [=SUM(B13:D13)]
When using the sum formula, the colon (:) is used to select the adjacent cell and the comma (,) is used to select non-adjacent cells. This formula [=SUM(B4:C4,B6:C6)] would give the sum of the adjacent cells, B4 & C4 added to the sum of adjacent cells B6 & C6.

Result: The value displayed in the cell D4 should be 30000 with the cursor in a ready position in cell D5.

 

 

Activity 4: Copying Formulas into Multiple Locations

In this activity you will learn how to copy and paste a formula into multiple cell locations. You will copy and paste the formula from D4 into cells D5 through D13.

  1. Select cells D4 through D13 by either clicking on D4 and dragging down to D13 or clicking on D4 and holding down the Shift key and then clicking on D13.
  2. In the Edit menu, select Fill>Down

ExcelFillDown

Result: The formula and values should be displayed in cells D5 through D13. Cell D13 should display the number 38260.

Additional Information: Fill Down can be used with adjacent cells and does not require the use of the clipboard which uses copy and paste. The Fill Down action provides a Relative Reference to the cells. This means that the formula will remain the same but the cell letters and numbers will reflect the currently selected rows and columns. If you want to learn more about Relative vs. Absolute Reference in cells, check the help menu.

 

Alternative Method: An alternate way to complete the Fill Down is to select the cell to be copied, then click and hold the small black box or handle on the edge of the cell, then drag until you have selected all of the cells you want filled.

ExcelFillBox

 

 

Activity 5: Entering Additional Formulas

In this activity you will learn how to add additional formulas to the spreadsheet. You will enter a sum formula to total Phase 1, Phase 2, and the Grand Total.

  1. Click on B14 and enter =sum(
  2. Drag from B4 through B13 and key in a right parenthesis and press Enter

ExcelSum

Result: The value of 73290 should be displayed in cell B14.

 

An alternate way to include the Sum formula is to click on cell B14 and then click on the Sigma symbol for summation and select and drag through B4 through B13. If you have double clicked on the cell, you will need to double click on the Sigma symbol to activate the formula.


ExcelSumSym

 

Now you are ready to add the totals for Phase 2 and the Grand Total.

  1. Copy and paste the formula from B14 into C14. Remember to click Enter or Esc to stop copying.
  2. In cell C14, enter the formula to sum column D

The spreadsheet calculations are complete.

ExcelComp

 

 


Go Back to Tutorials
http://edutech.msu.edu/online/

Wrap Up:

Evolution of the Spreadsheet:
The first electronic spreadsheet, known as VisiCalc, was developed by Dan Bricklin and Bob Frankston. While an MBA student at Harvard, Dan used this spreadsheet concept to crunch numbers in a case study around Pepsi-Cola’s marketing campaigns. This software soon became one of two “killer apps”, (along with word processing) that influenced personal computer growth. Today the electronic spreadsheet is used in numerous fields for asking “what if” questions and providing immediate answers by using formulas to perform calculations. To read more about the development of electronic spreadsheets, click here or visit Dan Bricklin’s Web site www.bricklin.com.

Workbook:
As spreadsheets became larger, they became more difficult to manage. The concept of a workbook was developed to manage this collection of spreadsheets. In this lesson, you have opened a sheet within a workbook in Excel. The default number of sheets included in a workbook is three but you can add as many sheets as needed depending on the memory available on your computer. For more advanced tips, check out the tutorial web site created by School of Information, University of Texas at Austin.

In the next section, Excel Part 2, we will begin with lessons on how to format a spreadsheet.

810 4 4 Excel Tut Part2

Introduction to Excel Part Two: Formatting an Existing Spreadsheet

Purpose

Upon completion of this tutorial you will have learned how to format the following:

  • Labels
  • Title
  • Rows and columns
  • Values
  • AutoFit Selection

Software Needed

Microsoft Office Excel

Prerequisite Knowledge and Skills

Before beginning this tutorial please make sure that you have completed Excel Introduction – Part 1.

Overview

Lesson 1: Formatting

All of the activities in this lesson will focus on formatting the spreadsheet. In this lesson you will learn how to format labels, titles, and values. You will also use functions like AutoFit that will automatically format the width of columns or rows.  

 

 

 

 

 

Info
Remember to save your file often. Click on the Save icon, press Ctrl S, or use the File menu to select Save.

Activity 1: Spreadsheet Labels

Purpose: To improve the appearance and readability of the spreadsheet

  1. Select Row 3 by clicking on the row header. Remember that the row header is the number at the beginning of the row.
  2. Click on the Bold button ExcelBold
  3. Click on the Centering icon ExcelCenter

ExcelBoldCenter

Result: Column titles are bold and centered

  1. Select Column A by clicking in the column header
  2. Click on the Bold button
  3. Click on the Align Right button ExcelRtAlign

ExcelBRtAlign

Result: Categories are bold and right aligned

 

Info
The Merge and Center icon is a toggle button so it can be turned on and off.

Activity 2: Spreadsheet Title

  1. Select the title cell – B1
  2. Click on the arrow in the Font Size box, select 16 (See image below)
  3. Click on the Bold button
  4. Select cells A1 through D1
  5. Click on the Merge and Center icon ExcelCenter

ExcelFontSz

ExcelMergeCenter

Result: Title is reformatted

 

Info
An alternate way to use the format menu is by right clicking in the cell and selecting Format Cells

 

Info
Once the initial formatting of the cells is completed, using the icons on the toolbar works well for individual cell formatting changes.

Activity 3: Formatting Values

Purpose: To make values easier to read

  1. Select the box of cells — B4 through D15
  2. In the Format menu, select Cells
  3. Click on Accounting from the Category menu. Then on the right side of the Format Cells window make sure that Decimal Places is set to 2 and Symbol is set to None
  4. Click on OK

ExcelFormatCells

Result: All values are formatted with commas and two decimal figures

ExcelAccount

 
  1. Select cell D15
  2. In the Format menu, select Cells
  3. Set Symbol to $

ExcelDollarSign

Result: Grand Total is formatted with a dollar sign

 

Activity 4: AutoFit Columns

  1. Select the entire worksheet by clicking the Select All Button (box) in the upper left corner where the rows and columns intersect. The box is blank. (See the image below)
  2. Select Format, select Column, and then AutoFit Selection

ExcelAutoFit

Result: All of the columns are spaced to accommodate the widest content in each column.

Make sure you save your file.

 

Activity 5: Page Set Up

Purpose: To format the spreadsheet for printing
  1. In the File menu, choose Page Setup…
  2. Click on the Page tab, make sure the Orientation is set at Portrait
  3. Select the Margins tab and under the section Center on Page, check the box next to Horizontally
  4. Select the Sheet tab and under Print, check Gridlines (this will add lines to the worksheet when printed)
  5. Then press OK
  6. In the File menu choose Print Preview… to see the placement of your worksheet on the page.
  7. Save your Excel Document.

ExcelPageSetUp

Lesson 2: Creating a Chart

Excel is not only used to perform computations or to sort and organize data. It can also be used to create graphical representations of information.

 

Info
Placing the date in a Header or Footer is helpful on a spreadsheet because of the many times the data is changed and printed. Also, placing the chart name in the Header is helpful when there are numerous pages in a spreadsheet.

Activity 1: Creating a Chart

Purpose: To produce a graphic display of project budget totals
*Non-adjacent cells will be selected to create this chart.

  1. Select cells A4 through A13
  2. With the previous cells selected, hold down the Ctrl key, click and drag from D4 through D13
  3. Click on the Chart icon ExcelChartIcon(if the Office Assistant appears, click on [No, don’t provide help now])

ExcelChart
  1. The Chart Wizard appears displaying the first window in a series of four windows. The window is titled Step 1 of 4 – Chart Type
  2. Under the heading Chart Type,  keep it set to the first option Column.
  3. Under the heading Chart sub-type select the 3-D chart. (See image below – it is the first option in the second row.)

ExcelChart3D
  1. Once this sub-type is selected, you can click on the Press and Hold to view Sample button (See image above) to see if the data is displayed properly. It should look like the image below.
  2. If this chart is acceptable, click on Next

ExcelChartPrev
  1. Behind the window Step 2 of 4 – Chart Source Data notice the highlighted selections on the spreadsheet. This refers to the Data range shown in the Wizard.
  2. Accept the default settings by clicking Next

ExcelStep2High.

  1. The new window Step 3 of 4 – Chart Options. Key in the title Budget Category Totals
  2. Click on the Legend tab, and uncheck Show Legend
  3. Click on Next

ExcelChartStep3Title

  1. Click on As new sheet and key in the name Totals Chart to label the sheet
  2. Click on Finish

ExcelChart4

Result: Column Chart is created in a separate sheet.

  1. Double click on the Sheet1 tab that is adjacent to the Totals Chart tab
  2. Rename your data sheet to Budget Data
  3. To change the orientation of the text for the Budget Categories, double click on one of the labels.

ExcelChartTab

Notice: The chart worksheet tab includes the name Totals Chart that you provided while using the chart wizard. Sheet 1 corresponds to the data we have in the spreadsheet.

A Format Axis dialogue box appears. In the Orientation field, select the Diamond connected to text and drag it to the top of the circle so that the text orientation is 90 degrees

ExcelFormatAxis

  1. In the File menu, select Print Preview
  2. If the chart appears the way you want it to look when printed, click on Print
  3. If you would like to make changes to the appearance or add a Header or Footer, click on Setup and select the options you would like to change.

ExcelChartText

Result: Chart with viewable text representing each category

 

 

 


Go Back to Tutorials
http://edutech.msu.edu/online/

Wrap Up:

You have just completed the Introduction to Excel – Part II which included:

  1. General purposes of electronic spreadsheets
  2. Modify and format an existing Excel spreadsheet
  3. Output an excel spreadsheet to a flash drive, printer, and chart

In the next section, you will create your own spreadsheet and learn many fascinating ways to manipulate data to help with student understanding of various concepts.

 

810 5 2 Excel Tut3

Introduction to Excel Part Three: Creating a New Spreadsheet & Performing Data Computations

Purpose

Part 3 is a continuation of an introduction to Excel. . In this tutorial you will learn how to:

  1. Open a new file
  2. Enter column labels & data
  3. Format data
  4. Compute data using a formula
  5. Sort data
  6. Create a header for a spreadsheet
  7. Filter data
  8. Create a custom filter with a single criterion
  9. Create a custom filter with multiple criteria
  10. Subtotal data
  11. Create a chart with subtotals

Software Needed

Microsoft Excel

Prerequisite Knowledge and Skills

Before beginning this tutorial please make sure that you have completed Excel Introduction – Parts 1 & 2.

Overview

Excel is a spreadsheet that has many functions, one of which is the ability to ask questions of or query the data that is presented. In these lessons you will have the opportunity take a blank workbook and turn it into a vehicle for exploring information by sorting and filtering data according to specific criteria or characteristics. This formatted and queried data can then be subtotaled, and charted allowing further comparison and contrast of data.
When these lessons have been completed, you should have the basic knowledge, comfort level, and skill set for using a spreadsheet for your own classroom subject matter application.

Lesson 1: Creating a New Spreadsheet

In the previous lessons, the spreadsheet was started for you. In this lesson you will start with a blank spreadsheet and enter, format and manipulate data to allow for more thoughtful, in-depth interpretation of the raw data.

Activity 1: Open New File

  1. From the File menu, choose New
  2. With the Workbook selected, click on OK

Result: Blank Spreadsheet

 

Info
Here’s a shortcut…An easy way to view data from two sources, is by minimizing both screens, placing them beside one another and, re-sizing them so there is no overlapping of the documents. You can move easily from one document to the other with no lengthy re-loading of each document screen.

 

Activity 2: Entering Column Labels & Data

  1. Key in the following labels in Row 1
    Country
    Continent
    Population
    Total Area
    Life Exp
    Pop Growth

ExcelColLabels
  1. Starting with Row 2, key in the following:
    United States
    North America
    298444215
    9631420
    77.85
    .0091     (The data given .91 is in % format so we will need to enter the raw data and format the cells to have the correct data for calculations)
  2. Press Enter and then the Home key to start at cell A3

Data for this spreadsheet is available at The World Factbook Web site… https://www.cia.gov/cia/publications/factbook/
Just select the country in the pull down menu.

  1. In Row 3, key in the following
    France
    Europe
    60876136
    547030
    79.73
    .0035
  2. Since some of the data may not be visible due to the default column width it may appear as a series of pound symbols (##########). Use the Column AutoFit Selection option in the Format menu to automatically re-size the column widths. Remember to first use the Select All Button to make sure all data is included.
  3. Save Sheet 1 as Countries. Remember to double click on the sheet tab and type in Countries.

ExcelColumnAutoFit

  1. Using the CIA Web site provided, fill in the data on the following countries (Remember to use the drop down menu to go to the country page and then scroll down for the data):
    Argentina
    China
    Chad
    Denmark
    Egypt
    Chile
    Japan
    Canada
    Algeria

  • An alternate way to fill in the spreadsheet is to use the Data Form. A single record is visible, allowing for ease in entering the data.
  • Highlight the data, in cells A1 through F13. In the Data menu, select Form (Data> Form)
    This box appears with the header row as the field names.

ExcelForm

  • The first record is visible in the individual field locations. The current record being viewed along with the total number of records is provided. You can select Find Next to go to the next record, Find Prev to take you back a record, Delete the current record, add a New blank record, search using specific Criteria, or Close the form.
  • Make yourself familiar with this form and then add the remaining countries. Pressing Enter is another method of taking you to a new empty field. Tab will take you to the next field in the record. Select Close when finished.

 

Info
Remember that text by default is center justified and numbers are right justified.

 

Info
Freeze pane is a helpful viewing tool when you have data that goes beyond the viewing area of your screen. The row and column headers remain in place and the data inside is allowed to move so you know the field and record you are viewing. To use this tool, place the courser in cell B2 (the intersection point of the field and column names. Select Window in the tool bar, then select Freeze Panes. To remove this feature, select Unfreeze Panes.

Activity 3: Formatting the Data

  1. Format the population and total area data (Format> Cells) as accounting with 0 decimal places. Remember to highlight the data first, then apply the formatting. (Notice that the Sample in the dialogue box gives you a preview of how the data will be displayed.
  2. Format Life Exp data as numbers with 2 decimal places.
  3. If an individual column does not properly display the data, (remember the series of pound symbols (##########)), another way to re-size the column is by mousing over the right edge of the column until the double sided arrow appears. Double click and the individual column will adjust the width to fit the data.
  4. Format Population Growth as % with the decimal set at 2 .

ExcelColSize

Your spreadsheet should look like this…

ExcelCountries

 [back to top]

Lesson 2: Data Computations

In this lesson, the country data will be computed, sorted, filtered various ways, subtotaled, and charted to better understand, interpret, and draw conclusions about the raw data presented for each country.

Info
When you copy the formula by highlighting the cells and dragging the fill down box, you are using a Relative Reference. The formulas are created relative to or in relationship to the rows you are selecting. Another method of referencing cells would to use an Absolute Reference which accesses data in a specific cell. The formula would include a $ in front of each column letter and each row number such as =$A$13.

 

Activity 1: Computing Data Using a Formula

Now that the data is entered into the spreadsheet, you can use it to make inquiries about the information on the countries by placing formulas in various cells.

  1. Add one more column by placing the title Pop. Density in cell G1.
  2. In cell G2, enter the formula to compute the density. The formula you should type in the formula box is =C2/D2. The result should be 30.98652.
  3. Copy the formula into the rest of column G (cells G3-G12) by highlighting cell G1 and dragging the fill down box (black) in the right hand corner down until cells all data cells are highlighted.

ExcelPopDensity

  1. Format the column so the numbers show two decimal places and the field name is completely visible.

Result: Data that is easily comparable to like items

 

 

Info
You can also complete a simple descending sort based on one column by selecting the sort icon ExcelSortDecSm

Activity 2: Sorting Data

  1. Single Click on a cell within the data set you want to sort.
  2. In the Data menu, select Sort…

If you see the message below, you need to select any cell containing information that is part of this list of data. You need to tell the software which list of information you intend to sort.

ExcelSortError

Another error message you may see would be the Sort Warning box if you select only a few cells but not all of them within your data set. Select Expand the selection button to include the full data set, not just the few cells that were selected.

ExcelSortWarn

  1. Notice that the range of cells to be sorted is highlighted.
  2. At the bottom of the Sort dialogue box, make sure My Data Range has Header Row selected .
  3. In the Sort By section, select Continent.
  4. In the first Then By section, select Population.
  5. It is fine to leave the default order of Ascending .
  6. Click on OK.

ExcelSort

Result: Data sorted by Continent. Within each Continent the data is sorted by Population

ExcelSortContPop

 

Activity 3: Creating a Header for the Spreadsheet

  1. In the File menu, select Page Setup… (File> Page Setup)
  2. Select the Header/Footer tab, click on Custom Header…
  3. In the Center section type Countries Sorted by Continent
  4. Click on OK to accept changes to the header
  5. Click on OK in the Page Setup dialogue box
  6. Double Click on the tab for Sheet 1 and change the name to Sort by Continent

Result: Sheet of sorted countries ready for printing

 

Info
Clear Filter – Column
If you were using filters from several columns, you could remove the filter from just one column by selecting All in the drop-down menu for that column.

Activity 4: Filtering Data

Once the data is sorted you can use the filter command on specific records dependent on criteria. The records that are extracted will depend on the criteria you select.
  1. In the Data menu, choose Filter then AutoFilter (Data> Filter> AutoFilter)

A series of Pop-down menu icons appear next to each field name in the header row.

ExcelFilterIcon

  1. Click on the pop-down icon for Continent and scroll to select Africa

ExcelFilterMenu

Result: Only Countries from the Continent of Africa appear (records 2, 3, & 4). Notice that rows 5-12 were not visible since these were filtered out from view.

ExcelFiltCont

  1. To Copy this filtered data to another sheet to preserve the original data, use the Select All button in the corner and Copy the data.
  2. Click on the Sheet2 tab and Paste the data into the sheet. The default cell selection is A1 so a paste command will work. Rename the sheet tab ” Filter by Continent“.
  3. Notice the pop-down icons are no longer present and the row numbers are back to consecutive numbering. Only the data has been copied, not the filtering command prompts.
  4. The Header/Footer content does not carry over from one sheet to the next so you will need to repeat the commands to add a Custom Header for Filter by Continent of Africa. To return to these directions, click here.

Clear Filter – All Fields

  1. Return to the original sheet that is now labeled Sort by Continent
  2. Clear the Filter command by selecting Filter, then Show All in the Data menu. (Data> Filter> Show All)

ExcelFilterShowAll

Result: All data is displayed

 

Activity 5: Custom Filter – Single Criterion

By creating a custom filter you can specify data ranges within a column.
To filter continents that have America as part of their name, you will use the “wild card” symbol of asterisk (*) in the filter criteria. The asterisk represents any series of characters that may be part of the filter criteria.

  1. With the filter icons still in place, Click on the drop-down icon for Continent
  2. Select Custom
  3. Under the heading Show row where: Continent, if the term equals is not already displayed, then use the drop-down menu to select equals.
  4. In the adjacent box, key in *America
  5. Press OK to complete the filter

ExcelFilterCustSing

Result: Countries in North and South America are displayed

ExcelFilterCustSingAm

  1. Copy this filtered data onto a new sheet, rename the sheet, and add a Header to the sheet

 

Activity 6: Custom Filter – Multiple Criteria

This section uses Boolean logic to combine terms. The words used to qualify the data are called operators. In reference to filtering within a column we will be talking about the Boolean operators and and or.

With this data set, countries with similar size can be compared in terms of population, life expectancy, population growth and population density. Filtering countries with a total land area of more than 756,950 and less than 9,596,960 will give us this data set for comparison and further manipulation.

  1. Return to the Sort by Continent worksheet and Clear the Filter using the same method as before (Data> Filter> Show All)
  2. Click on the drop-down icon for Total Area
  3. Select Custom
  4. Under the heading, Show row where: Total Area, use the drop-down menu to select is greater than
  5. In the adjacent drop-down menu, select 756,950
  6. Make sure the radio button for And is selected
  7. In the next set of drop-down menus, select is less than and then 9,596,960 as the criteria
  8. Press OK to complete the filter

ExcelFilterCustomCr

Notice that the filter icon arrow of the column where a filter has been applied has changed from black to blue.

ExcelFilterMult

Result: Only Countries with a Total Land Area greater than 756,950 and less than 9,596,960 are displayed.

Now additional comparisons can be made about the population density in relationship to life expectancy, and population growth for countries with similar land area.

  1. Copy this filtered data onto a new sheet, rename the sheet, and add a Header to the renamed sheet. A Workbook has a default of three Sheets. To add more, in the Insert menu, select Worksheet. A new Sheet appears on the Sheet Tab list to the left of the current worksheet. If you want to move this sheet to the rightmost position, just left click on the tab until you see the sheet icon appear. (It looks like the New document icon.) Drag the sheet name to the preferred location on the bar. An insertion arrow will appear to designate the new location. Release the mouse if the location is correct.

ExcelSheetTabMove

Result: Sheet Tab is listed in user defined order

Remove Filter – this command will remove the Filter drop-down menus from each column. This command is executed when you deselect AutoFilter. (If AutoFilter is on you will see a check next to the word in the Filter menu.

  1. Return to the original sheet that is now labeled Sort by Continent.
  2. Go to Data> Filter. Click on AutoFilter if there is a check next to the term. This command acts like a toggle switch (on or off). By clicking on AutoFilter you have turned the command off.  

ExcelFilterRem

Result: All data is visible and Filter icons have been removed

 

Activity 7: Subtotaling Data

While the Average function can work well to average data, you may want to average the data according to a specified field or category and view or hide varying amounts of data according to that category. The Subtotal command will compute this function within the data set.

  1. Make sure you are in the Worksheet labeled Sort by Continent because we will begin with data sorted by continent. Not all of the subtotal formatting holds when copied, so a new sheet should be selected first. Select All (button in left-hand corner) and copy the data from the sheet into a new Worksheet
  2. Format your new sheet
    1. Label the sheet tab Subtotals
    2. Re-size the fields
    3. Add a header
    4. Move the Subtotal tab to the last position (far right).
  3. Click on a cell with data. Go to Data> Subtotals.
  4. Under the header At Each Change in: use the drop-down menu to select Continent.
  5. Under the header Use Function:, use the drop-down menu to select Average.
  6. Under the header Add Subtotal to:, click on Life Exp to check  it. If any other box has a check mark, then click on the to de-select it.
  7. Click on OK.

ExcelSubtotalDialog

Result: Life Expectancy for each continent is averaged and displayed below the continent

ExcelSubtotLifelExp

 

With Subtotaling, a display appears on the left side of the worksheet which acts like the outline feature in Word. The + and – symbols will expand or collapse (toggle) the rows according to the level you select. Currently all of the data is visible along with the new Life Expectancy averages for each continent. When all data is visible, you will see Level 3, represented by dots. Level 2 can collapse the rows according to the subtotaling criteria; in this case it is countries. Level 1 can collapse all rows with only the field titles appearing and the Grand Average (average of all continents) displayed. You can also select the numbers at the top to collapse or expand all records at that level. The remaining activities will allow you to further explore the subtotaling feature.

ExcelSubtotLifExpOutL

Hiding the Detail
  1. Click on the small number 2 in the outline report on the top left of the page.

ExcelSubtotalOutline

Result: Subtotals of Life Expectancy for each continent are displayed along with the Grand Average

 

Info
The Horizontal Cylinder chart was chosen to better accommodate the lengthy data titles. Choosing different chart types and sub-types help to display the data in a manner that supports better comprehension.

 

Info
Try formatting the various parts of the chart to fit your own style and to better represent the data. Click on the data you want to change, then right click and select Format to bring up a dialogue box with options

Activity 8: Creating a Chart with Subtotals

In this activity you are going to create a bar chart that displays all of the Life Expectancy subtotals for each continent. Only the data that is visible will be included in the chart, not data from the hidden cells and rows.

  1. Highlight cells B1-B17, press Ctrl (to include the next set of cells) then select cells E1-E17
  2. Click on the Chart Icon ExcelChartIcon2to create a chart
  3. Step 1 of 4 — In Chart type, scroll down and choose Cylinder
  4. In the window, Chart sub-type, choose the horizontal cylinders with a description of Bar with a cylindrical shape. Click and hold on the button Press and Hold to View Sample to view a sample of the chart.
  5. If that chart type is suitable, click on next or choose another type of chart

ExcelSubtotAveChart

  1. Click on Next
  2. In the window Step 2 of 4 — Chart Source Data, leave the data range in columns and just select Next to go to the next step in the wizard
  3. In the window Step 3 of 4 — Chart Options, under the Titles tab, in the Chart Title box, remove the period and complete the spelling of Expectancy
  4. Click on the Legend tab. De-select the Show legend box to remove the legend (See image below)
  5. Select Next

ExcelSubtotAveChart2

  1. In the window Step 4 of 4 — Chart Location, you will select the radio button next to As object in:. Use the drop-down menu next to As object in:  to select the Subtotal sheet.
  2. Press Finish

ExcelSubtotChartComp

Result: A Chart of Life Expectancy subtotals for each continent.

Notice in the image above that the cells with chart data and text have a colored outline. The colored outline corresponds to the data and text from the spreadsheet; purple highlights the Continents displayed, blue highlights the data used (subtotals) for life expectancy and green highlights the data series title. Also notice the formula bar with the chart series that references the absolute values of the subtotal cells. (the ! in the formula refers to the worksheet named Subtotal)

Copy Sheet

There is a difference between copying a sheet and copying the data within a sheet. As you saw earlier, copying the data does not retain all of the filtering or subtotaling features of the worksheet. A way to allow these features is by copying the worksheet.

  1. Select Edit, then Move or Copy Sheet
  2. A new sheet with the subtotal features is created with the name of Subtotal (2). Change the name of the sheet tab to Subtotal Detail and move the worksheet to the right of the Subtotal worksheet.

Show Detail

  1. Click on the plus sign next to the Africa Average

ExcelShowDetailAfrica

Result: The individual fields from the African countries are displayed with only the averages from the other continents displayed

Notice that the chart also changed to include all Life Expectancy for countries in Africa because it was referencing a specific cell range. Click on the chart and using the keyboard select Delete since it is no longer referencing the data initially specified.

Removing Subtotals

  1. In the Data menu, select Subtotals… (bottom left of page)
  2. Click on Remove All

Result: All data with no summaries displayed

This is not a toggle button. The chart data will change and all subtotals will need to be re-selected if removed.

Wrap Up:

Excel is used to explore and answer questions by way of computing data, sorting, filtering, and subtotaling various data sets. You should now be able to alter this data set or create a new spreadsheet for use in your own educational setting with specific learning objectives relevant to your subject area. You will need these skills to complete the Excel assignment for this class. Remember that Excel is a very powerful software tool that has great potential for displaying and analyzing facts and figures.

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *