Exercise 16 Excel 3 Dimensional Workbook

  1. You have three stores in Atlanta, Boston and Chicago

  2. You also have 3 products - product 1, 2 and 3

  3. You would like to combine the sales results of all three cities into one Total Sheet

  4. Open 3dimensionalworkbook and calculate each quarter into the Total Worksheet.

  5. The first cell has been completed - please calculate the remaining cells. 

  6. Save your work and email to me.

Exercise 17 Excel - PivotTable. A pivot table is an extremely flexible tool that helps you manipulate data in multiple work sheets. You can then produce new reports. An example would be if your are in Management and you are presented with a long list of sales figures for each of the sales reps and district, you can create a PivotTable and then drag fields to PIVOT that data and see totals for each rep and district.

Have a look

  1. Open pivottable  - and click anywhere within the list range A7:E29

  2. Click Data on the menu bar , then click PivotTable and Pivot Chart Report - Get rid of the assistant if he/she appears!

  3. In the step 1 of 3 dialog box, make sure that the Microsoft Excel list or database and the PivotTable option buttons are selected, then click Next  

  4. In the step 2 of 3 dialog box, make sure that the list range $A$7:$E$29 appears in the range box, then click Next  

  5. In the step 3 of 3 dialog box, make sure the New Work Sheet Option is selected, then click Finish . ( A new worksheet appears, containing PivotTable drop areas surrounded by blue lines and a PivotTable Field list showing all of the list fields. The PivotTable toolbar appears on the worksheet.)

  6. Drag the Begin Date field from the field list to the Drop Row Fields Here area, releasing the mouse button when the pointer becomes a miniature PivotTable.

  7. Drag the Item field from the Field List to the Drop Column Fields Here area

  8. Drag the Amount field from the Field List to the Drop Data Items Here area. You can see expense totals by date and totals for each expense item.

  9. Save your work and email it to me.

Exercise 18 Excel - Create formulas using the IF function

  1. Open IF  - and click cell I10

  2. Click Insert Function button fx  on the Formula bar

  3. Find IF and Select it. There are three parts to this formula. The Logical test, value if TRU and value if FALSE.

  4. Enter the following information in the Function Arguments dialog box:

Logical_test    H10>120

Value_if_true  OK

Value_if_False Below average

  1. Click OK

  2. Drag the fill handle on cell I10 down through cell I36, then click outside the range

  3. Double-click the right side of the column I column heading to AutoFit the cell contents

  4. Save your work and email it to me

Exercise 19 Excel - Forecast values with What if Analysis- Creating a Trend Line

  1. Open Whatif  - and click on the sheet tab Lesson Revenue Chart

  2. Click Chart on the menu bar, then click Add Trendline

  3. In the Add Trendline dialog box, click the Type (it is probably already selected) then, under Trend/Regression type, click Linear

  4. Click OK

  5. Click Chart on the menu bar, then click Add Trendline

  6. In the Add Trendline dialog box, click Exponential, then click OK

  7. You will note that if the trend is expected to continue in a linear manner then the projected sales would be $37,000; if they are expected to increase exponentially then the projected figures would be $43,000. 

  8. Save your work and email to me.

Exercise 20 Excel - Recording New Macros - an example for calculating PMT

  1. Open Excel

  2. Click on Tools then select Record New Macro

  3. For Macro Name say IMBA

  4. For short cut key enter a small p

  5. Click OK

  6. The window should close and now you are recording

  7. In cell A1 enter Sales Amount

  8. In cell B1 enter down payment

  9. In cell C1 enter Loan amount required

  10. In cell D1 enter periods

  11. In cell E1 enter interest rate

  12. In cell F2 enter the formula for PMT (=-pmt(E2/12,d2,c2)

  13. Now back to Tools, Macro, Stop recording

  14. To test it out open a new excel sheet

  15. Press Ctrl p

  16. The macro should have kicked in

  17. Now enter the amounts in cells A2,B2,C2,D2,E2

  18. F2 should give you the result automatically .

  19. From now onwards all you have to do to calculate PMT is Ctrl p and enter the values.

  20. :-) (S-Thanks for your email)

Exercise 21 Excel - Protecting your work with a password

  1. Open Excel

  2. And remind me to discuss it with you :-)

 

Home  | Log in  |  Syllabus | Excel I Exercises | Excel II Exercises | Excel III Exercises | Excel IV Exercises | PowerPoint Exercises I |    Laptop and PC maintenance  Exercises I |  Exercises II  | Exercises III (Speed Test your connection)