I. Basics of Excel

What is Excel?

 

Excel was developed in the 1980's by Microsoft to be a simple electronic calculating spreadsheet. Today it is a high-powered, multi-tasking electronic spreadsheet program developed to handle most any kind of calculation or data processing.

An electronic spreadsheet is a software program that is designed to calculate and perform a variety of mathematical equations instantly and simultaneously.

The uses for Excel are wide-ranging; it can be used for solving financial and statistical problems, performing various “what-if” analyses, and creating effective line graphs or bar charts to enhance a presentation.

Excel consists of workbooks and within those, worksheets. The idea of the workbook is similar to an 88¢ single subject notebook in that you have hundreds of sheets of paper all spirally bound together. You don't have to tear each sheet out and place it into another binder, because it will always be there whenever you open it and close it. This is the same idea for the Excel workbook. You can save hundreds of sheets (all with different information) bound together just by saving the one Excel Workbook file.

All Excel files end with the extension ".xls." For our study, we'll be using a file called "snowwhite.xls".


The Components of Excel

 

The foundation of succeeding in using Excel like any other program lies in understanding the workbook environment, the tell-tale signs of the cursor, and the document window. Review the handouts: Workbooks, Cursors, and Document Windows. After studying these, take the mini-tests to determine that you're ready to move on with Excel 1: Introduction.

1. Menus

The menus in Excel contain all the functions, tasks, and possibilities that-may-be. First, using your mouse cursor, click on File in the Menu Bar. You'll notice another menu window pops up and from there you may move your mouse cursor down and select a specific task by left-clicking (normal click) on top of it. Move your mouse cursor across all of the Menu Bar menus:

File, Edit, View, Insert, Format, Tools, Data, Window, Help.

2. Cells

In Excel, all spreadsheets are made up of cells.

Cells are identified by their column and row—or “cell reference” (B10, where B is the column and 10 is the row number)—indicated in the Refernce Area of Excel. Cells can contain a number, a text label, or a formula.

II. Using Excel

A. Creating workbooks/spreadsheets

 

Open the Excel Spreadsheet “snowwhite.xls”

If you haven't already downloaded the necessary Excel files, open a browser and download the "2001_Excel_1.exe" from http://www.csuchico.edu/stcp/workshops/download.

Save the file to your Desktop. On the Desktop, double-click "2001_Excel_1.exe" to install the files.

Now, you are ready to begin using Excel!

1. Go to File in the Menu Bar and pull-down to Open. An Open window pops-up. At the top in the "Look in:" field is the current directory (also called a folder) that you are in.

2. Click on the pulldown menu next to the current directory name (also called a folder).
From there, choose the C: drive.

3. Now, double-click on the "My Documents" folder and then double-click on the "STCP_Tutorials" folder.
Inside is where the file "snowwhite.xls" is located.

4. When "snowwhite.xls" is found, double-click it to open it.

Working with Excel

 

1. Editing cells

Change Grumpy's THIS YEAR income to "1000." Do this by demonstrating the two different ways to edit cells:

a. Clicking on the cell and typing directly over it.



b.) Clicking on the cell and using the Formula Bar on top.



Press Enter to submit your changes.

2. Undo

You can undo changes or mistakes that have been made in three different ways:

a. Click the Undo button in the Toolbar



b. Go to Edit in the Menu Bar and pull-down to Undo



c. Hold down the Ctrl key and press 'Z' simultaneously



Go ahead and Undo the insertion of "1000."

3. Insertion

Add a 4th Dwarf, Doc, to the spreadsheet. Insert him between Bashful and Grumpy.

a. Insert Doc by clicking on the number of row 4. This should highlight all the cells to the right of the number 4.



b. In the Menu Bar at the top, select Insert. Choose Rows from the pull-down menu. Grumpy should move down a line and now you have a blank row above his.



c. Type in Doc's name, 444 as his LAST YEAR total and 400 as his THIS YEAR total.

Add a "% Total" column. Insert it between the "Last Year" and "Change" columns.

d. Insert the % TOTAL column by clicking on the (column) letter D to highlight all the cells beneath it.

e. Right click on it and choose Insert from the pop-up menu. This should move the CHANGE column to the right, inserting a blank column to the left of it.



f. Type in "% TOTAL" at the top of the new column.

g. When finished the spreadsheet should look like this:



C. Formatting

 

1. Bold, Underline, & Alignment

Bold the names of the Dwarves and the column headers

a. Select all of column A (Remember to click on the column letter A.)

