1 / 41

Excel Level 3

Excel Level 3. Jeffrey A. Richards August 2016. Excel Level 3 Agenda. Part Ten - Automating Repetitive Tasks with Macros Plan Your Macro Record and Test Your Macro * Edit Your Macro * * * Make it Even Easier to Run Macros * * Part Eleven - Analyzing Alternative Data Sets

deforest
Télécharger la présentation

Excel Level 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel Level 3 Jeffrey A. Richards August 2016

  2. Excel Level 3 Agenda • Part Ten - Automating Repetitive Tasks with Macros • Plan Your Macro • Record and Test Your Macro * • Edit Your Macro * * * • Make it Even Easier to Run Macros * * • Part Eleven - Analyzing Alternative Data Sets • Creating Scenarios * • Creating Scenarios with Multiple Variables * • The Scenario Summary * * • Using Goal Seek * • Using Solver* * • Part Twelve – Conditional Functions • Introducing Conditional Functions * • Applying Multiple Conditions* * • Tracing Components of Formulas • Part Thirteen – Introduction to PivotTables • Analyzing Data with PivotTables* * • Editing PivotTable Layouts • Basic PivotTable Manipulation * • Appendix • Excel Limitations & Specifications • Getting Certified in Excel • Past Certification Exam Questions

  3. Other Excel Classes • Excel Level 1 • two weeks ago… • The Excel Environment • Entering & Formatting Data • Working with Data & Tables • Finishing your Workbook • Data Filtering • Excel Level 2 • last week… • Re-ordering & Summarizing Data • Creating Charts & Graphs • Performing Calculations on Data (Formulas 101) • Collaborating with Colleagues New Advanced Level Classes for Excel • Excel Formulas and Functions • Saturday, October 22, 2016 • Text Functions • Date and Time Functions • Statistics and Array Functions • Logical and Look-up Functions • Excel PivotTable Data Crunching • Saturday, November 05, 2016 • Creating PivotTables and Basic Formatting • Customizing PivotTables • Performing Calculations in Pivots • Remapping Source Data and Reverse Pivots • PivotCharts – the Best of Both Worlds

  4. Part Ten - Automating Repetitive Tasks with Macros Excel documents are often easily formatted or changed as a one-time thing (as when you enter data into your workbook) or with simple, easy-to-complete clicks of the mouse (as when you apply formatting to a range of cells). These tasks are not difficult. Other tasks are much more complex, as when you apply a PivotTable to a database or when you execute a custom sort and filter on a table of information. Whether difficult or easy, you can write a macro as a shortcut for tasks you perform frequently. For example, you can create a macro to enter and format text and add a company logo in a header for your worksheet. Or perhaps a macro to print and save a worksheet. To create a macro, you record a series of actions, or you can write the instructions in a special programming language. STEP ONE: PLAN YOUR MACRO Because the sequence of actions is important, you need to plan the macro carefully before you record it.

  5. Macros continued • STEP TWO: RECORD and TEST YOUR MACRO • A macro is nothing more than an Excel-recorder that automates a series of actions by recording and then “playing back” your keystrokes, the same way your DVR plays back TV shows you have recorded. • The easiest way to create a macro is to record it using the Excel Macro Recorder. The basic “record” process looks like this: • Enable your workbook to receive macros first • View tab, Macros group, Macros down arrow, Record Macro… • In the Record Macro dialog box, insert a name (no spaces), then assign • a shortcut key, store the macro, and insert a description • Once you hit OK, the recording begins • When finished: View tab, Macros down arrow, “Stop Recording” • As you record the macro, Excel automatically translates each action – whether keystrokes or mouse clicks – into program code you can later view and modify. You can take as long as you want to • record the macro because a macro only copies your actions, not the length of • time you took to record it. • Applying a macro once it has been written is even easier • If you saved the macro to a Personal Macros workbook, it will automatically • open when you start Excel, just as your TiVo contains all of the shows you • have recorded, regardless of which you are watching • If you saved the macro to a specific workbook, it will open with the workbook, • like popping in a DVD (the functions stay with the media) • Either way, applying a macro is done by going to the View tab, Macros group, • and clicking the Macros button, then selecting the Macro you want to apply • from the list of available macros. Let’s create and then test the Division • Stamp macro on a blank worksheet.

  6. Macros continued STEP THREE: EDIT YOUR MACRO When you use the Macro Recorder to create a macro, the program instructions, called program code, are recorded automatically in the Visual Basic for Applications (VBA) programming language. Each macro you record is stored as a module, which is nothing more than a program code container. The module is attached to the workbook. A macro can also be written in VBA code in the first place. Either way, if you want to make changes to an existing macro, you will need to be able to go into the code and make changes there. You do this by using the Visual Basic Editor, a program that lets you display and edit your macro code. To edit a macro: Display the available macros: View tab  Macros group  Macros down arrow  View Macros 2. Select the macro you want to edit 3. Click the Edit button on the right 4. In the Visual Basic Editor, click the name of the module you want to edit in the Project Explorer window 5. Examine and edit the code in the Code window 6. File  Close and Return to Microsoft Excel The name of the macro and any comments you typed in appear at the top of the Code window. While recording a macro, when you open and make selections in a dialog box, Excel automatically stores all dialog box settings in macro Code. Let’s change our DivStamp VBA code so that the font of the Sales Division text is in 16 point font… Code window Project Explorer window Properties window

  7. Macros continued self-paced skills practice – Creating and Editing a Macro Let’s practice editing Macros, using the YearlySalesSummary workbook in your personal folder. Make sure to enable the content, if you are asked to, then follow these steps: On the View tab, in the Macros group, click the Macros arrow and then, in the list that appears, click Record Macro. The Record Macro dialog box opens. In the Macro name box, delete the existing name, and then type RemoveHighlight. Click OK. The Record Macro dialog box closes. Select the cell range C4:C7. The text in these cells is currently bold. On the Home tab, in the Font group, click the Bold button to un-bold the text. On the View tab, in the Macros list, click Stop Recording. Excel stops recording the macro. In the Macros list, click View Macros. The Macro dialog box opens. In the Macro name area, click RemoveHighlight, and then click Edit. The Visual Basic Editor starts. Edit the line of code that currently reads Range(“C4:C7”). Select so that it reads Range(“C3:C9”).Select. This macro statement selects the cell range C3:C9, not the incorrect range C4:C7. On the Standard toolbar of the Visual Basic Editor, click the Save button (looks like a blue 3.5” floppy disk) to save your change. On the title bar of the Visual Basic Editor window, click the Close button. The Visual Basic Editor closes. Select cells C3:C9, format them as bold, and then click cell C9. Excel formats the cells’ contents in bold. In the Macros list, click View Macros. The Macro dialog box opens. Click RemoveHighlight, and then click Run. The bold formatting is removed from cells C3:C9. On the Quick Access Toolbar, click the Save button. Excel saves your workbook. Close the workbook and move to the problem on the next slide.

  8. Macros continued self-paced skills practice – Editing (De-bugging) a Macro Let’s practice editing Macros, using the VolumeHighlights workbook in your personal folder. Be sure to enable the content, if you have the yellow bar at the top requesting you to. Then: On the View tab, in the Macros group, click the Macros arrow and then, in the list that appears, click View Macros. The Macro dialog box opens. The HighlightSouthern macro will turn 3 Centers in the orange table red: Atlantic, Southeast, and Southwest. Click the HighlightSouthern macro, and then, to display the macro code, click Edit. The Visual Basic Editor opens, with the code for the HighlightSouthern macro displayed in the Module1 (Code) window. In the Visual Basic Editor window, click the Close button. The Visual Basic Editor closes, and Excel displays the VolumeHighlights workbook. In the Macros list, click View Macros. The Macro dialog box opens. Click the HighlightSouthern macro, and then click Step Into. The macro appears in the Visual Basic Editor, with the first macro instruction highlighted. The F8 key allows you to execute the macro, one step at a time, while at the same time navigating through the corresponding instructions in the code window. Press the F8 key. Excel highlights the first instruction. Press F8 again. The macro selects the Atlantic row in the Excel table. Press F8 twice. The macro changes the Atlantic row’s text color to red and highlights the next instruction. Click the Visual Basic Editor Close button. A warning dialog box opens, indicating that closing the Visual Basic Editor will stop the debugger. Click OK. The Visual Basic Editor closes. In the Macros list, click View Macros.The Macro dialog box opens. Click the HighlightSouthern macro. Click Run. The Macro dialog box closes, and Excel runs the entire macro. Close the workbook without saving it.

  9. Macros continued • STEP FOUR: MAKE IT EVEN EASIER TO RUN YOUR MACRO • Add Macro Buttons to the Quick Access Toolbar - You can easily manipulate the Quick Access toolbar, as we learned in Level One: • Click the Customize Quick Access Toolbar arrow at the right end of the toolbar and select “More Commands” from the shortcut menu • At the bottom of the Popular Commands list, you can easily add the View Macros button to your toolbar, so that now you can display the macros available to you without going to the View tab and hitting the Macros drop down arrow • If you want to run a particularly useful macro directly from the Quick Access toolbar without first having to view the available macros, you can simply add a new macro button to your QA toolbar. • Customize the QA toolbar, but choose “Macros” from the Choose commands from dropdown • menu • Choose the macro you want and click “Add” • Click “Modify” to change the button (160 choices) and the screen tip • Click OK twice • Or if you prefer to run the macro only from the workbook and not from Excel generally, • you can add a button with greater graphics capability. This • happens when you have a reusable workbook that is shared by • people who need a quick way to view only the data they are • interested in This is done by clicking a object in your workbook. • First step is to insert a shape or picture into the workbook • Second step is to assign a macro to the object When you • assign a macro to run from an object, don’t change the name • of the macro that appears in the Assign Macro dialog box. • Changing the macro name breaks the connection between • the macro and the object and prevents Excel from running • the macro.

  10. Macros continued • Running Macros Automatically When a Workbook is Opened • Instead of running a macro by hand or from the Quick Access toolbar or an object in the workbook, you can write a macro that will automatically run each time the workbook is opened. • The trick of making that happen is the name you give the macro. • Whenever Excel finds a macro with the name “Auto_Open,” (one per workbook), it automatically runs the macro when the workbook to which it is attached is opened • Removing them from your system is done on the View tab, by viewing the macros and selecting one to delete • Removing them from your Quick Access toolbar is done by right clicking the button and removing it on the shortcut menu • skills practice Plan and create a macro that enters your name and address and e-mail into a blank worksheet, starting in cell G2 and moving vertically down the worksheet • Name the macro “MyInfo,” store it in the current workbook. Assign the shortcut key [Ctrl] + [Shift] + E and add a description to the macro that indicates this is your personal info. • As you record the macro, resize column G to accommodate the information, put a border around the information, and turn the text green and bold. • Create a new, blank spreadsheet and test the macro • In the Visual Basic Editor, remove the bold by changing the macro code from Selection.Font.Bold = True to Selection.Font.Bold = False. Re-test the macro to be sure the bold is no longer a part of the way your information is displayed. • Add the View Macros to a button on your Quick Access Toolbar

  11. Macros continued self-paced skills practice – Running a Macro upon Opening a Workbook Let’s practice creating Macros that run when you open a workbook, using the RunOnOpen workbook in your personal folder. On the View tab, in the Macros group, click the Macros arrow and then, in the list that appears, click Record Macro. The Record Macro dialog box opens. In the Macro name box, delete the existing name, and then type Auto_Open. Click OK. The Record Macro dialog box closes. Select the cell range B3:C11. On the Home tab, in the Font group, click the Bold button twice. The first click of the Bold button formats all the selected cells in bold; the second click removes the bold formatting from all the selected cells. Click cell C11 and then, in the Macros list, click Stop Recording. Excel stops recording your macro. In the Macros list, click View Macros. The Macro dialog box opens. Click Highlight, and then click Run. The contents of cells C4, C6, and C10 appear in bold type. On the Quick Access Toolbar, click the Save button to save your work. Click the Close button to close the RunOnOpen workbook. Click the File tab and then, in the Recent Documents list, click RunOnOpen.xlsm. If a warning appears, click Enable Content, and then click OK to enable macros. RunOnOpen opens, and the contents of cells C4, C6, and C10 change immediately to regular type. Close the workbook without saving your changes.

  12. Part Eleven - Analyzing Alternative Data Sets Excel workbooks allow you to discover important information about your business, essentially to see what has happened in the past. You can use that data – either by itself or as part of a calculation – to discover important information about your business. When you track business on a time basis, you can find your best and worst periods and correlate them to outside events. For example, a shipping company can see the package volume increases during the holidays as customers ship gifts to friends and family members. The data in your worksheets is great for asking the question, “What happened?” The data is less useful for asking “what-if” questions such as: “How much money would we save if we reduced our labor to 20% of our total costs?” You can, of course, save an alternate version of your workbook and create formulas that calculate the effects of your changes, but you can do the same thing in your original workbook by defining one or more alternative data sets and switching between the original data and the new, alternative sets you create. Excel also allows you to work backward. It allows you to start with the desired outcome and determine the inputs that would be required to get that result. For example, if your gross margin is $100,000 for the first half of the year, you might want to determine what your second half payroll and other controllable expenses would need to be in order for you to come in at a gross margin of $300,000 instead of $200,000 for the whole year. Obviously the biggest danger that people fear in running alternative scenarios on their data is that you have to change the data in order to “play” with the result. Many are afraid that they will accidentally save the hypothesis testing they are doing and lose the actual, original data.

  13. Alternative Data Analysis continued Creating Scenarios A scenario is a set of values you use to observe different worksheet results. For example, you might plan to sell 100 widgets, at a price of $5 each, producing gross revenue of $500. But what if you reduced the price to $4 or increased it to $6? The individual inputs in your equation are referred to as variables. The Excel Scenario Manager is a tool that allows you to name and save profiles with hypothetical changes to variables. This allows you to “play” with your data and compare the results side-by-side. • You get started first by determining the changing cells that you want to manipulate and then by going to the Data tab, Data Tools group, What-If Analysis button, Scenario Manager • Click “Add” to begin your first scenario for the worksheet • Type in the scenario name, specify the cell(s) to be changed, and click OK • The Scenario Values dialog box opens, fill in the alternate, hypothetical value • The updated Scenario Manager box opens and the scenario entered can be shown • Close the Scenario Manager. Use the Undo button to remove the scenario Once you have completed the view, click undo to restore the original value.

  14. Alternative Data Analysis continued self-paced skills practice – Setting Up a Scenario Let’s practice creating Scenarios, using the Skills Practice Workbook Excel Level 3, go to the Skills Practice A tab. On the Data tab, in the Data Tools group, click What-If Analysis and then, in the list, click Scenario Manager. The Scenario Manager dialog box opens. Click Add. The Add Scenario dialog box opens. In the Scenario name field, type 2DayIncrease. At the right edge of the Changing cells field, click the Collapse Dialog button so the worksheet contents are visible. The Add Scenario dialog box collapses. In the worksheet, click cell C5 and then, in the Add Scenario dialog box, click the Expand Dialog button. $C$5 appears in the Changing Cells field, and the dialog box title changes to Edit Scenario. Click OK. The Scenario Values dialog box opens. In the value field, type 13.2, and then click OK. The Scenario Values dialog box closes, and the Scenario Manager is displayed again. If necessary, drag the Scenario Manager dialog box to another location on the screen so that you can view the entire table. In the Scenario Manager dialog box, click Show. Excel applies the scenario, changing the value in cell C5 to $13.20, which in turn increases the value in cell E8 to $747,450,000.00. In the Scenario Manager dialog box, click Close. On the Quick Access Toolbar, click the Undo button. Excel removes the effect of the scenario. Close the workbook without saving your changes.

  15. Alternative Data Analysis continued • Creating Scenarios with Multiple Variables • Set up the original values as a Scenario first, using all changing cells • Add in as many individual scenarios as you would like • If you execute multiple scenarios, all selected ones are applied, and the last scenario applied winning in multiple scenarios applying to the same cell(s) • Run a Scenario Summary that gives you the details of all your summary changes compared with the original, making sure to select only your results cells for comparison • Multiple Scenarios Demo • On the Demo 4 tab in the Demo workbook, let’s work with the base rates and expected sales of our Coffee Wholesaler. The default values sent to you by your sales department will not suffice to achieve your objective if you need a total product gross revenue of $3.2M this year to support your P&L. You need to start working on some scenarios to drive up your by-product gross revenue… • Select cells C5:D8 as your changing cells – the variables – then Data tab  Data Tools Group  What If Analysis button  Scenario Manager • Set up an Original Sales Figures scenario with the current values and click OK to save the scenario • Add a second scenario that sets a uniform base rate of $19 across the board for all of our products. • Noting that there is a low set of expected sales for our two most expensive products, add a third scenario that adds 10K sales to the two Columbian products we sell. • There has been a recent interest from customers in buying Venezuelan Dark, and to capitalize on this trend, management would like to model the possibility of upping the sales quota from 40K to 60K, and to encourage this upswing, dropping the base rate from $17.50 to $17. Write a fourth scenario that does this. • Toggle between scenarios to see how close to $3.2M each option would get us. Would some combination of the scenarios help us achieve our goal?

  16. Alternative Data Analysis continued the scenario summary Although it is useful to “show” the different scenarios, the real value of the Scenario Manager is in being able to see the alternatives side-by-side in a single report. The Scenario Summary is an Excel table that compiles data from the changing cells and the corresponding results cells for each scenario you enter, and shows the alternatives side-by-side. • Scenario Summary Demo • Let’s continue with our Gross Revenue work on the Demo 4 tab. We have 4 scenarios already defined, including the original values. Now let’s • Identify your results cells. The changes will effect all of the gross revenue cells (E5:E9) • In the Scenario Manager dialog box, click on Summary, then define the results cells and click OK • In the resulting Scenario Summary that opens on a new tab, delete the Original column, since it is duplicative of the Current Values column • Notice that the changes in each scenario appear in gray • Replace the Changing Cells cell references with descriptive names • Replace the Results Cells with descriptive names • Set the column widths to a proper size, center the field names, and change the scenario names, if necessary

  17. Alternative Data Analysis continued • self-paced skills practice – Setting Up Multiple Scenarios • Let’s practice creating multiple Scenarios, using the Skills Practice Workbook Excel Level 3, go to the Skills Practice C tab. In this scenario, your are the training manager for the fictitious company Happy Tummies. You’ve been asked by your boss, the Director of Training and Development to prepare a report comparing how much a Customer Training workshop would cost from four vendors. You had already prepared a report with the original vendor that you were told the company was going to use to develop the materials for the training and conduct the workshop training. So now you need to add this new data and compare it to the one that you had already created. These three vendors have different costs per student and total number of students they allow in the workshops. These are the pieces of data that will vary. You need to also prepare a report showing these figures to present it to the management team so a final decision between the vendors can be made. • Start by creating a scenario with the original values for Eureka To Go, the original vendor. The changing values will be in B3 and B4. • Create a second scenario for the vendor LLX Learning, which allows a maximum number of students of 25 and a cost per student of $125 • Create a third scenario for the vendor Jimenez Associates, which allows a maximum number of students of 30 and has a cost per student of $100 • Finally, Mc-Graw Learning Specialists has submitted a bid, with a cost per student of $115 and a maximum number of students of 25. • Create a Scenario Summary that shows the student count and the cost per student side –by-side for each vendor. Cell B5 is your results cell.

  18. Alternative Data Analysis continued • Using Goal Seek: What If Analysis in Reverse • Using Scenarios is a good way to test hypotheses, but it is pretty hit-and-miss when you have a particular result in mind and want to know how to change certain components in order to get there. • Goal Seek works backward from the result you want, altering the components you specify • Data tab, Data Tools group, What-If Analysis down arrow, then select Goal Seek • To complete the process (called “backsolving”) you define: • The cell with the result you want • The desired value • The cell to change to get to the result you want, the click OK • Let’s see how this works for our coffee wholesaler, which is changing to an • internal quota of $800,000 for the year for Columbian cultured roast coffee • and wants to know how many units it would have to sell to get to that quota. • Using Solver: Goal Seek on Steroids (Multiple Variables) • Goal Seek is a great tool for finding out how much you need to change a single input to generate a desired result from a formula, but it is no help if you want to find the best mix of several inputs. For example, our coffee wholesaler may want to get the gross revenue for Columbian cultured roast up to $800,000 but has the flexibility to change the base rate, the excepted pounds sold in order to reach our goal, or both, and wants to know the most efficient combination. • First step, check to see that you have Solver installed on your Data tab:

  19. Alternative Data Analysis continued • To install Solver • File tab, Options button, Add-Ins menu (left side) • At the bottom, Manage drop down, choose Excel Add-Ins, click “Go” • Add-Ins dialog box – Solver Add-In check box and then OK • Solver will then be added to Excel on the Data tab • Now that it’s installed, let’s see how Solver works with multiple variables to create a solution. To do tis, let’s use the Demo 5 tab in your Demo Workbook. In this scenario, we work in the marketing department of a large company, and we are using Solver to determine the best mix of advertisements to buy, given the following goals of our management team: • Most of all, we want to maximize the number of people who will see our ads • We must buy at least 8 ads in three magazines and at least 10 ads in a fourth • We cannot buy part of an ad (all numbers have to be integers) • We can buy no more than 20 ads in any one magazine • We must reach at least $10,000,000 people • We have an ad budget of $3,000,000 Notice that these constraints are laid out for you in cells D11:G15. Now all we need to do is enter the goal and the constraints into Solver and see what Excel recommends as the best answer. Remember, however, that no tool is a substitute for good judgment!

  20. Alternative Data Analysis continued self-paced skills practice – Solver We work in the budgeting department for a worldwide widget seller, and we are looking at the upcoming Fourth Quarter budgets for each of the four regions in the Skills Practice B tab of the Skills Practice Workbook Excel Level 3. You have decided to fund the regions equally with an expense budget of $750,000. Realizing that many of the budget items are fixed expenses, you know you can adjust travel and entertainment allocations in order to keep the total budget for each region at $750K. Use Solver to suggest the best possible allocation. • On the Skills Practice B tab, note that the communications, equipment, advertising, salaries, and rent are fixed costs. So you will use Solver to change the entertainment and travel amounts in cells G3 through H6 to achieve your goal of a $3M budget. • Other constraints you want to remember are that each region to be equally funded to $750K, that (based on past experience) travel budgets need to be at least $80,000, and entertainment budgets must be at least $93,000. These have been entered in A8:C12 to remind you. • Open Solver and use cell I7 as your Target Cell and set it to a value of 3,000,000 (with the commas). • Use the cell range G3:H6 as your Changing Cells • Add the following constraints: • Set the region totals (I3:I6) to = C9 • Set the entertainment cells (G3:G6) to >= C11 • Set the travel cells (H3:H6) to >= C10 6. Click Solve 7. Solver changes the values to get to the budget you requested ($3M total, $750K per region) and asks if you want to keep the solution or revert to original values. You decide to restore the original values and save the solution values in a separate worksheet. Click Restore Original Values, click Save Scenario, enter Adjusted Budgets as the name. 8. Open the Scenario Manager and generate a Scenario Summary that allows you to see the current values (adding up to a budget of $2.812M) compared with the adjusted budgets (adding up to $3M).

  21. Part Twelve – Conditional Functions • Introducing Conditional Functions • We learned how to apply the SUM, COUNT, and AVERAGE functions for data ranges in Level 2’s Formulas 101 section. These are shortcuts for writing longer formulas. For example to get the monthly average of sales for the year 2015, as in the Demo 1 tab, you could write the formula: • =(C5+C6+C7+C8+C9+C10+C11+C12+C13+C14+C15+C16)/12 • This would yield the answer $286,829.00 • Of course, the shortcut is to just write an AVERAGE function to do all that math for us: • =AVERAGE(C5:C16) • The five most basic Functions are • AVERAGE • COUNT • MIN • MAX • Starting in Excel 2007, you were able to SUM, AVERAGE, or COUNT items in a range ONLY if • the cells in that range satisfied criteria you specified. For example, if you want to count the • number of sales in a month that were larger than $40,000. These tools are referred to as Conditional Functions. and of course the one used far more often than any other: SUM

  22. Conditional Functions continued • Let’s look at an example. In the Demo 2 tab, you can see the sales for a tour company for a recent month. We are going to use conditional formulas to determine total sales for each tour. • Use the COUNTIF function to determine the number of each type of tour sold, being sure to use quotation marks for the condition, as in • =COUNTIF(A5:A24,"Pacific Odyssey") • Use the AVERAGEIF function to determine the mean average price of each type of entry in the month’s sales • =AVERAGEIF(A5:A24,"Old Japan",B5:B24) • Use the SUMIF function to determine the aggregate total (the sum) of each type of tour • =SUMIF(A5:A24,"Pacific Odyssey",B5:B24) • Two arguments • Range to which the condition is applied (also the range which will be counted if the condition is met) • The condition, such as >34 or matching text • Three arguments • Range to which the condition is applied • The condition • The range which is to be averaged if the condition is met • Three arguments • Range to which the condition is applied • The condition • The range which is to be summed if the condition is met

  23. Conditional Functions continued • Let’s fill in the empty columns for our Coffee Wholesaler, on the Demo 3 tab. Specifically, let’s complete the empty fields: • Accounts Receivable • Days since the sale of the product (days between today and the date of the sale) • Write-off (if there is a write off for the Accounts Receivable if the number of the days since the sale is 170 or greater) • Hint: Set the write-off IF function to measure J10>=170 and return a value of “write-off” or “active.” Correcting the write-off IF function so that it does not return a write off or active value when no money is owed requires that we nest two IF functions together: =IF(I10>0,IF(J10>=170,"write-off","active"),"n/a") • Amount of Incentive, if each sale qualifies for a • commission, assuming a 2% incentive kicks in • only for the amount of each sale at least $40,000. Then • we summarize the total incentive we will have to • pay to each of our 3 salespeople in C2:C4 • Let’s summarize the total company’s high volume sales for the six-month period, still defining a “high volume sale” as any sale $40,000 or greater.

  24. Conditional Functions continued CountIfs, SumIfs, and AverageIfs Formulas – multiple conditions Sometimes you may want to create multiple criteria to limit the sum, average, or count. In the last demonstration, we only included one limiting factor (sales equal to or greater than $40,000, for example). But conditional functions can look at multiple ranges and applying limiting criteria. Let’s say that we want to continue with our analysis of high volume sales. We now want to add in the high volume sales information for the 3 salespeople, meaning that we have two conditions to apply: the salesperson name and the size of the deal. It is important to note that you do not have to memorize your arguments. Excel will help you figure out how to write the function if you know what conditions you want to apply. Open the Function Builder by going to the Formulas tab, selecting the proper function library (see the table on the first page of this Part) and select the proper tool. For example, for SUMIFS: Formulas tab  Functions Library group  Math & Trig button  SUMIFS • Follow the steps outlined in the dialog • Be aware of sum/average/count ranges vs. criteria ranges • Avoid entering in the quotation marks – Excel will apply them for you

  25. Conditional Functions continued self-paced skills practice – Conditional Functions Let’s practice writing Conditional Functions, using the Skills Practice Workbook Excel Level 3, go to the Skills Practice X tab In cells H3:H300, write a function that returns the value of the discount if a 20% discount were to be applied only on the Large Envelopes and Boxes (a large size discount). If the expense is eligible for a discount, have the function return the value of the discount, but if the entry is not Large, have the function return a value of “None.” In cell J3, write a conditional function that calculates the average expense for all Boxes. In cell J6, write a conditional function that calculates the total expenses for International Envelopes only. • Tracing Components of Formulas • Once you have identified the error, you can • take steps to fix it by finding the precedents • or dependents. • A precedent is a component of a formula, • which, when combined with other precedents, • creates a result. These are the cells with the • values used to calculate the active cell’s • formula. • Dependents are the opposite. They are • the cells that use another cell’s value in their calculations. • You can test the formulas by visually inspecting the • precedents and dependents by having Excel draw blue arrows to the destination cell. • If the formula is wrong, you can hide the • tracers and correct the formula. • Turn on the tracer in the Formulas tab, • Formula Auditing group

  26. Part Thirteen – An Introduction to PivotTables Excel workbooks can contain literally a million rows of data. When a worksheet is more than 100 rows long, you have to use sorting, filtering, and subtotaling to limit the data you are looking at to meaningful groups. When the data you are looking at is over 200 or 300 rows, you need even more powerful tools to help you organize the data. One limitation of the standard Excel worksheet is that you cannot change how the data is organized on the page. For example, a worksheet in which each column represents an hour in the day, and each row represents a day in the month, and the body of the worksheet contains the total sales for every hourly period of the month, you cannot easily change the worksheet so that it displays only sales on Tuesdays during the afternoon.

  27. Introduction to PivotTables continued • Analyzing Data Dynamically with PivotTables • Excel allows you to gather important data into a single place, where it can be presented for consumption • The standard configuration of the original worksheet cannot be easily changed • Take a look at the Demo Workbook Excel Level 3, Demo 3 tab… • Such a neutral presentation of your data is versatile, but it has its limitations. • While you can use sorting and filtering to restrict the rows and columns shown, it is difficult to change the organization of the worksheet. For example, in our coffee wholesale worksheet, it is not easy to start the columns with the sales person and then show the dates in order for each sale, omitting the region and product. • You also cannot easily flip columns to row and rows to columns • and have the corresponding data line up properly. • The solution is a PivotTable • Easy to set up, but scary to look at • (like Access) • Insert tab, PivotTable button / drop down • The defaults will identify the source data • table and designate a new spreadsheet as • the destination for the Pivot

  28. Introduction to PivotTables continued • When you do this, a blank Pivot opens up with your Field List open • The Field List has all of the column headings and four quadrants • Report filter – applies to the entire report, located in the top, left corner • Column labels – used more rarely, as Pivots are tabular by nature • Row labels – allow you to pivot your view in whatever order suits you • Values – the totals, whether sum, average, count, etc. • Begin to build your Pivot by clicking and dragging your column headings (called • Fields) into one of the four quadrants in the Field List • It is important to note that the order in which you place the Fields into the quadrants • will determine what your table looks like, and how the information is presented • If the Field list disappears, click any cell in the Pivot to bring it back again. • If you have closed it, you can toggle the field list on and off by using the button • in the Show group on the contextual PivotTable Tools Analyze tab

  29. Introduction to PivotTables continued self-paced skills practice – creating PivotTables Let’s practice creating a basic PivotTable using the Skills Practice Workbook Excel Level 3, go to the Skills Practice Y tab Click any cell in the Excel table. On the Insert tab, in the Tables group, click the PivotTable button (not the arrow). The Create PivotTable dialog box opens. Verify that the DailyVolumes table name appears in the Table/Range field and that the New Worksheet option is selected. Click OK. Excel creates a PivotTable on a new worksheet. In the PivotTable Field List task pane, drag the Center field header to the Row Labels area. Excel adds the Center field values to the PivotTable row area. In the PivotTable Field List task pane, drag the Year field header to the Column Labels area. Excel adds the Year field values to the PivotTable column area. In the PivotTable Field List task pane, drag the Volume field header to the Values area. Excel fills in the body of the PivotTable with the Volume field values. In the PivotTable Field List task pane, in the Column Labels area, drag the Year field header to the Row Labels area, and drop it beneath the Center field header. Excel changes the PivotTable to reflect the new organization. Close the workbook without saving it.

  30. Introduction to PivotTables continued • Editing PivotTable Layouts • Once you have created your PivotTable, you are ready to start editing it for content (and later for format) • Report Layout - There are 3 choices for you to use in formatting your PivotTable • Compact form: This is the default for all PivotTables in Excel. It stacks row labels in column A for aesthetic purposes • Outline form: This form allows you to examine a Pivot the same way you might read an outline, from left to right, important drop-down by levels to subcategory • Tabular form: This is the only form available in previous versions of Excel, and it requires subtotals to come at the bottom of each group • You change the layout on the PivotTable Tools Design tab, Layout group, Report Layout button Compact Layout Tabular Layout Subtotals at the top, but this can be changed Subtotals at the bottom Outline Layout

  31. Introduction to PivotTables continued • Basic pivottable manipulation • Change the subtotaling and grand totaling options on the PivotTable Tool Design tab, the Layout group. • Change the style of the Pivot on the Design tab, in the PivotTable styles group, using the More button to display the gallery. Hover your cursor over the thumbnail to see the name of the style and to get a Live Preview • Change the format of numbers in the values quadrant using the Value Field Settings. Here you can • Change the name of the field • Change the calculation in the values quadrant • Change the format of a number • The Two Key Features of PivotTables: • They pivot, as well as filter • They are refreshable, as they are linked to the source data

  32. Introduction to PivotTables continued • self-paced skills practice – Putting it All Together with PivotTables • Scenario - You are the owner of an art supply store called Portfolio, based in Vancouver. You sell products at the store as well as online. You also take orders by phone from your catalog customers. You have been using Excel to maintain a sales summary for the year’s sales of paint, brushes, and paper. You want to create a PivotTable to analyze and graph sales in each category by month and by type of order. • TASKS • Open the Skills Practice Workbook Excel Level 3, go to the Skills Practice Z tab • Create a Pivot Table on a new worksheet that sums the sales amount for each category in the rows and each type of sale in the columns. • Change the layout of the Pivot to a tabular layout • Put the Month field in the Report Filter and pivot the sales data to show April only • Turn off the grand totals for the columns and display the grand totals for rows only (to the right of the Pivot) • Format the sales values using the Currency format with no decimal places • Format the entire PivotTable using Pivot Style Dark 7 • On the source data worksheet (“PivotTables Practice” tab), add $10,000 to the April Online Paint sales, so that the new total is $53,225. Update the PivotTable to • reflect this change. • In cells A56:D64, add the July totals as follows, then • update the PivotTable to include the new additions.

  33. AppendixExcel Limitations & SpecificationsGetting Certified in ExcelPast Certification Exam Questions

  34. Appendix – Excel Limitations & Specifications • MICROSOFT EXCEL SPECS • Available online at https://support.office.microsoft.com/client/Excel-specifications-and-limits-CA36E2DC-1F09-4620-B726-67C00B05040F?NS=EXCEL&Version=16&SysLcid=1033&UiLcid=1033&AppVer=ZXL160 • The site above covers a very large number of specifications, including:

  35. Appendix – Getting Certified in Excel • MICROSOFT OFFICE SPECIALIST EXAMS • Available from Certiport (www.certiport.com), Excel 2007, Excel 2010, and Excel 2013 • In the MOS 2007 and 2010 exams, individuals are presented with a series of situations where they are asked to perform certain tasks in Excel. These randomly-sequenced, performance-based scenarios stand independent from each other. • With the new MOS 2013 exams, testing candidates are presented with a project that they must complete. Rather than being a series of required tasks, individuals now must complete a project that delivers a better indicator of real world Excel 2013 use. As such, individuals are measured on their performance at a deeper skill level. • EXPERT EXAMS • Sharing and Maintaining Workbooks • Workbook settings, properties, and data options • Protection and change tracking • Cell comments • Scenarios and What-If tools • Advanced Formulas and Functions • Audit Formulas • Conditional Functions • Lookup Functions • Presenting Data Visually • Advanced chart features • Data analysis • PivotTables and PivotCharts • Macros • CORE EXAM • Creating Cell Data, including using Autofill • Formatting Cells and Worksheets • Merge cells • Hide and unhide • Managing Worksheets and Workbooks • Window & workbook views • Basic Formulas and Functions • Order of precedence • Cell references & ranges • Logical functions • Charts, Graphs, and SmartArt • Collaboration (incl. Cell Comments) • Analyzing and Organizing Data • Filtering • Sorting • Conditional Formatting

  36. Getting Certified continued MOS 2010 Study Guide by John Pierce and Geoff Evelyn • PREPARING FOR THE EXAM: USING THE STUDY GUIDES • Step-by-step exercises and practice questions for building and strengthening your skills • Real world scenarios • Easy to follow screenshots • Practice files online for all the book’s lessons • End-of-chapter objective reviews • Voucher for a free practice test to assess your exam readiness (take an online skills test—looks and scores like the certification exam except you get feedback on your answers). Identify where you need to focus your study time • Fully searchable e-Book MOS 2013 Study Guides Excel Core exam by Joan Lambert Excel Expert exams (part 1 and part 2) by Mark Dodge

  37. Getting Certified continued Ready to get Certified in Excel? • Go to Certiport.com • Find testing center (using the locator) • Register on Certiport (so your transcript will be available) • Buy an exam voucher at Certiport • Activate the voucher • Use the voucher within 12 months of purchase • Also use Certiport to • Get the latest information on how to get college credit for your certifications • See the latest research on the importance of certification and how it is used by hiring managers and supervisors • Search for approved study guides and resources you can use to prepare • Find the subject matter breakdowns, so you can see exactly what is going to be tested in each exam • After you get registered and receive your exam voucher • Make an appointment at your local testing center (proctored exam) • Usually the exams are only offered on certain days; there are limited slots and the exams are timed • Closed book exam, help tools disabled • When you begin the exam, you will have to put in your exam voucher and the proctor's name • Computer monitor takes you to partially set-up spreadsheets and you execute the commands to complete the random-order tasks you are asked to do.

  38. Getting Certified continued Tracking Your Certification and Displaying it for Others Certiport keeps records of your progress so that you and others whom you choose can see your results, thus proving your certifications. You may select from two view options: Authorized View- displays information available to those whom you grant access to your Digital Transcript. Personal View - displays similar information but also enables you to view your exam score report by clicking the exam links. Authorized Viewers do not have access to your score reports. There is also a place on the MyCertiport page that allows you to input your certification goals and track your progress against them. The site also provides some self-assessment tools to be sure you are ready to take a certification exam or if you need more studying, as well as test taking tips provided by successful candidates.

  39. Appendix – Past Certification Exam Questions self-paced skills practice – Actual Certification examples For questions 1-3, use the SummaryConditionworkbook in the Certification Examples file. Open the SummaryCondition workbook, and in cell F2, create a formula to count the number of Berry Bushes products in the cell range A2:A38, which has been defined as the named range Category. Then in cell F8, write a formula that counts the number of Flowers products in the Category range that are more than $20.00. In cell F4, sum the value of all Flowers products in the Category range. Then in cell F12, add up the cost of all carnivorous products in the Category range that are less than $7.00. In cell F6, average the price of all Herbs products in the Category range. Then in cell F10, calculate the average cost of Carnivorous products in the Category range that are named Bladderwort. For questions 4-5, use the LookupFormulaworkbook in the Certification Examples file. Open the LookupFormula workbook and create a formula in cell C12 to find the last name of employee number 5. In cell C14, write a formula that finds the street address of the employee named Kirk. For questions 6-9, use the Grouping workbook and the Subtotals workbook. Open the Grouping workbook. You have the individual product names in column A and the Category names in column B. Use the selling price in column C and the margin in column F to determine the Cost of each product in column D and the Profit dollars for each product in column D. In cell I1, write a formula that returns the average margin for all of the product sales. Once you have the value, hard code it into the cell. Add subtotaling that groups the Categories so you can see the total Sales, Cost, and Profit for each. Close the Grouping workbook and open the Subtotals workbook. Add subtotals the SalesByCategroy worksheet by adding summary rows that total product sales by category. Next, add a grouping to hide column A. Then, hide all the rows other than those containing subtotals. Finally, remove the outline without removing the summary (subtotaled) rows.

  40. Past Exam Questions continued self-paced skills practice – Actual Certification examples continued For questions 10-11, use the ConditionalFormattingworkbook in the Certification Examples file. Open the ConditionalFormatting workbook. On the Orders worksheet, format Seattle in the City column with red text. On the Details worksheet, conditionally format the values in the Extended Price column using the Three Arrows (Colored) icon set. Then add over the top of the arrows the blue data bars for the same values. Finally, fill all cells containing values over $150 with pale yellow. Still on the Details worksheet, edit the formatting rules for the Three Arrows formatting only to that the green upward arrow is applied for values greater than or equal to $40, and a red downward arrow is applied for values less than $10. For questions 12-14, use the Password workbook in the Certification Examples file: Open the Password workbook and set the password to open the workbook to W0rkB00k (using a zero (0) in place of each letter “o”). Do not include a password to make changes. Save the workbook to your personal file as “MyPassword” and close the workbook, then re-open it to test the password protection. Remove the password protection for the workbook and re-save it. Then password protect the efficiency worksheet with the password W0rkSh33t (using a zero (0) in place of the letter “o” and a three (3) in place of each letter “e”), but leaving unprotected the cells C6:G6. Add a cell comment to cell C6 asking the Finance team to fill in the final 2011 efficiency baselines for all 5 cells. Save the workbook. You have changed your mind and now decide that you want a password applied to fill in cells C6:G6, but that it should be different from the one used to protect the worksheet. Add the password ExcelL3 to the range C6:G6 while continuing to apply the password W0rkSh33t to protect all other cells on the worksheet. For questions 15-18, use the Finishing workbook in the Certification Examples file: Open the Finishing workbook and configure the SalesByCategory worksheet so that printing will print only columns B and C. On the Orders tab, insert a page break before row 31. Then review the page breaks and adjust accordingly to ensure that only columns A through D will appear on the first page.

  41. Past Exam Questions continued self-paced skills practice – Actual Certification examples continued Select the JanFeb worksheet. Changing only the layout to ensure that all the contents of the worksheet will print horizontally on two pages of letter-sized paper. Return to the Orders tab. Create a header that will print on all of the pages of the of the Orders worksheet except the first. On the left, enter a dynamic date, in the center, enter the name of the workbook, and on the right, enter the page number. Return to Normal view and then change the center section of the header to reflect the name of the worksheet instead of the name of the workbook. For questions 19-23, use the Charting workbook in the Certification Examples file: Open the Charting workbook and select the Sales (1) worksheet. Plot the data as a simple 2-D Clustered Column chart. Then switch the rows and columns. Next, change the chart to a 3-D Clustered Column chart. Change the layout of the chart to Layout 1, Style 34. Change the chart style to the Subtle Effect – Accent 3 shape style. On the Sales (2) tab, increase the size of the chart until it occupies cells A1:L23, then move it to a new worksheet named Sales Chart. Go to the Sales (3) tab and change the October sales amount for the Flowers category to 888.25. Then add the November data series to the chart. Change the way the data is plotted so that you can compare sales for the two months. Go to the Air Quality tab. Add data labels to the pie chart which show the name of each air quality category and the percentage (not decimal) of days for the year the air was categorized for each particular quality level. Change the color of the data labels as necessary to show up better against the darker pie slices. Extract the pie slice “Unhealthy for Sensitive Groups” slightly so that it stands out from the rest of the pie. Create a new, blank tab. Insert a Basic Bending process diagram. Add the following text to the shapes: Take Order; Create invoice; Fulfill Order; Ship Order; Order Received. Then apply the Powdered style and one of the Colorful color schemes.

More Related