810 4 3 Excel Tut Part1
Introduction to Excel Part One
Purpose
Upon completion of this session you will be able to:
- Describe the general purposes and uses of an electronic spreadsheet
- Modify and format an existing Excel spreadsheet
- 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
- Electronic Spreadsheet
- Common elements of Spreadsheet window
- Unique elements of Spreadsheet window
- Spreadsheet cells
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.
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.
- Turn on your computer.
- Click on the Start button
then click on All Programs> Microsoft Office> Microsoft Excel
. (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. )
Elements of importance:
- Columns
- Rows
- Cells
We will discuss these elements further in this tutorial.
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:
- Right click here
- 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. - Click on Save and the demoXP.xls spreadsheet will be saved to your folder.
To Open the File:
- In the main Excel menu bar click once on the File menu, scroll down and click on Open. ( File> Open )
- Navigate through your folder directory and click on the demoXP file. (You should have added your initials to the file name.)
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 |
|
|
|
Menu Bar |
|
|
|
Standard Toolbar |
![]() 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. ![]() |
|
|
Formatting Toolbar |
|
|
|
Scroll bar |
|
|
|
Status Bar |
|
|
Lesson 3: Spreadsheet Window – Unique Elements
These elements are unique to Excel and other electronic spreadsheets.
|
|
Formula Bar |
|
|
|
Column & Row Header |
|
|
|
Column |
|
|
|
Row |
|
|
|
Cell |
|
|
|
Tabs |
|
|
|
Cursor |
![]() ![]() 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 |
|
|
|
Entry Bar |
|
|
|
Selected Cell(s) |
|
|
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.
Remember – there are two levels when clicking in the cell – single click and double click
Activity 1: Values and Formulas
|
![]() 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.
To begin adding values:
|
|
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.
|
|
|
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
|
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. 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.
|
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. |
|
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.
|
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.
|
|
Now you are ready to add the totals for Phase 2 and the Grand Total.
The spreadsheet calculations are complete.
|
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.
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 |
Result: Column titles are bold and centered |
Result: Categories are bold and right aligned |
|
The Merge and Center icon is a toggle button so it can be turned on and off.
Activity 2: Spreadsheet Title
Result: Title is reformatted |
|
An alternate way to use the format menu is by right clicking in the cell and selecting Format Cells
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 |
Result: All values are formatted with commas and two decimal figures
|
Result: Grand Total is formatted with a dollar sign |
|
Activity 4: AutoFit Columns
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 |
|
|
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.
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.
|
|
|
|
|
|
|
Result: Column Chart is created in a separate sheet. |
|
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
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:
- General purposes of electronic spreadsheets
- Modify and format an existing Excel spreadsheet
- 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:
- Open a new file
- Enter column labels & data
- Format data
- Compute data using a formula
- Sort data
- Create a header for a spreadsheet
- Filter data
- Create a custom filter with a single criterion
- Create a custom filter with multiple criteria
- Subtotal data
- 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
Result: Blank Spreadsheet |
|
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
|
Data for this spreadsheet is available at The World Factbook Web site… https://www.cia.gov/cia/publications/factbook/
|
|
|
|
Remember that text by default is center justified and numbers are right justified.
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
Your spreadsheet should look like this… |
|
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.
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.
Result: Data that is easily comparable to like items |
|
You can also complete a simple descending sort based on one column by selecting the sort icon
Activity 2: Sorting Data
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. 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.
Result: Data sorted by Continent. Within each Continent the data is sorted by Population |
|
Activity 3: Creating a Header for the Spreadsheet
Result: Sheet of sorted countries ready for printing |
|
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. |
A series of Pop-down menu icons appear next to each field name in the header row.
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.
|
|
Clear Filter – All Fields
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.
Result: Countries in North and South America are displayed
|
|
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. |
Notice that the filter icon arrow of the column where a filter has been applied has changed from black to blue. 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.
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.
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. |
Result: Life Expectancy for each continent is averaged and displayed below the continent
|
|
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.
|
|
Hiding the Detail |
Result: Subtotals of Life Expectancy for each continent are displayed along with the Grand Average |
|
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.
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.
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.
|
|
Show Detail |
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 |
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.