b. Press the Bold button in the Toolbar



Let's right-align this while it's still highlighted.

c. Click on the align right justification button in the Toolbar.



Now highlight the column headers and Bold these by navigating the Menu Bar.

d. Go to Format in the Menu Bar and pull-down to Cells…

e. From here choose the Font tab and select "Bold" under Font style.



Now select only the headers of row 1 (from LAST YEAR to CHANGE) and underline them all.

f. While highlighted, click the underline button in the Toolbar.



Center these headers while they're still highlighted.

g. Click on the center alignment button in the Toolbar.



2. Font Type, Size, & Color

Add the label, TOTALS, to cell A8.

a. To insert the label, just click on A8 and type in "TOTALS".

Add the title, "Four Dwarves' Income" at the very top of the spreadsheet.

b. Begin by selecting row 1 (to highlight all the cells in that row).

c. Right-click on row 1 and choose Insert from the pop-up window. This moves all the dwarves' names and information down one row.

d. Type the title, "Four Dwarves' Income" into cell C1. Click the green checkmark when finished.

e. When finished, the spreadsheet should look like this:



Let's make some modifications to dress-up this spreadsheet. We can change the font, color, and size of the title. We will also use borders to insert a line that goes across the cell.

Change the font size and font type of the title from 10pt Arial to 12pt Courier.

f. To change the font type or size, first click on the cell with the text in it; this is C1: Four Dwarves' Income.

You can easily change the font size from the Toolbar.

g. Pull-down the Font Size box and scroll until you find "12".



h. Now, click on "12". This applies it to the currently selected cell, C1: Four Dwarves' Income.

Now change the font type.

i. Pull-down the Font box and scroll down through the list until you find Courier.



j. Click on the Courier font name. This applies it to the selected cell, in our case, the title.

We can also make the title green.

k. Single-click the small triangle to the right of the button in the Toolbar. You can select any color that is shown there by simply clicking on it.



Finally, we should make the title bolded.

l. Click the Bold button in the Toolbar.

3. Lines (Borders & Shading)

Insert a line above TOTALS by using the Borders button in the Toolbar.

a. Highlight cells B7:E7.

b. In the Toolbar, click the small triangle to the right of the Borders button. From this list, select the Bottom Border. This applies the regular Bottom Border to the cells just above the TOTALS row.



4. Selection

Select all the cells that will contain numbers of currency. These cells would be LAST YEAR and THIS YEAR (B3:C6), CHANGE (E3:E6), and TOTALS (B8:E8).

Highlight multiple adjacent cells of LAST YEAR and THIS YEAR (the range of B3:C6).

a. Click on the cell B3 to start with highlighting. While still holding the mouse button down, drag the mouse toward cell C6.

Now add to our selection by adding the cells in the CHANGE column (E3:E6)

b. DO NOT just click on the next set of cells! This will deselect your previous range.

c. Hold down the Ctrl key on the keyboard.

d. While still holding the Ctrl key, click normally with your mouse to highlight the cells of the CHANGE column (E3:E6).

Lastly, highlight the TOTALS cells as well.

e. Hold down the Ctrl key, click, and drag with your mouse to select the TOTALS range from B8:E8.

When finished, it should look like this:



5. Number Formatting

Now that all the cells for currency are actively highlighted, let's set the formatting to have dollar signs ($) and decimal places.

a. Click on the Currency button in the Toolbar. This automatically sets the cells to be in the Accounting mode so that the dollar sign is always aligned to the left, two decimal places are present, and that the currency is US Dollars.

6. Tidying up! (Merge & Center, Alignment Tweaks, Autofit Rows & Columns)

What have we done so far? The currency has been set, the column headings are centered and bolded. Our dwarves' names are also bolded and aligned to the right, and we have a unique title across the top of our spreadsheet. But before we go onto Formulas, Headers and Footers, Printing, etc. we should tidy up and customize our spreadsheet a little more.

Begin with merging the title's cells and centering them for a more professional look.

a. Highlight all 5 cells from A1:E1.



Merge and Center all of those cells so that the title is perfectly centered and spans across all five columns.

b. While these cells are still selected, click the Merge and Center button in the Toolbar. Viola! The title is now centered and the former 5 cells now act like one single cell.

Now, we will add your name to the spreadsheet in a unique way.

c. Select all the cells from F1:F8.

d. Go to Format in the Menu Bar and pull-down to Cells…

e. From here, choose the Alignment tab.

Below are a myriad of choices that allow you to tweak with how words are displayed.



