Microsoft Excel 2010 2 Formulas, Functions, and Formatting Introduction In Chapter 1, you learned how to enter data, sum values, format a worksheet to make it easier to read, and draw a chart This chapter continues to highlight these topics and presents some new ones The new topics covered in this chapter include using formulas and functions to
5 Have EXCEL help to find the IF statement by typing the word “IF” in the search area Then click on “GO ” 6 Select the “IF” function and click “OK ” 7 The screen should now show the “Function Argument” window 8 Let EXCEL help you fill in the 3 parts of the IF statement Then click on “OK ” 9
Excel 2010 is a spreadsheet software in the new Microsoft 2010 Office Suite Excel allows you to store, manipulate and analyze data in organized workbooks for home and business tasks Three types of basic data In a spreadsheet there are three basic types of data that can be entered • labels - (text with no numerical value)
Essential Fundamentals of EXCEL 2010 Page 7 of 19 Creating and Opening Workbooks Excel files are called workbooks Each workbook holds one or more worksheets (also known as "spreadsheets") To Create a New, Blank Workbook: 1 Click the File tab This takes you to Backstage view 2 Select New 3 Select Blank workbook under Available Templates
Opening Microsoft Excel: To get started with Microsoft Excel (often called “Excel”), you will need to locate and open the program on your computer To open the program, point to Excel’s icon on the desktop with your mouse and double-click on it with the left mouse button If you don’t see the Excel icon on your desktop, you’ll
1" "AdvancedExcel Formulas:Functions " Being"able"to"work"with"Excel"Formulas"can"take"your"experience"with"the"program"to"anew" level "Formulas"are"the"basic
Excel Cheat Sheet Basic Skills The Excel Program Screen Keyboard Shortcuts Getting Started Create a Workbook: Click the File tab and select New or press Ctrl + N Double-click a workbook Open a Workbook: Click the File tab and select Open or press Ctrl + O Select a recent file or navigate to the location where the file is saved
Cours Excel 2010 Leçon 1 : • Les bases (bases, texte) • Les bases (mise en forme du texte) • Les bases (alignements, fusion de cellules, couleurs) • Les bases (déplacer/copier du contenu) • Les bases (enregistrement, impression, feuilles) • Les bases (exercice à télécharger) Leçon 2 : • Les tableaux (bordures)
[PDF] fonctions excel 2007 pdf gratuit
[PDF] calcul de l'annuité
[PDF] cours mathématique financière maroc pdf
[PDF] formule financière excel
[PDF] mathematique financiere annuité
[PDF] valeur actuelle d'une annuité
[PDF] calcul valeur future
[PDF] cours annuités mathématiques financières pdf
[PDF] controle statistique 4eme
[PDF] fabrication systeme solaire a l'echelle
[PDF] systeme solaire echelle reduite
[PDF] pyélonéphrite
[PDF] oxalate de calcium monohydraté
[PDF] dissoudre oxalate calcium
[PDF] calculs renaux aliments interdits
John W. Jacobs Technology Center
450 Exton Square Parkway
Exton, PA 19341
610.280.2666
ccljtc@ccls.org www.ccls.org
Facebook.com/ChesterCountyLibrary
Intermediate Microsoft
Excel 2010-
Charts and Formulas
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 1
Workshop Topics:
Charts and Formulas
o Create multiple charts from a single worksheet and links to another file o Change the appearance and location of a chart
Formulas
o Use date, autosum, logical, and lookup functions o Understand relative and absolute addressing
Outline of Workshop:
Charts and Formulas
o Autosum formula for rows and columns o Insert column chart o Modify the column chart using the chart tabs o Use formulas to arrange data for other chart types o Insert pie chart o Modify the pie chart using the chart tabs o Use formulas and links (from another file) to prepare data for other chart types o Insert line chart (Optional - if time permits) o Modify the line chart using the chart tabs (Optional - if time permits) o Copy a chart o Move a chart to a separate worksheet tab o Make changes by hiding rows
Formulas
o Date functions: NOW() o Logical functions: IF SUMIF o Lookup function: VLOOKUP (Optional - if time permits) o Review relative and absolute addresses
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 2
Autosum formula for rows and columns
1. Go to H3 in the ͞Graph of Monthly Edžpense" tab.
2. Click on the FORMULAS worksheet tab and select the AUTOSUM command (SUM).
3. Copy the formula down to the H7 cell.
4. Go to cell B8. Repeat the AUTOSUM command for B8 and copy across to the H8 cell.
The screen should look the same as the following:
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 3
Insert column chart
Steps to create column chart
1. Highlight range (e.g.-A2 to G7)
2. Go to INSERT TAB
3. Click on COLUMN
4. Click on 3-D CLUSTERED
5. Click on LAYOUT 3 in Chart Layouts
6. Position chart and resize
7. Name the chart.
Modify the column chart using the chart tabs
Design Tab (Try the chart layout and style options)
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 4
Layout Tab
Format Tab
Use formulas to arrange data for other chart types
1. Go to cell J3.
2. 2. Enter the following formula: =A3
3. Copy the formula in cell J3 to cells J4 through J8.
4. Go to cell K2.
5. 2. Enter the following formula: =H2
6. Copy the formula in cell K2 to cells K3 through K8.
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 5
The screen should look the same as the following:
Insert Pie Chart
Pie Chart
1. Highlight range (e.g.-J2 to K7)
2. Go to INSERT TAB
3. Click on PIE
4. Click on 3-D PIE
5. Click on DATA LABELS in LAYOUT TAB
6. Click on BEST FIT
7. Position chart and resize
The screen should look the same as the following:
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 6
Modify the pie chart using the chart tabs
Design Tab (Try the chart layout and style options)
Layout Tab
Format Tab
Use formulas and links (from another file) to prepare data for other chart types
1. Go to cell B11.
2. Start a formula by entering: ͞с"
3. Click on the other Edžcel file named ͞Sales1" on the task bar at the bottom of the screen.
4. Go to cell B3 in the ͞Sales1" file screen and enter one left click on the mouse.
5. Press the ENTER key on the keyboard.
6. Adjust the absolute to relatiǀe address by deleting the ͞Ψ" symbol in the formula.
7. Copy the formula in cell B11 to cells C11 to G11.
8. In cell B12, enter a formula that copies the data in cell B8.
9. Copy the formula in cell B12 to cells C12 to G12.
10. In cell B13, enter the following: =B11-B12
11. Copy the formula in cell B13 to cells C13 to G13.
12. Enter the Autosum function (for the rows) in Cells H11 to H13.
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 7
The screen should look the same as the following:
Insert line chart (Optional - if time permits)
1. Highlight Cells A10 to G11.
2. Hold the ͞Ctrl" key down and highlight the cells A13 to G13.
3. Go to INSERT TAB.
4. Click on LINE.
5. Click on ͞Line with Markers" option (1st option in row 2).
6. Click on LAYOUT 3.
7. Position chart and resize
8. Name the chart.
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 8
The screen should look the same as the following:
Modify the line chart using the chart tabs (Optional - if time permits) The options are available in the Design, Layout, and Format tabs see the references above.
Copy a chart
1. Highlight the PIE chart on the screen.
2. On the HOME TAB, click the copy command.
3. Go to cell M2.
4. On the HOME TAB, click the paste.
5. Position chart and resize
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 9
The screen should look the same as the following:
Move a chart to a separate worksheet tab
1. Highlight the PIE chart recently copied.
2. On the DESIGN TAB, click the Move Chart command icon.
3. Click on the ͞New Sheet" option and then ͞OK."
4. The PIE chart was moǀed to the ͞Chart 1" worksheet tab.
The screen should look the same as the following:
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 10
Make changes by hiding rows
1. Go to the ͞Graph of monthly edžpense" worksheet TAB.
2. Move the mouse to the row # 3 indicator. A appears.
3. Right click and choose ͞Hide" as the option.
4. Observe the differences in the charts.
The screen should look the same as the following:
Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 11
Formulas
Date Functions: NOW()
1. Go to the ͞WS Formulas" worksheet tab.
2. Go to the cell A1.
3. Click on the FORMULAS TAB.
4. Click on the ͞Date and Time" option.
5. Click on the NOW function.
6. Cell A1 includes the following function: =NOW()
7. The date and time are recorded as a time stamp.
8. View the other options under ͞Date and Time."
Logical functions: IF
1. Go to the cell E3.
2. In cell E3, we wish to have the following IF statement evaluate
the difference in the yearly amount (Column T) vs. the budget amount (Column U): =IF(T3
3. Reǀiew APPENDIy ͞A" 4. Remain in cell E3 and click on the ͞fx" button to the left of the
formula bar. 5. Have EXCEL help to find the IF statement by typing the word ͞IF"
in the search area. Then click on ͞GO." 6. Select the ͞IF" function and click ͞OK."
7. The screen should now show the ͞Function Argument" window.
8. Let EXCEL help you fill in the 3 parts of the IF statement. Then
click on ͞OK." 9. Click on E3 and then the FORMULA BAR to view the color coding
of the function as an additional way to understand the IF statement. Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 12
Logical functions: SUMIF
1. Go to the cell H17.
2. In cell H17, we wish to have the following SUMIF
statement reference the expense categories in column ͞G" and add the expense amounts in the subsequent columns of numbers in column ͞H" through column ͞S" by matching the expense categories in column ͞G:" =SUMIF($G$3:$G$15,$G17,H$3:H$15) 3. Reǀiew APPENDIy ͞B"
4. Remain in cell H17 and click on the ͞fx" button to the left
of the formula bar. 5. Have EXCEL help to find the SUMIF statement by typing
the word ͞SUMIF" in the search area. Then click on ͞GO." 6. Select the ͞SUMIF" function and click ͞OK."
7. The screen should now show the ͞Function Argument"
window. 8. Let EXCEL help you fill in the 3 parts of the SUMIF
statement. Then click on ͞OK." 9. Click on H17 and then the FORMULA BAR to view the
color coding of the function as an additional way to understand the SUMIF statement. Intermediate Excel 2010 Workshop 2
Charts and Formulas
Page 13
Lookup function: VLOOKUP (Optional - if time permits) 1. Go to the cell B3.
2. In cell B3, we wish to have the following VLOOKUP
statement reference the named city in column ͞A" and search the referenced table (W2 through Y11) to locate and return the value for the State in the indicated column (2) of the table on the same table row: =VLOOKUP(A3,$W$2:$Y$11,2) 3. Remain in cell B3 and click on the ͞fx" button to the left
of the formula bar. 4. Have EXCEL help to find the VLOOKUP statement by
typing the word ͞LOOKUP" in the search area. Then click on ͞GO." 5. Select the ͞VLOOKUP" function and click ͞OK."
6. The screen should now show the ͞Function Argument"
window. 7. Let EXCEL help you fill in the 3 required parts of the
VLOOKUP statement. Then click on ͞OK."
8. Click on B3 and then the FORMULA BAR to view the color
coding of the function as an additional way to understand the VLOOKUP statement. Review relative and absolute addresses
1. Reǀiew APPENDIy ͞C"
IF function
This describes the formula syntax and usage of the IF function in Microsoft Office Excel.
=IF(logical_test, value_if_true, [value_if_false]) The IF function in cell E3 is: =IF(T3Description The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(T3COMPARISON OPERATORS You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.
COMPARISON OPERATOR MEANING EXAMPLE
= (equal sign) Equal to A1=B1 > (greater than sign) Greater than A1>B1 < (less than sign) Less than A1= (greater than or equal to sign) Greater than or equal to A1>=B1 <= (less than or equal to sign) Less than or equal to A1<=B1 <> (not equal to sign) Not equal to A1<>B1 APPENDIX A - IF Function
SUMIF function
This describes the formula syntax and usage of the SUMIF function in Microsoft Office Excel
Description
=SUMIF($G$3:$G$15,$G17,H$3:H$15) In this formula in cell H17, you can apply the criteria to one range (G3 to G15 where one of the expense categories matches the expense category listed in G17) and sum the corresponding values in a different range (Columns H to S). For example, the formula in cell H17 is =SUMIF($G$3:$G$15,$G17,H$3:H$15) and sums only the values in the range from H3 to H15, where the corresponding cells in the range from G3 to G15 equal the same value that is in cell G17 (Rent). The formula in column was then copied to columns through . Please note that the use of relative and absolute addresses in the formula allow for the summations by columns based on the selection evaluation that takes place APPENDIX B - SUMIF Function
Relative and absolute cell address
Copy a formula:
The following table summarizes how a reference type updates if a formula that contains the reference is copied two cells down and two cells to the right. FOR A FORMULA BEING
COPIED:
IF THE REFERENCE IS: IT CHANGES
TO: $A$1 (absolute column and absolute row) $A$1 A$1 (relative column and
absolute row) C$1 $A1 (absolute column and relative row) $A3 A1 (relative column and
relative row) C3 The formula in cell H17 is
=SUMIF($G$3:$G$15,$G17,H$3:H$15). When this formula is copied across the row and down the column, the absolute range from G3 to G15 does not change. The selection criteria of G17 will change the relative address of the row 17 but will not change absolute column designation of G. The range from H3 to H15 will change columns but not rows when copied. APPENDIX C - Relative and Absolute cell address
quotesdbs_dbs7.pdfusesText_13
Microsoft Excel 2010 2Formulas, Functions, and Formatting