New York City College of Technology

Microsoft Excel 2010   Contact Information: 718-254-8565 [email protected] 

Office 2010 “Ribbon”



Formatting tools are categorized by tabs. For example, the View tab has specific functions.



is backward compatible with previous versions of Microsoft office, “Save As” such as Office 2003 and /or PDF formats.



Opening Templates:

           

 

 

1 

Select any template installed on your computer or download templates by selecting any category.

Click

when done.



The “Quick Access” toolbar can be customized:



“Mini Toolbar” – Allows you format your information as you go by simply right-clicking on the desired information to be formatted:

 

 

 

2 

EXCEL WORKSHEETS  “Worksheet” -- By default the file name of the first Excel file will be book1 and it will contain 3 worksheets labeled worksheet1, worksheet2 and worksheet3:  In the title bar you will see the entire book:  At the bottom of the workbook you will see your current sheet:  You should save your workbook with a different name by going to:  Type in the desired name for your workbook.  For a worksheet new name you need to double-click on the sheet name or right-click for options on this particular worksheet:



Formatting information: Columns are vertical and are represented by Letters and rows are horizontal and are represented by numbers. The corner of your worksheet will be a “Cell” with a reference address of A1:



The yellow areas selected are called headers. You can expand the header of Column “A” by double-clicking between header “A” and “B”. Wait for the pointer to become a double arrow pointer.



To copy information over to another cell, make sure your cursor looks like a white cross. Hold the mouse down drag and select desired information to copy



To move information to another cell, select the information and make sure you pointer looks like a four arrow cross. Drag the mouse until you reach the desired destination.

 

 

3 

Activity 1 1. Type Last Name into cell A1. 2. Type First Name into cell B1. 3. Type Department into cell C1. 4. Select cells A1-C1.

5. Change Font Size to 16. 6. Click the Bold button to change text to bold. 7. Click the Center button to center text. 8. Adjust cell widths. 9. Type 5 Last Names, First Names, and Departments in cells A2-C6.

 

 

4 

THE HOME TAB

Sorting information:



Sort & filter utility



Click on Custom Sort

You will also need to check the box that reads: My data has headers

Filtering information:

Select desired information to be filtered:



Use the Sort & Filter utility

A drop-down menu will be place in every heading:

 To clear filter click on the drop-down menu heading.

  

 

5 

THE REVIEW TAB Adding comments:



Select the “Review tab” on the ribbon:



Click on New – a new comment box shows:

All comments are hidden by default unless you ask Excel to show all comments:

Protecting information in a sheet: 

Click on Protect Sheet and assign a desired password.

Spell checking: Also found under Review.

THE FORMULAS TAB

Select the Formulas Tab on the ribbon

 

 

6 

Simple summation can be done by using the AutoSum feature: 

Select cells and the sum will appear in the bottom blank cell:

Simple subtraction: Select location for output and click on the insert function button:



Search and select IMSUB command

 Complex subtraction: 

Think of subtraction as a result of sum – another sum



Identify total of information to be subtracted from another.



Subtract A2-SUM(A3:A10) =

: following:

 

Other functions included in the AutoSum button menu

 

are the

7 

Activity 2 1. Open a blank worksheet and Save As Activity 2. 2. Type Name in cell A1. 3. Type Monday in cell B1. 4. Type Tuesday in cell C1. 5. Type Wednesday in cell D1. 6. Type 3 names in cells A2-A4. 7. Type 3 amounts in cells B2-B4, C2-C4, and D2-D4. 8. Type Savings in cell A6. 9. Type 200 in cells B6-D6. 10. Type Total Spent in cell A8. 11. Type Amount Remaining in cell A10.

12. Select cell B8 and click on the AutoSum button. 13. Select cells B2-B4 and hit Enter. 14. Repeat Steps 12 and 13 for cells C8 and D8.

15. Select cell B10 and click the Fx button. 16. Search and Select IMSUB command.

17. Select B6 for Inumber1. 18. Select B8 for Inumber2. 19. Repeat Steps 15-18 for cells C10 and D10. 20. Save and close.  

 

8 

THE DATA TAB

Select the Data Tab from the Ribbon

 



Add information from the Web by selecting the location where information should go. Click on “From Web” icon:



Type in the address that you want to import from:



Click on Import:



Always select the top left corner so that all of your information is visible:

 

9 

Activity 3 1. Open a blank document and Save As Activity3. 2. Click on From Web button. 3. Type www.citytech.cuny.edu/Phone/listall.asp in address bar.

4. Click on the arrow next to the table. 5. Click Import. 6. Select cell A1 and click OK.

 

 

10 

THE VIEW TAB

Select the View Tab from the Ribbon





Custom View

allows you to save queries of information on a worksheet:

->

->

The “Zoom to Selection” button will allow you to zoom into the location of your cursor or cell. Click on the “100%” zoom button to restore normal size and view.

THE PAGE LAYOUT TAB

Add effects to your information:



 

Selecting the Themes, Colors, Fonts and Effects group:

 

.

11 



Margins are customizable according to your needs:



Page Orientation – Landscape or Portrait:

.

Selection Pane allows you to see existing format: 

 

Click on the “Eye” icon on the right hand side panel to view formats or items of your data.

 

12 

THE INSERT TAB

Using the Insert tab to insert a header:



Click on the Header & Footer button

When the header is selected you get a new formatting tab called “Design” tab:



Click on the Go to Footer button to switch to footer.



Excel will keep you in the Design tab



Go to the View tab and click on Normal button



Go to the Insert tab.

for normal view.

Use tables to sort information:

 



Click on the Table button



Tell the table location

 

 

13 



Tables have filter and sorting capabilities:

When the table is selected you get a new formatting tab called “Design” tab:

Creating Charts:



Select information to be included:



Go to

-> Select type - >

or

When the chart is selected a new formatting tab pertaining to charts is displayed - Chart tools:

 

 

14 

Activity 4 1. Open Activity 2. 2. Go to Insert tab.

3. Click on the Table button 4. Select cells A1-D4 and click OK.

5. Click on Column button

.

6. Select 2-D Column (1st Choice). 7. Save and close.

 

 

15