f. Change the Horizontal pull-down list to "Center."

g. Change the Vertical pull-down list to "Center."

h. Under Text Control, check "Shrink to Fit" and "Merge Cells."

i. In the Orientation area, change the 0 degrees to -90 degrees.

j. Click on OK.

We can now add your name to this cell.

k. The cell is still highlighted, so just enter "By: Your-Name-Here" and press the Enter key when you're finished

What else could be cleaned up?

We should edit our column widths so that it is easier to read the column headers and any information that may be contained within the cells.

Note: sometimes cells will show only" ###". If this happens, it is because the width of the cell is too narrow to display the correct information.

Select the entire spreadsheet and adjust the columns to accommodate the biggest cells of information.

l. Click the gray box in the upper left corner of the current spreadsheet. This selects the entire spreadsheet easily.



m. Go to Format in the Menu Bar and pull-down to Columns.



n. Another pop-up window appears and from here, you may select Autofit Selection.

o. So far, your spreadsheet should look like this:



D. Formulas

 

Formulas always start with an equal (=) sign. Formulas can be created by manually typing them into the cell or by inserting a formula from the pre-existing list. Cells may contain any of the listed items below:

Item	Example	Formulas
Numbers	12	=12
Math operators	+ - * /	=4+8 or =5*7
Parentheses	( )	=(4+8)/2 is not =4+8/2
Cell references	B2, C13	=B2+C13 or =(D4+E2)/F1
Functions	sum, average	=sum(D3:D12)+5


1. Summation

Formulas can be created by manually typing them into the cell directly or by inserting a formula from the pre-existing list. If entering formulas through typing, it should be known that there are different ways to write formulas.

a. If we were to type in: =SUM(B3+B4+B5+B6), it would be the same as typing in the range instead: =SUM (B3:B6).

Using a predefined formula, we only need to click on the cells we need; Excel takes care of defining the selecting ranges.

Total LAST YEAR's income for the four dwarves'.

b. Click cell B8. It should now be highlighted.

c. Click the AutoSum button in the Toolbar above.



Notice that a moving dotted border is circling around an estimated range of cells.

d. Currently, there is an extra-unneeded selected cell. Re-select the correct range of cells. Click on B3 and drag your mouse down to B6.



e. Notice how the range in the summation formula changes as you click and drag your mouse over new selections.

f. Hit Enter on the keyboard or click the Formula Check when finished. This will add the selected cells together.

g. Now, find the total of THIS YEAR's income. Refer back to these steps if needed.

h. When finished, your spreadsheet should look like this:



E. Additional Items: Properties, Headers, Footers, Titles, etc.

 

1. Document Propterites

Additional settings about your document can reveal who the author is, whether or not it should be read-only, or if it was last changed by someone. These items are typically used by the Header and Footers to automatically update the document so that you won't have to do it every time.

Let's set the Summary Properties to reflect the title and the author of this modified document.

a. Click on the File menu and pull-down to Properties.

b. Within properties, you see several tabs across the top. Click the Summary tab.



c. Change the Title to read: "Four Dwarves' Income"

d. Change the Author to read "your name here."

e. Click OK.

That's it! Now when you edit your Footer, you can use autotext to automatically update anytime the file is opened by someone else.

2. Headers & Footers

Headers, footers, titles, page numbers, and filenames are all additional items that you may add to your work at any time; you may do it at the very beginning or at the last moment.

To see an example of a footer, look below at this tutorial's footer. It has everything from the saved filename, author, last day/time modified, page numbers and contact info.

Excel has the option to edit the headers and footers of a file. You can specify what it is you would like to display in the header or footer of your file.

1. Go to the View menu and pull-down to Header and Footer.



From here, you may choose both the Header and Footer items from the pull-down list or if you feel creative and confident enough, you can create a Custom Header to print exactly what you'd like. For now, let's choose a pre-defined header and footer.

2. Pull-down the Header list and scroll through it until you find the spreadsheet's title: "Page1, Four Dwarves' Income".

3. Click the phrase "Page1, Four Dwarves' Income". This returns you to viewing the Header and Footer.

Let's do the same with the Footer.

4. Pull-down the Footer list and select : "Your-Name-Here, Page 1, date".

5. Click on it then click on OK. You will be returned to editing your spreadsheet.

III.) Finishing up in Excel

A. Saving

 

It's definitely time to save our work! Ideally, we'd rather save our work sooner than now, because computer accidents happen: your disk could be unusable, the electricity could go out, or your computer "crashes". So, remember to save early on and save frequently (every 10 minutes is ideal).

For the first time you save a file, you should choose to Save As... because it allows you to change its name and location of where you're saving it.

1. Go to the File menu and pull-down to Save As...

Within the Save In: window, choose a new destination in which to save the "snowwhite" excel file. It is strongly suggested that you save your file(s) to a floppy disk or zip disk if you planning to carry them around with you between home, work, and/or school.

2. If you have one, place a floppy disk into the A:\ drive and choose the 3½ Floppy (A:) from the pull-down list.

If you don't have a floppy drive with you, that's okay. For this exercise, you may save the file in the My Documents folder. If you were at home, you would probably save your files to this folder most of the time.

Now, we want to rename the file. This is useful if you don't want to write over your original file or if you'd like to rename it to be something more meaningful.



3. Rename our current "snowwhile.xls" Excel file to read: "Dwarves_Income.xls". Be certain that "Microsoft Excel Workbook (*.xls)" is specified under the Save as type: region.

4. Click to save the excel file.

B. Preview

 

We need to verify that everything we've worked on looks correct and is positioned effectively on the paper before we send "Dwarves_Income" to print.

1. Click the Print Preview button in the toolbar.



This brings up the window allowing you to see how the document will look on the page. At this time, many available page-formatting tweaks are in their default state.

Notice how the spreadsheet is crammed into the top-left corner of the page? We can change the placement of the spreadsheet and alter the margins as well. We can also rotate the paper orientation so that the width of "Dwarves_Income.xls" will fill the paper space effectively.



2. Begin by clicking Setup...

The first tab at the top (the one we are currently in) is for options of page formatting. This is where we change the paper orientation and how big or small the spreadsheet is going to print.



3. Change the paper orientation to "Landscape".

Now, let's make the spreadsheet fill the page.

4. Click the radio button next to Fit to: 1 page(s) wide by 1 tall.

Changing the margins can allow for more printing area, often making it easier to read complex spreadsheets or to allow space on the left for hole punches.

5. Begin by clicking on the Margins tab at the top.



6. Change the left and right margins to be 1 inch each.

7. Also, check the Center on page options "Horizonatally" and "Vertically."

Notice that there is a miniature preview in the middle that illustrates how the changes will look on the potential page.

8. Now, click on OK to return to the print preview mode.



9. Here, you are returned to the overall page complete with your text and setup modifications.

We can return to the editing layout by clicking on the Close button. This is the one exception of choosing Close that does not end the program; instead, it only closes the Print Preview and gives us the opportunity to make necessary alterations. However, we can also choose to print from this screen, by selecting the Print button.

10. For now, click the Close button to return to editing.

We're almost ready to print. We ought to check for any misspellings before sending any jobs to the printer.

11. Click on the Spelling and Grammar button and wait while it searches for any potential errors.

12. If Excel presents this message: "Do you want to continue checking at the beginning of the sheet?" Just click the Yes button to continue spell-and-grammar checking the entire spreadsheet.

Chances are, we have no errors. We are now ready to print!

C. Printing

 

If we wanted to double-check the look of our document again, we could certainly go back to Print Preview and then print from there. But if we feel confident (as we do right now) that there aren't any other modifications we need to review, then we can directly choose to print.

Within the editing mode of Excel, there are three common ways to launch the print command: navigating through the menus, clicking on the Print Toolbar button, or by pressing Ctrl+P.

It should be mentioned that there are differences between using the menus and "Hot Key" method versus the Print button on the Toolbar. The Print button on the Toolbar sends it directly to the default printer without allowing you to modify any printer settings such as resolution nor does it allow you to select what part (or all) of the spreadsheet you'd like to print.

Often, it's better to choose printing from within the menus or by pressing Ctrl+P.

1. Begin by going to the File menu.

2. Pull-down to Print...



From here, you can select various different printers, change those printer settings, or choose what selection of the Excel spreadsheet you'd like to print.



3. If you need to, select another printer from the top pull-down list.

4. Change what pages you'd like to print (in our case, it's only one page, so we can go with the default of "All".

5. If you wish to print more than one copy, change the number by clicking the up-down arrows or by deleting the old number and entering the number of copies you wish to print.

6. When you're ready to print, click the OK button.

Additional resources:
For additional resources on Excel, access to the Microsoft e-Learning Library is available at this link:
http://mantis.csuchico.edu:2048/login?url=http://etraining.csuchico.edu