Excel 2010: P3: Business Intelligence

Chapter 22: Using Automatic Subtotals

Adding Automatic Subtotals Follow the steps: Sort the data by your group field meaning sort the data per column Select one cell in your data set Data, Outline, Subtotal, displays the Subtotal dialog box In the Subtotal dialog, change the At Each Change In drop-down to reflect your group field Ensure that Use Function is set to Sum For each field that you want totaled, select the field in the Add Subtotal To List You can select Page Break Between Groups Ok. Then Excel adds a subtotal between each group. Excel inserts extra rows between groups and adds subtotals At the very bottom of the data set, Excel has added a Grand Total row. Working with the Subtotals Group and Outline buttons were added automatically by the Subtotals command. A. Showing a One-Page Summary with Only the Subtotals Click the 2nd button on the Group and Outline buttons to hide all the detailed rows, leaving only the customer subtotals and the Grand Total row. B. Sorting the Collapsed Subtotal View So the Largest Customers Are on Top You can sort the data when it is collapsed Excel brings all the collapsed detail rows along with the subtotal row during a sort C. Copying Only the Subtotal Rows Copy and paste the data with the subtotal rows in a new worksheet, Excel copied all the hidden rows as well and it no longer has the group and outline symbols Go To Special, Visible Cells Only. Then add the subtotals Collapse to the subtotal only view by the Group and Outline button in the clicking the #2. Select the entire range of collapsed subtotals Open the Find and Select drop-down from the right side of the Home tab, Select the Go To Special command. Excel displays the Go To Special dialog (Alt+;) to reduce your selection to items meeting a certain criteria. In the Go to Special dialog, select Visible Cells Only. Click OK. Excel will deselect all the hidden rows. Click Ctrl + C to copy those rows, which has been selected each visible row separately. Select a blank section of the workbook, use Ctrl + V to paste only the subtotals. After pasting, you have static values instead of formulas D. Formatting the Subtotal Rows Total value after the key field column is bolded The other subtotal columns get a formula that uses the SUBTOTAL function. Strangely, the cells containing the formulas in each subtotal row are not bolded. The hidden detail rows are formatted as well. How to bold the SUBTOTAL rows? Add the subtotals to a data set as described previously. Click the #2 Group and Outline button to collapse the data set to show only the subtotas Select the first subtotal row down to the grand total row. In the current data set, select from D67 through H592. Hold down Alt and press semicolon. Excel selects on the visible rows, which are only the subtotal rows. Apply any formatting desired. Click the #3 Group and Outline button to show all the detail rows E. Removing Subtotals Select one cell in the subtotaled data set Go back to the Subtotals command In the lower left of the Subtotals dialog box click the button for Remove All. Using Specialty Subtotal Techniques A. Summing Some Columns While Counting Another Column Go to the Subtotals dialog, specify the Use Function to Count, and specify the column for the subtotal and unclick the Replace current subtotals. The subtotal dialog box, choose to sum all the numeric columns. Also, choose to sum one text column. Examine the formula in the subtotal row of the one of the text columns. Select the entire text column Use Home, Find & Select, Replace. In the Find and Replace dialog, choose to replace every every (9 with a (3. B. Adding a Blank Row After Each Subtotal The Insert Rows command adds a blank row above the selected cells. Go To Special dialog, twice: to select the visible cells and to select the nonblank cells. Follow the steps: Add subtotals as described previously. Click the #2 Group and Outline button to show only the subtotal rows. The range should extend from the first subtotal row down to the last subtotal row, not including the Grand Total. Type Alt + ; to select only the visible cells in that column Type 1 and press Ctrl + Enter to enter the number 1 next to each subtotal row. Click the #3 Group and Outline button to show all the detail rows. Select the only visible cells in that column Select the first cell containing a 1 in the new column Select Home, Insert, Insert Cells, Shift Cells Down, OK This moves the 1s to the first row of each group. Select Home, Find & Select, Go To Special, In the Go To Special dialog, select Constants and click OK. From the menu, select Home, Insert, Insert Sheet Rows. You have added a blank row after every subtotal row. Select the new column and press the Delete key to clear the 1s from that column. C. Add Subtotals by Two Fields Add subtotals in the column? In the Subtotal command, clear the Replace Current Subtotals checkbox Sort By the columns Add subtotal to the outer group first Run the Subtotals command. Specify Change In Product Clear the Replace Current Subtotals checkbox Then you will have Group and Outline buttons

Chapter 29: Tour of the Best Add-Ins for Excel

Charting Utilizes from Jon Peltier Offers the PTS Chart Utilities add-ins that enable you to easily make the following types of charts: Waterfall Charts Box and Whisker Charts Cluster Stack Charts Marimekko (Mosaic) Charts Dot Plot Charts Cascade Charts Peltiertech.com/Utility/ Simplifies the creation process for several unusual chart types Creating Dashboards by Using Speedometer Chart Creator You can create dashboards by using the Speedometer Chart Creator from Mala Singh. It has various colors around the perimeter of the speedometer. A pointer indicates today's value, and an alternate pointer shows yesterday's value, so you can see whether there has been improvement. Add Labels to XY Charts Lets you specify any range to use as the labels for an XY chart, as well as control the positioning and color of the labels. Appspro.com/ Utilities/ChartLabeler.htm Loading PDF Data to Excel by Using Able2Extract Able2Extract solves the problem in retrieving data from the columnar format. You simply open a PDF file in Able2Extract, and you can convert the contained data to Excel with one click. If you need absolute control, you can specify regions so that, for example, titles and headers beyond page 1 are not imported. The professional version of Able2Extract can scanned image of the original file to import the data into Excel Customizing the Ribbon Using CustomizeRibbon To add new groups to any tab or even add a new tab to the ribbon: View tab, Customize Ribbon icon Accessing More Functions by Using MOREFUNC.DLL 66 new functions: LastRow-This function finds the last filled row in any column PageNum-This function finds the page number of any cell SheetName-This function finds the name of the current sheet WordCount-This function finds the number of words in text NBText-This function spells out a number as text in any of 13 languages. Iso. Weeknum-This function finds the ISO-compliant week number of a day. CountIf.3D—This function is the same as Countif but for 3D references Xcello5.free.fr/ To merge all workbooks in a folder Rondebruinnl/addins.htm General Purpose Utility Suites Fall one of two camps: (1) focuses on tasks for formatting worksheets. They might offer a way to format every nth row in a certain color or convert a range of uppercase data to proper case and so on. (2) designed to simplify data analysis ASAP Utility offer a noncommericial asap-utilities.com JMT Utilites andrewsexceltips.net/JMT%20Full%20List.htm J-Walk's Power Utility Pack spreadsheetpage.com/index.php/pupv6/home A. Utilities for Data Analysis Tasks Two other utilities focus on data analysis tasks. The tagline for DigDB offers to let you do Access with Excel. When you have to match and merge massive amounts of data in Excel, these utilities simply the process: DigDB offers a suite of 40 separate Excel add-ins that simplify data analysis. Download a trail from digdb.com Easy-XL offers a similar integrated suite of data processing commands, including support for macro recording. Download a trial from easy-xl.com

Chapter 27: Automating Repetitive Functions Using VBA Macros

Checking Security Settings Before Using Macros A. Enabling VBA Security To enable VBA security: 1. Select File, Excel Options to open the Excel Options dialog 2. Select the Customize Ribbon category. In the right side list box, select the Developer tab checkbox 3. Click OK to exit the Excel Settings dialog. You now have a Developer tab on the Ribbon 4. On the Developer tab, click Macro Security in the Code group. The Security dialog appears. 5. In the Security dialog, change the Macro Settings to Disable All Macros with Notification. Whit this setting, Excel alerts you whenever you open a workbook that has macros attached (there is the Melissa Virus that you need to be aware of. For the month and day and hour and minute make sure they do not match to bypass the virus from spreading. 6. You will have a pop up box when you open the document. If you are the one who created the document, then you can enable the content to revise. Recording a Macro To record a macro, follow the steps: On the Developer tab, select Record Macro In the Record Macro dialog box, type a name for the macro without spaces Shortcut Key: design the shortcut. Store Macro in: The personal macro workbook is a special workbook designed to hold general purpose macros that might apply to any workbook. Then click OK to close the Record Macro dialog Turn on relative recoding by clicking the Use Relative References icon under the Code group. Perform the actions you would like for the other cells Click the Stop Recording, then save the workbook before testing the macro You can test your macro with the shortcut you assigned earlier. Case Study: Macro for Formatting for a Mail Merge Before recording a macro, you need to picture what you will perform because Excel will repeat these steps for your data. You do this by using shortcuts. To assign the macro, you conduct the Ctrl + J for the first set of data then the recorded macro will continue on for the entire table. If you made a mistake, then you can quickly fix your records by holding down the ctrl + J. A. How Not to Record a Macro: The Default State of the Macro Recorder You do not rely on your first recorded macro to perform the rest of your data because the macro will literally record the cell references instead of the pattern. B. Relative References in Macro Recording In the Developer tab, select the Code group, click the Use Relative References You would need to record the macro with the Use Relative References turned on to refer to the pattern instead of the cell references. Then you can perform this to your data for the three desired fields. C. Starting the Macro Recorder Click the Record Macro icon in the Code group under the Developer tab The Record Macro dialog appears, fill it out, in the description you can enter the date and the individual who recorded the macro. Click Use Relative References Conduct the pattern that will be used throughout your data with cut, paste, delete rows shortcuts Stop the recording D. Running a Macro To run a macro, follow these steps: Open the Macro dialog Select the Marco that you named previously, then click the Run button. The macro fixed the first record. Conduct the shortcut to run the selected macro, then the second record will perform After confirming the correct pattern, you can conduct the shortcut by holding it down for all of your data then the data will be ready to mail merge. Everyday-Use Macro Example: Formatting an Invoice Register You can set up a macro for formatting your data into a table A. Using the End Key to Handle a Variable Number of Rows The End Key can have many options with the arrow keys in order to prevent any mistakes for opening a file with the same pattern after the data is inputted, you can follow the steps to have a general idea on how to conduct this macro for every workbook open: Turn on the absolute recording To display the Go To dialog, refer to the last cell in the worksheet, A1048576 Turn on relative recording Have a total to this worksheet for the first empty cell after your data and include your formula in the cell with absolute reference for the field. You can select all the cell, then format it by using the ribbon After doing so, you turn on the absolute recording to continue on recording Use Save As to reflect the date then click Stop Recording button Before recording the macro, have an open blank excel labelled as MacroToImportInvoices.xlsm There will be two limitations with the absolute and relative recording: The macro will attempt to overwrite the new file. The macro will always want to open the same file everyday, unless your register produces a file with the same folder. B. Editing a Macro To edit a macro: Click the Macros button in the Code group, under the Developers tab Then the Macros dialog appears, select Macro and click the Edit button The Visual Basic Editor is launched. Three panes are displayed: Code pane-The actual lines are in the code pane Project Explorer pane-Shows every open workbook, within the workbooks you can view the objects for every worksheet, an object for the workbook, and one or more code models. Ctrl + R Properties pane-useful if you design custom dialog boxes, press F4. Understanding VBA Code—An Analogy VBA is an object-oriented language [consist objects (noun: cell, row, column, worksheet), methods (verb: add, delete, and clear), collections (plural noun), arguments (adverb), and properties (adjective)] A. Comparing Object.Method to Nouns and Verbs Object-dot-method. B. Comparing Collections to Plural Nouns You can refer worksheets (plural noun that is a collection of worksheets) by a number or a name. The "s" indicates you are dealing with a collection instead of a single object. C. Comparing Parameters to Adverbs When you save as, you can specify several options that change how the command is carried out. An adverb modifies a verb like a parameter (factor) modifies a method. The line of code for Save As documents the every option in the dialog box: ActiveWorkbook.SaveAs. Filename:="C:\Something.sls",_ FileFormat:=xl1OpenXMLWorkbookMacroEnabled,_ CreateBackup:=False In the Tool drop-down menu, click Options, then you will see a dialog with four additional options: Always create a backup, Password to Open, Password to modify, Read-only Recommended Parameters have some potentially confusing aspects. Most of the time, there is a space following the method and then a list of one or more ParameterName :=ParameterValue constructs, separated by a comma and a space. However, there are a couple exceptions: If the result of the method is acted upon by another method, the list of parameter is enclosed in parentheses, and there is no space after the method name. One example is when you add a shape to a worksheet and then Excel selects the shape. The code to insert the shape would uses the AddShape method and five named parameters: ActiveSheet.Shapes.AddShape Type:=msoShapeRectangle,_ Left:=60, Top:=120,_ Width:=100, Height:=100 The macro recorder will record the process of adding the shape and then selecting the shape. Because the .Select method is acting upon the result of the .AddShape method, you see the parameters for the AddShape method surrounded by parentheses: ActiveSheet.Shapes.AddShape (Type:=msoShapeRectangle,_ Left:=60, Top:=120,_ Width:=100, Height:=100) .Select The only difference in this formula is the parenthesis and the .Select and the method is acting upon the method. You may not know the default order of the AddTextEffect, so you do not need to input all the values in the formula. For help, click anywhere in the formula and then press the F1 key. The Help topic for each method helps decode the default order of the parameters. ActiveSheet.Shapes.AddTextEffect(msoTextEffect2, "Test", _ "Arial Black", 36#, msoFalse, msoFalse, 323.25, 142.5). _ Select The Help topic lists the valid properties associated with the object and the valid methods that can be used on the object. Accessing VBA Help You must have the VBA Help file install in your machine. D. Comparing Adjectives The final construct in VBA is the adjective used to describe an object. In VBA, adjectives are called properties. Think about a cell in Excel with a formula in it. The cell has many properties. These are some of the most popular properties: Value (the value shown in the cell), Formula (the formula used to calculate Value), Font Name, Font Size, Font Color, Cell Interior Color Selection.Font.Bold=True Selection.Value = 100 then Selection.Font.Bold = True Properties are generally construct with the dot construct, and they are almost always followed by = . PropertyName = value E. Using the Analogy While Examining Recorded Code Worbooks.Open Filename:="C\ Invoices.xls" The parameter is Filename Rows("1:1").Select This will run faster than before Selection.Font.Bold = True The second line then sets the Bold property of the Font property. Using Simple Variables and Object Variables You can add variables when you edit macro instead of recording a macro. FinalRow = Range("A1048576").End(xlup).Row TotalRow = FinalRow + 1 You can assign a variable to the last row instead of repeating pressing the End key. Then the FinalRow and the TotalRow are variables that hold a single value for the grand total per range. Every row will have its total for the number of ranges. Object Variable can be used to represent any object such as worksheet, chart or cell. Whereas a simple variable holds one value, an object variable holds values for every property (can many properties) associated with the object. Object variables are declared using the Dim statement and then assigned using the Set statement: Dim WSD as worksheet Set WSD = Worksheets ("Sheet1") This allows the user to easily refer to WSD than to ActiveWorkbook.Worksheets("Sheet1") because you have defined the workbook AND the feature shows a list of valid methods and properties for the object. A. Using R1C1-Style Formulas Office Button, Excel Options, Formulas R1C1 Reference Style. The macro reader records formulas in this style. This is the A1-style formula: Range ("F2").Formula = "= D2 + E2" Range ("F2").Copy Destination: = Range ("F3:F15") This is the R1C1 style formula: Range ("F2:F15").FormulaR1C1 = "=RC[-2] + RC[-1]" It is best to use the R1C1 style formula because you will use one line of coding. Also the formula is identical in one field unlike the A1 style formula you have the cell references. To learn this, you can set up the style with conditional formatting when setting up VBA. B. Fixing Calculations Errors in Macros The R1C1 records the Macro properly However, you may still need to fix the formula when you want to set a pattern instead of referencing a cell. Customizing the Everyday-Use Macro Example: GETOPENFILENAME and GETSAVEASFILENAME File Open or File Save As dialog: FileToOpen = Application.GetOpenFileName ( _ FileFilter:="Excel Files,*.xl*", _ Title:="Select Today's Invoice File Open the file specified in the variable: Workbooks.Open Filename:=FileToOpen To ask for the filename to use in saving the file, use the code: NewFileName = Application.GetSaveAsFilename( _ Title:="Select File Name for Today") ActiveWorkbook.SaveAs Filename:=NewFileName,_ FileFormat:=xlOpenXMLWorkbookMacroEnabled To use the macro every day, you will reference and select cells, please refer to p.841 From-Scratch Macro Example: Loops, Flow Control, and Referring to Ranges A macro that examines each row and outputs a different answer. A. Find the Last Row with Data FinalRow = Range ("A65536").End (xlUp).Row To find the last arrow with data in the column. FinalRow = Cells(Rows.Count,1).End(xlUp).Row The following line of code finds the last row in Column A with a nonblank value Application.Rows.Count returns the total number of row available in the current worksheet. B. Looping Through All Rows Sub WriteSquares() For x = 1 To 10 Range("A" & x).Value = x Range ("B" & x).Value = x *x Next x End Sub In columns A and B, you will have your data If you will be deleting rows from a range of data, it is important to start at the bottom and proceed to the top of the range. You would use -1 as the step value: For x = 100 to 1 step -1 C. Referring to Ranges The macro recorder uses the Range property to refer to a particular range. You might see the macro recorder refer to ranges such as Range ("B3") or Range ("W1:z100"). The loop code shown in the preceding section emulates this style of referring to ranges. On the third time through the loop, this line of code would refer to Cell B3: Range ("B" & x).value = x * x However, how would you handle looping through each column? If ou wanted to conitunue using the Range property, you need to jump through some hoops to figure out the letter that is associated with Column 5: For y = 1 to 26 ThisCol = Char(64+y) Range(ThisCol & 1).value = ThisCol Next y This is a great method, if you are using 26 or fewer columns. However, if you need to loop through all the columns out to Column XFD, you will spend all day trying to write the logic to assign the column label. The solution is to use the Cells property instead of the Range property. Cells require that you specify a numeric row number and a numeric column number. For example, Cell B3 is specified as follows: Cells (3,2) If you need to refer to a rectangular range, you can use the Resize property. Resize requires you to specify the number of rows and the number of columns. For example, to refer to W1:Z100 use this: Cells(1,23).Resize(100,4) To make every other column bold: For y = 1 to 100 step 2 Cells(1,y).Resize(200,1).Font.Bold = True Next y D. Combining a Loop with FinalRow After finding the last row in the data and assigning it to a variable, you can specify that the loop should run through FinalRow: FinalRow = Cells(1048576,1).End(xlUp).row For x =2 to FinalRow ' Perform some action Next x E. Making Decisions by Using Flow Control Flow control is the ability to make decisions within a macro. The following sections describe two commonly used flow control constructs: If - End if and Select Case. a. Using the If - End if Construct If you need a macro to delete any records that say Sales Tax. You could accomplish this with a simple If - End if construct: If Cells (x,4).Value = "Sales Tax" Then Rows(x).Delete End If This constructs always starts with the word If, followed by a logical test, followed by the word Then. Every line between the first line and the End If line is executed only if the logical test is true. Now suppose that you want to enhance the macro so that any other amounts that contain service plan revenue are moved to Column F. To do this, you use the ElseIf linke to enter a second condition and block of lines to be used in that condition: If Cells(x,4).Value = "Sales Tax" Then Cells (x,1).EntireRow.Delete ElseIf Cells(x,4).Value = "Service Plan" Then Cells(x,5).Cut Destination:=Cells(x,6) End If You could continue adding ElseIf statements to handle other situations. Eventually, just before the End If, you could add an Else block to handle any other condition that you have not thought about. b. Using the Select Case Construct If you are using many Else If statements all testing the same value, then it makes sense to switch to a Select Case construct. For example, suppose you want to loop throught all the records toe examine the product in Column C. If Column C contains a printer, you want to move the amount in Column D to a new Column E. Scanner revewnue should go to a new Column F. Service plans go to a new column H. Sales tax goes to a new Column I. You should also handle the situation when something is sold that contains none of those products, In that case, you would move the revenue to a new Column G. The construct begins with the Select Case and then the value to check. The construct ends with End Select, which is similar to End If. Each subblock of code starts with the word Case and one or more possible values. If you needed to check for Printer or Printers, you would enclose each in quotes and separate them with a comma. After checking for all the possible values you can think of, you might add a Case Else subblock to handle any other stray values that might be entered in column C. The following code checks to see what product is in Column C. Depending on the product, the program copies the revenue from Column D to a specific column: Select Case Cells(x,3).Value Case "Printer", "Printers" Cells(x,4).Copy Destination:=Cells(x, 5) Case "Scanner", "Scanners" Cells(x,4).Copy Destination:=Cells(x, 6) Case "Service Plan" Cells(x,4).Copy Destination:=Cells(x, 8) Case "Sales Tax" Cells(x,4).Copy Destination:=Cells(x, 9) Case Else ' Something unexpected was sold Cells(x, 4). Copy Destination:=Cells (x, 7) End Select F. Putting Together the From-Scratch Example: Testing Each Record in a Loop You can write a macro that finds the last row, loops through the records, and copies the total revenue to the appropriate column by using the building blocks. Add new headings before running the macro. The macro should use the End property to locate the final row and prefill Columns E through 1 with zeros. Next, it should loop from Row 2 down to the final row. For each record, the revenue column should be moved to tone of the columns. At the end of the loop, the program alerts you that the program is complete, using a Msgbox command. The following is the complete code of this macro: Sub MoveRevenue2() Final Row = Cells(Rows.Count,1).End(xlUp).Row Range("E2",Cells(FinalRow,9)).Value = 0 For x = 2 to FinalRow Select Case Cells (x,3).Value Case "Printer", "Printers" Cells(x,4).Copy Destination:=Cells(x,5) Case "Scanner", "Scanners" Cells(x,4).Copy Destination:=Cells(x,6) Case "Service Plan" Cells(x,4).Copy Destination:=Cells(x,8) Case "Sales Tax" Cells(x,4).Copy Destination:=Cells(x,9) Case Else ' Something unexpected was sold Cells(x,4). Copy Destination:=Cells(x,7) End Select Next x MsgBox "Macro complete" End Sub After running this macro, you see that the revenue amounts have been copied to the appropriate columns. G. A Special Case: Deleting Some Records If a loop is conditionally deleting records, you will run into trouble if it is a typical For-Next loop. Suppose you want to delete all the sales tax records, as follows: Sub ThisWontWork() FinalRow = Cells(Rows.Count,1).End(xlUp).Row For x = 2 To FinalRow If Cells (x,3).Value = "Sales Tax" Then Cells(x,1).EntireRow. Delete Else Cells(x,5).Value = "Checked" End If Next End Sub The first time through loop, x is equl to 2. Cell C2 does not contain sales tax, so Cell E2 has the word checked. A similar result occurs for Row 3 and 4. The fourth time through the loop, Cell C5 contains sales tax. The marco deletes the tax in Row 5. However, Excel then moves the old Row 6 up to Row 5. The next time through the loop, the program inspects Row 6, and the data is now in Row 5 will never be checked. The macro succeeds in deleting tax. However, several rows were not checked, and several extra blank rows at the bottom were checked needlessly. The solutions is to have the loop run backward. You need to start at the final row and proceed up through the sheet to Row 2. When the macro deletes the tax in Row 31, it can then proceed to checking Row 30, knowing that nothing has been destroyed (yet) in Row 30 and above. To reverse the flow of the loop, you have to tell the loop to start at the final row, but you also have to tell the loop to use a step value of -1. The start of the loop would use this line of code: For x = FinalRow to 2 Step -1 The macro you need here represents a fairly common task: looping through all the records to do something conditionally to each record. The following macro correctly deletes all the sales tax records: Sub DeleteTaxOK() FinalRow = Cells(Rows.Count,1).End(xlUp).Row For x = FinalRow To 2 Step -1 If Cells(x,3).Value = "Sales Tax" Then Cells(x,1).EntireRow.Delete Else Cells(x,5).Value = "Checked" End If Next x End Sub For example described here, the macro reader would be almost no help. You would have to write this simple macro from scratch. However, it is a powerful macro that can simply tasks when you have hundreds of thousands of rows of data. Combinations Macro Example: Creating a Report for Each Customer A combination of recorded code and code written from scratch Advanced filter for the selected customers in column A and copy them to a new workbook You add the code for the loop and other simple tasks such as copying the selection to a new workbook. Whenever you encounter a step for which you have never written code, you can leave a comment with question marks. Sub ProduceReportForEachCustomer() ' Define object variables for new workbook ' Suffix of N means New Dim WBN As Workbook Dim WSN As Worksheet ' Define object variables for the current workbook ' Suffix of O means Old Dim WBO As Workbook Dim WSO As Worksheet Set WBO = ActiveWorkbook Set WSO = ActiveSheet ' Find the FinalRow in today's dataset FinalRow = Cells(Rows.Count,1).End(xlUp).Row ' Use an Advanced filter to copy unique customers ' from column A to column H ' ??? 'Find the final customer in column H FinalCust = Cells(Rows.Count, 8).End(xlUp).Row ' Loop through each customer For x = 2 To FinalCust ' Turn on the AutoFilter for this customer ' ??? ' Create a new workbook Set WBN = Workbooks.Add Set WSN = WBN.Worksheets(1) 'In the original workbook, select visible cells ' ??? ' Copy the selection to the new workbook Selection.Copy Destination:=WSN.Cells(3,1) ' AutoFit columns in the new workbook WSN.Columns.AutoFit ' Add a title to the new workbook WSN.Range("A1").Vaue = _ "Recap of Purchases for " & WSN.Cells(4,1).Value ' Save the new book WBN.SaveAs Filename:="C:\" & WSN.Cells(4,1).Value & ".xlsx" 'Return to the original workbook WBO.Activate WSN.Select In the following sections. You need to figure out how to code: • the advanced filter to copy a unique list of customers to Column H • apply a filter to Column A. • select only the visible cells from the filter A. Using the Advanced Filter for Unique Records ' Find the FinalRow in today's dataset FinalRow = Cells (Rows.Count, 1).End(xlUp).Row ' Use an Advanced filter to copy unique customers ' from column A to column H ' ??? For the advanced filter, follow the steps: Turn on the macro recorder On the Data tab, in the Sort & Filter group, click the Advanced icon to open the Advanced Filter dialog. Select the option Copy to Another Location Adjust the list range to refer only to Column A. The copy-to-range will be Cell H1 Check the Unique Records Only box. Click OK The result is a new range of data in Column H, with each customer listed just once. On the Developer tab, click Stop Recording Use the Macros button to select Macro1 and then select Edit The recorded macro: Sub Macro1() ' ' Macro1 Macro ' ' Range("A1:A1001").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ "H1"), Unique:=True Range("H1").Select End sub In the recorded marco, there is no reason to select Cell H1 to delete that line of code. The remaining problem is that the macro recorder hard-coded that today's data set contains 1,001 rows. You might want to generalize this to handle any number of rows. The following code reflect these changes: FinalRow = Cells(Rows.Count,1).End(xlUp).Row Range("A1:A" & FinalRow) .AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("H1"), Unique:=True B. Using AutoFilter When you have a list of customers, the macro will loop through each customer. The goal is to use an AutoFilter to display only the records for each particular customer. Next, finish this section of the code as follows: ' Loop through each customer For x = 2 To FinalCust ' Turn on the AutoFilter for this customer ' ??? To apply an AutoFilter to this section of code, follow the steps: On the Developer tab, select Record Macro On the Home tab, select the icon Sort & Filter ---Filter. Drop-down arrows are turned on for each field. In the drop-down in Cell A1, clear Select All and then select Hip Lawn Corporation Back on the Developer tab, stop recording the macro Use the Macros button to locate and edit Macro2 as follows: Sub Macro2() ' ' Macro1 Macro ' ' Range("A2").Select Application.CutCopyMode = False Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Hip Lawn Corporation" End Sub The macro reader always does too much selecting. You rarely have to select something before you can operate on it. You can theorize that the only line of this macro that matters is the Selection.AutoFilter line. Because you will always be looking at the AutoFilter drop-down in Cell A1, you can replace Selection with Range("A1"). Rather than continually ask for one specific customer, you can replace the end of the line with a reference to a cell in Column H: Range("A1").AutoFilter Field:=1, Criteria1:=Cells(x,8).Value C. Selecting Visible Cells Only After you use the AutoFilter in the macro, you see records for only one customer. However, the other records are still there, but they are hidden. If you copied the range to a new worksheet, all the hidden rows would come along, and you would end up with 20 copies of your entire data set. To select visible cells only, display the Go To dialog. In the dialog, click the Special button and then click Visible Cells Only. However, the shortcut is to press Alt + ; To learn how to select visible cells only in VBA, record the macro: Select the data in Columns a through F Turn on the macro recorder and press Alt + ; Stop the macro recorder. See that the recorded macro has just one line of code Sub Macro5() ' ' Macro5 Macro ' Selection.SpecialCells(xlCellTypeVisible).Select End Sub In your original outline of the macro, you had contemplated selecting visible cells only and then doing the copy in another statement, like this: ' In the original workbook, select visible cells ' ??? ' Copy the selection to the new workbook Selection.Copy Destination:=WSN.Cells(3, 1) Instead, copy the visible cells in one statement: ' In the original workbook, select visible cells WSO.Range("A1:F" & FinalRow).SpecialCells(xlCell TypeVisible).Copy _ Destination:=WSN.Cells(3,1) D. Combination Macro Example: Putting It All Together The following macro started as a bunch of comments and a skeleton of a loop. Sub ProduceReportForEachCustomerFinished() ' Define object variables for new workbook Dim WBN As Workbook Dim WSN As Worksheet ' Define object variables for the current workbook Dim WBO As Workbook Dim WSO As Worksheet Set WBO = ActiveWorkbook Set WSO = ActiveSheet ' Find the FinalRow in today's dataset FinalRow = Range("A60000").End(xlUp).Row ' Use an Advanced filter to copy unique customers ' from column A to column H Range ("A1:A" & FinalRow).AdvancedFilter Action:xlFilterCopy, _ 'Find the final customer in column H FinalCust = Range("H1").End(xlUp).Row ' Loop through each customer For x = 2 To FinalCust ' Turn on the AutoFilter for this customer Range("A1").AutoFilter Field:=1, Criteria:=Cells(x, 8).Value ' Create a new workbook Set WBN = Workbooks.Add Set WSN = WBN.Worksheets(1) 'In the original workbook, select visible cells WSO.Range("A1:F" & FinalRow).SpecialCells(xlCellTypeVisible.Copy _ Destination:=WSN.Cells(3,1) ' Copy the selection to the new workbook Selection.Copy Destination:=WSN.Cells(3,1) ' AutoFit columns in the new workbook WSN.Columns.AutoFit ' Add a title to the new workbook WSN.Range("A1").Vaue = "Recap of Purchase for " & WSN.Cells(4,1).Value ' Save the new book WBN.SaveAs Filename:="C:\" & WSN.Cells(4, 1).Value & ".xlsx" WBN.Close SaveChanges:=False 'Return to the original workbook WBO.Activate WSN.Select Next x End Sub You are able to fill in the sections to copy the customer records to a new workbook. You should have a new workbook for each customer on your hard drive. VBA macros open up a wide possibility of automation for Excel worksheets. Try to design a macro to do the task instead because it takes less time, plus you can save and revise your macros.

Chapter 20: Sorting Data

Introducing the Sort Dialog There are three sort levels: AZ, ZA, Sort by Color Custom Sort: The Sort dialog appears Make sure the My Data Has Headers check box is selected if your data is not in a defined table. In the Sort dialog, for the Sort By select a field and for the Sort On select values. The cells can contain dates, numbers, text to be sorted. You can click on the Add Level button to add more columns to be sorted. Using Specialized Sorting You can sort by case, custom sequence and color, or even to the left-to-right fashion. A. Sorting by Color or Icon Sort by fill color, font color or icon sets This also works with color applied through conditional formatting. For example, you have one column with many values with different colors. With the Sort dialog, you can use add many levels for the values to sort, the first order will be called On Top. You can click the Copy Level to order the values by color with the Order drop down menu. B. Factoring Case into a Sort Excel sort ignores the case of the text. Values that are lowercase, uppercase and any combination are treated equally as well as cell ranges. If you want Excel to consider case-sensitivity, follow the steps: On the Sort dialog, choose options and mark the case sensitivity checkbox. C. Reordering Columns with a Left-to-Right Sort A Left -to-right sort: Ensure the table has headers, select a cell within the table (not the headers), open the Sort Options dialog, which is located on the top right of the box. Select the Sort Left to Right checkbox. In the Sort By drop down menu, it contains a list of row numbers. In summary, add an extra row to specify the correct sequence of the columns and then sort from left to right. Then delete the extra row of the table that is number in the correct sequence. D. Sorting into a Unique Sequence by Using Custom Lists You will only set up a custom list once per computer! Type the correct sequence for the values in a column Select this range Select File, Options and the Excel Options dialog Click the Advanced Group Display section and then select Edit Custom Lists, the Custom Lists dialog appears In the Custom Lists dialog, the bottom section shows the range of cells you selected Then click the Import button, your new list with the corrected column sequence is in the default custom lists. Then press OK all the way through. To resize the columns: Select all cells with Ctrl + a, use home, format, autofit. You can use your custom list sequence with the Sort dialog in the Order drop-down menu. One-Click Sorting Encompasses the entire current region Excel warns that you will sort part of your data and ignore the adjacent data Right click menu or the Data tab You can add the cell color, font color, or icon on the top in the one click sorting which will be explained in the next chapter. A. Sorting by Several Columns using One-Click Sorting When you have a column with the same value, you can reverse orders with the two columns shown in the table: Region (AZ) and Product (ZA). Sorting Randomly =RAND() calculates a random decimal value between 0 and 1. Double click the handle. Select AZ to sort the values into a random sequence. Hidden rows are not re-sequenced in a sort Excel calculates the range after sorting if the cell contains formulas outside of the table. Sorting with Blank Columns Select the header of your table, From the Data tab, click the Sort button, the Sort dialog appears In the Sort dialog, select the My Data Has Headers check box The Sort By column contains a mix of column headings and placeholders for blank headings. Choose the proper heading from the drop-down, then click OK, and the entire data is properly sorted.

Chapter 21: Removing Duplicates and Filtering

Remove duplicates with filtering Filtering Records Filter works with any range of data with headings, ranges that have been defined as well as regular ranges. You can also filter text columns that contain a value (conceptual values , ect.) rather starting and ending with a value. Filter by colors, text columns, number columns, date values, by selection. The columns must have special text, number or date formatting uniformly to one column. Excel offers only the special filtering type for the value type that occurs more frequently in the column that may have different types. A. Using a Filter You need to select one cell in your data range before clicking the filet. Your data cannot have any blanks columns nor rows before filtering. The filter drop down now features a multi-select list as well as special filters. B. Selecting One or Multiple Items from the Filter Drop-Down Select the Filter Drop-Down, then click Text Filters for options for filtering, you can also select the values in the Filter Drop Down. ☺ C. Identifying Columns with Filters The columns will have filters D. Combining Filters Two filters can be applied to the same column. E. Clearing Filters Clear Filter from Column - this is from the filter drop down and this leaves the filter on in other columns. Choose a different filter - this is located in the filter drop down From the ribbon - clear the column from the filer, but leaves the filter feature to later select the values. F. Refreshing Filters When you add new rows or edit data or formulas/ functions in other workbooks, filters do not automatically filter. Reapply a filter by right a cell and then select Filter, Reapply. G. Resizing the Filter Drop-Down To resize the filter drop-down, hover the mouse to the bottom right corner where you see the three dots and expand. H. Filtering by Selection-Hard Way MS Access has offered a Filter by Selection icon in the right click menu (filter by value, color, font color, or icon) I. Filtering by Selection-Easy Way Add the AutoFilter icon to the Quick Access Toolbar by clicking on the File, Options, Quick Access Toolbar, then scroll down to All Commands, then add the Customize Quick Access Toolbar, Add AutoFilter. This is Filter by Selection. J. Filtering by Color or Icon Works for the cell color, font color or the icon in the cell. K. Handling Date Filters Groups the dates into hierarchical groups. You can turn off the grouping in the File, Options, Advanced category, scroll down to the section for Display for This Workbook. Next, select a workbook and then clear the check box for Group Dates in the AutoFilter Menu. Click the plus sign to expand the grouped dates. L. Using Special Filters for Dates, Text, and Numbers Determine if the column contains mostly text, dates, or numeric values. Text: Begins With, Ends With, Contains, Does Not Contain, Equals, and Does Not Equal. You can use an asterisk (*) for any number of characters or a question mark (?) to represent a single character. Numeric Values: Top 10, Above Average, Below Average, Between, Less Than, Greater Than, Does Not Equal, and Equals., top 10 percent of items. Change the number 10 to any number. Dates: Before, After, or Between a particular day, week, month, quarter or year. The special filters also include Year to Date or All Dates in a particular period, as shown in Figure 21.11. Date Filters, All Dates in the Period, Quarter 3 The Custom AutoFilter dialog, allows you combine two conditions by using an AND or OR clause. Sorting Filtered Results The Filter drop down provides choices to sort a column. After applying the Top 10 filter to Column F, Excel shows the top 10 values, but they are unsorted. Excel does not filter the hidden rows. A. Totaling Filtered Results SUBTOTAL function sums the visible rows from the data set that has been filtered. You can use this function to find the count, average, minimum, and maximum as well as other calculations on the visible rows. Cauton on the COUNTIF function with the SUBTOTAL, use another column to copy the visible rows for an accurate result. B. Formatting and Copying Filtered Results Rows that are hidden using Hide Rows will often be included when you copy or format a range that contains those rows. You must use Alt + to narrow your selection to only the visible rows. To highlight all rows matching criteria by changing the background color of the cell: Select one cell in the unfiltered data set that matches the proper criteria Click the Filter by Selection icon in the Quick Access Toolbar Select the first visible cell below the headings Press Ctrl + Shift + Down Arrow and then Ctrl + Shift + Right Arrow to select all the cells below the heading Format the cells as desired Select Data, Filter to remove the filter and show all rows. You will find that only the rows that were visible during the filter have the new formatting. Using the Advanced Filter Command Advanced Filter can be used in 8 different ways. There are three options in the Advanced Filter Dialog, and depending on your choices for those three options, you can have possible combinations. 1. You can choose to either Filter in Place or Copy to a New Location 2. You can choose to filter with a criteria range or without any criteria 3. You can choose to return all matching values or only the unique values. If you choose to copy records to a new location, you can either copy all the input columns in order or specify a subset of columns and/or a new sequence of columns. You can build a simple filter for one column. You can combine any number of filters for multiple columns. You can build incredibly complex filters, using any formula imaginable. Alternatively, you can not use no criteria at all. Using no criteria is common when you using Advanced Filter to extract unique values or when you want to use Advanced Filter to reorder the sequence of columns. Follow the steps to use the Advanced Filter on a data set: 1. If you are using criteria, copy one or more headings from your data set to a blank section of the worksheet. Under each heading, list the value(s) that you want to be included 2. If you are using an output range and want to reorder the columns or include a subset of the columns, copy the headings into the appropriate order in a blank section of the worksheet. If you want all the original columns in their original sequence, the output range can be any blank cell. 3. Select a cell in your data range 4. Select Data, Sort and Filter, Advanced. 5. Verify that the list range contains your original data set 6. If you are using criteria, enter the criteria range. 7. If you want to copy the matching records to a new location, select Copy to Another Location. This enables the reference box for Copy to. Fill in the output range. 8. If you want the output range to contain only unique values, click Unique Records Only. If your output range contained a single field, a list of the values in that field is displayed that match the criteria. If your output range contains two or more fields, every unique combination of those two or more fields is displayed. 9. Click OK to preform the filter You can build advanced filter criteria that can ask for a range of values. It is unlikely you will want to filter to the customer with exactly 7,553 in sales. However, you might want to filter to invoices that are over $5,000 in sales. To set up this criteria, type Sales into Cell K1. In Cell K2, type the text > 5000. When you issue the Advanced Filter, Excel returns all invoices in excess of $5,000. Use the Advanced Filter dialog, and enter the Copy to another location with the Copy to cell named ranges. Using Remove Duplicates to Find Unique Values As you analyze transactional data, you often want to find the number of something in the data set. Advanced Filter, IF function, Pivot Table Remove Duplicates tool You can create an extra column for the customer id for duplicated transactions. Follow these Steps: • Copy the data set to a blank section of the worksheet • Leave a blank column in between real and copy data • Select a single cell within the data set • Data tab, in the Data Tools group, select Remove Duplicates. Excel expands the selection to include the entire range. In the Remove Duplicates dialog, Excel predicts if your data has headers. This dialog also shows a list of all the fields in the data set. • Because you're interested in a unique list of customers, click the Unselect All button to clear all check boxes and then select the customer field (one column) as shown. A. Removing Duplicates Based on Several Columns Select Home, Conditional Formatting, Highlight Cell Rules, Duplicate Values Send a copy of the unique values to a new location by using the Advanced Filter Remove duplicates, add the sales for all the removed records and then add them to the customer field. This can be used with the Pivot Tables by using the Consolidate feature. To analyze unique combination of two fields when looking for duplicates: Copy the data then paste it to a blank section of the worksheet Leave a blank column between your data and the copy of the data Select a single cell within the data set On the Data tab, Data Tools group, select Remove Duplicates In the Remove Duplicates dialog, leave the check boxes for both of the fields selected Click OK to remove the duplicate values In this case, the result is a list of all unique combinations of market and customer B. Handling Duplicates Other Ways The Remove Duplicates command is also available in Table Tools, Design tab. For example, you can use this option to remove duplicates from a table that you have defined as a range. C. Combining Duplicates and Adding Values You can use a pivot table to find the total sales for each customer. Alternatively, you can use the data tools to consolidate the table down to one record per customer, start a blank section of the workbook before invoking the feature. Use these tools when you have a customer that appears in the column values (sales, costs, profit values) more than once. To use the Consolidate feature: 1. Move the cell pointer to a blank section of the worksheet 2. Select Data, Data Tools, Consolidate, then the Consolidate dialog box appears 3. In the dialog, enter the reference to your data in the Reference box. The data will be combined based on the field in the left column of the range. If you had multiple lists of customers, you could click the Add button and enter additional ranges. 4. Make sure to select the Top Row and Left Column check boxes in the Use Labels in section 5. Click OK Excel creates a new table. Each customer appears in the table just once. The sales associated with all the records of the customer appear in the new total.

Chapter 28: More Tips and Tricks for Excel 2010

Speeding Up-Calculation by Using Multithreaded Calculation Multithreaded calculation allows Excel to spot formulas that can be calculated concurrently and then run those formulas on multiple processors simultaneously. To enable multithreaded calculation, follow the steps: Select File, Options. The Excel Options dialog appears Select the Advanced category and scroll down to the Formulas section Select Enable Multithreaded Calculation Unless you are using XLL add-ins, select the Use All Processors setting. If you are using XLL add-ins, sell the following note. Watching the Results of a Distant Cell As you change the assumptions, it would be good to know the impact on ROI. You can set up a watch to show you the current value of the distant cell(s). People developing VBA macros in Excel have had a Watch Window dialog available in VBA for more than a decade. Microsoft finally added a Watch Window dialog To set up a watch, follow the steps: Select Formulas, Formula Auditing, Watch Window to display the floating Watch Window dialog over the worksheet. Click Add Watch in the Watch Window dialog In the Add Watch dialog, clikc the RefEdit button and then click the cells that you want to watch Click Add to add the cell(s) to the Watch Window dialog Repeat steps 2-4 as necessary Position the Watch Window dialog in an out-of-the-way location above your worksheet so that you can continue to work. The Watch Window dialog shows you the results of key cells that you define. These cells can be in far-off or on other worksheets. Opening the Same Files Every Day In some jobs, you might have to open the same workbooks at the same time to perform a certain recurring task. After opening the files manually, you can specify that the files belong to a workspace. Then when you open that workplace, Excel opens all the documents associated with the workplace. To set up a workplace, follow these steps: Close all open workbooks Open each workbook associated with the task From the View tab, select Window, Save Workspace. The Save Workspace dialog appears Browse to a location to save the workspace. Give the workspace a name. Click Save. Note that the file is saved with an .xlw extension. The next time you need to open those files, select File, Open and select the XLW file. If you later rename one of the files or move one of them to a new location, you have to re-create and resave the workspace. Comparing Documents Side by Side with Synchronous Scrolling To compare an original workbook to a new workbook visually: Close all other documents Open the first workbook Open the second workbook Select View, Window, View Side by Side If you have more than two workbooks open, you have to choose just one of the other workbooks o be used for the comparison. The two workbooks appear together. If the windows are split horizontally, one above the other, select View, Window, Arrange, Vertical to have the worksheets appear side by side Begin scrolling through the data using the scrollbar or the wheel on your scroll mouse Synchronous scrolling does not work well if someone deleted or inserted extra rows in one workbook. To solve this problem, follow these steps: If one worksheet has extra rows and is out of sync with the other worksheet, click View, Window, synchronous Scrolling to temporarily turn off this feature Use the arrow keys or scrollbar to line up the worksheets. Scroll one worksheet so that both worksheets have the same record as the top row in the window Click View, Window, Synchronous Scrolling again to turn the feature back on. You can now continue scrolling the rows below the mismatched rows. Calculating a Formula in Slow Motion This can help you locate any logic errors in the worksheet: To evaluate a formula in slow motion, follow these steps: Select the cell that contains the formula Select Formulas, Formula Auditing, Evaluate Formula. The Evaluate Formula dialog box appears, showing the formula. One element of the formula is underlined, indicating that this element will be calculate next To see the value of the underlined element immediately, click Evaluate If you want to see how that element is calculated, instead of clicking Evaluate, click Step In. Excel shows the formula for that element. Eventually, the final level is evaluated to a number. Click Step Out to return one level up the dialog Continue clicking Evaluate until you arrive at the answer shown in the cell Inserting a Symbol in a Cell Instead of memorizing arcane key combinations, you can use Insert Symbol to add symbols to a workbook. On the Insert tab, use the Symbol icon to display the dialog. Edit an Equation On the Insert tab, the Equation drop-down offers nine prebuilt equations. To build some other equation, insert a shape in the worksheet first. While the shape is selected, use Insert, Equation, Insert New Equation. A blank equation is added to shape. You have to be inside the equation to have the Equation Tools Design tab showing. Adding a Digital Signature Line to a Workbook After you sign a document, it is converted to read-only to ensure that no one changes the document after you sign it. Digital signatures are provided by a third-party certifying authority. There is a fee for this service. To purchase a digital signature to a file, open the file and then select Insert, Text, Signature Line, Add Signature Services. This leads to a list of approved digital certificate providers. Choose a provider and follow the steps on the provider's website to purchase and install the signature. To add a signature to a file, open the file and then select Insert, Text, Digital Signature. The right-click the signature and select Signature Setup to access the dialog box: To sign a signature line, follow these steps: Double-click the signature line in the document. The Sign dialog appears. In the Sign dialog, do one of the following: Type your name in the box next to the X to add a printed version of your signature. Click Select Image and choose a graphics file of your signature On a table PC, sign your name in the box by using the inking feature. Signing a document marks it as final. If someone changes the document, the signature disappears. Protecting a Worksheet If you have many formulas in a worksheet, you might want to prevent others from changing them. In a typical scenario, your worksheet might have some input variables at the top. You may want to allow those items to be changed, but you might not want your formulas to be changed. To protect a worksheet: Select the input cells in your worksheet. These are the cells that you want to allow someone to change Press Ctrl + 1 or go to the Cells group of the Home tab and select Format, Format Cells. The Format Cells dialog appears. On the Protection tab of the Format Cells dialog, clear the Locked check box. Select Home, Cells, Protection, Protect Sheet. The Protect Sheet dialog appears. Optionally, change what is allowed to happened in the protected workbook Click OK to apply the protection Do not rely on this password for security, because it can be easily be broken. Sharing a Workbook Offers a Share Workbook icon under Review, Changes, Share Workbook. After you share a workbook, it becomes so limited that is practically unusable. Shared workbooks cannot have tables. You cannot insert blocks of cells in them. You cannot delete their worksheets. You cannot merge cells, add conditional formats, add validation, add charts, add pictures, add or change pivot tables, insert hyperlinks, use scenarios, use subtotals, write macros, or edit array formulas in shared workbooks. Basically, all but the simplest spreadsheets are unsharable. You can find one feature in Excel 2010 regarding sharing by selecting the Office icon and then Publish, Create Document Workspace. With the Create Document Workspace feature, you can save a workbook to a server and require others to check the file out before editing. This prevents multiple people from simultaneously editing the workbook. Separating Text Based on a Delimiter If the fields are separated by a character, then you can separate the data into multiple columns, follow the steps: Select the one-column range that contains multiple values in each cell. Select Data, Data Tools, Convert Text to Column. Excel displays the Convert Text to Columns Wizard dialog. In step 1 of the wizard, select Delimited and click Next. In step 2 of the wizard, choose the delimiter. Excel offers check boxes for Tab, Semicolon, Comma, and Space. If your delimiter is something different, select the Other box and type the delimiter. Click Next. In step 3 of the wizard, indicate whether any of your columns are dates. Click the column in the Data Preview section and then select Date in the Column Data Format section. By default, Excel replaces the selected column and uses adjacent blank columns. To write the results to a different output area, enter a destination in step 3 of the wizard. Click Finish to parse the column Excel does not automatically make the columns wide enough, so select the Cells section of the Home tab and then select Format, Width, AutoFit to make the output columns wide enough for the contents. Translating Text You can translate the document a single cell at a time, follow the steps: Select the cell containing text that you want to translate On the Review tab, click Translate In the Research task pane, choose a From and a To language. The Research pane shows you the translation. Select the translated text by dragging over it with the mouse. Press Ctrl + C to copy the translation to the Clipboard. Click in a cell in your Excel worksheet and press Ctrl + V to paste. This feature can be powerful if you have the adjacent column stayed in the foreign language.

Chapter 24: Using Slicers and Filtering a Pivot Table

Using Slicers and Filtering a Pivot Table Slicers allow you to perform ad-hoc analysis by choosing various items from various fields in the pivot table. Filtering Using the Row Label Filter Report Filter as the Page Field area of the layout To add filter cells to a pivot table to do basic ad hoc analysis use the Report Filter To add filter drop-down to the top of the pivot table, you drag the fields to the Report Filter drop zone in the pivot table field list. Before adding a date field to the Report Filter, follow these steps to create your own hierarchy: 1. Add the date field temporarily to the row field area of the pivot table 2. Select the first cell containing a date in the pivot table 3. In the Options tab, click Group 4. In the Grouping dialog box, select Days, Months, Quarters, and Years. Click OK. 5. Move the Date, Months, Quarters, and Years fields to the Report Filter drop zone. A. Arranging the Filters Arrange fields in the Report Filter area The vertical arrangement means seven rows (the data with an added row) will be taken up with the noncustomer data (the data). Select one cell in your pivot table Click the Options icon in the left side of the Options tab In the PivotTable Options dialog box, the Layout & Format tab offers two settings that affect the arrangement of the Report Filter. In the Display Fields in Report Filter Area is set to Over, Then Down. The Report Filter Fields Per Row is set to BLANK A. This creates an arrangement of the total number of filter fields without the added number (BLANK B/ BLANK A = #) B. Selecting Multiple Items You are able to choose multiple items from the Report Filter field. The Select Multiple Items was an intermediate solution added to make slicers possible in order to print and view the multiple items. Filtering Using Slicers The Slicer arrangement invites people to start running ad hoc analyses by clicking the slicers A. Adding Slicers Add extra rows above your pivot table and extra columns on the both side in order to add slicers: Select one cell in your pivot table On the Options tab, select the Insert Slicer icon, Insert Slicers dialog Choose any fields that you would make suitable filter fields meaning the field can not contain sufficient data for printing. Excel tiles a bunch of one-column slicers B. Arranging the Slicers To choose a logical arrangement for the slicers, you can reposition and resize the slicers: The Slicer Tools Options tab allows you to control the number of columns in a slicer. By making the slicer shorter, the extra < and > items are hidden from view. To create that arrangement, click the slicer and use the Columns spin button in the Slicer Tools Options tab to specify three columns. After you adjust the number of columns, make the slicer wider to fit all the buttons. Horizontal slicer C. Formatting the Slicers Choose one of the fourteen slicers color themes in the gallery located on the Slicer Tools Options. Control the caption that appears at the top of each slicer Choose a slicer theme Click the Slicer Settings icon on the left side of the Slicer Tools Options tab You can adjust the caption in the Slicer Settings dialog Slicers provide a visual indication in the report. D. Using the Slicers To select a single item from a slicer, choose that item To multiselect from a single slicer, hold down the Ctrl key while selecting multiple items. To select adjacent items, click on the first item and drag to the last item to be selected Selections in one slicer might cause items in other slicers to gray out. In this case, those items move to the end of the list. This gives you a visual indication that the item in not available based on the current filters. To clear a filter from a slicer, click the Funnel-X icon in the top right of the slicer Limits the list of customers active in the slicer. Filtering Oddities For filtering pivot tables A. AutoFiltering a Pivot Table AutoFilter a pivot table despite of the grayed out Filter field when you are inside a pivot table. Select the cell located to the next of the last header. Click the Filter icon Excel turns on the AutoFilter drop-down The AutoFilter icon will gray out the cells when you are in the pivot table The AutoFilters are not calculated after a Refresh. B. Applying Row Label Filters to Fields Not in the Pivot Table Report You can apply a filter to a field that does not appear in the pivot table Go to the top of the Pivot Table Field List and hover over any field A drop-down appears on that field Open the drop-down and you can apply a filter to the field, even though it is not in the current report. C. Replicating a Pivot Table for Every Customer Excel adds worksheets to your workbook. Each worksheet contains the original pivot table, with a different value chosen for the selected filter field. Follow the steps: Select the Options drop-down from the Options tab if your pivot table contains at least one Report Filter field. Select Show Report Filter Pages from the drop-down menu Confirm which field should be used. Replicate your pivot table for every value in a Report Filter field. Sorting a Pivot Table You can sort your table with the fields in any sequence. The drop-down menu offers the ascending or descending order, including the More Sort Options from this drop-down. The Sort Dialog appears when you select the More Sort Options It initially offers to sort in any order based on the field. The summary will give you a description of the outcome. You can also have a pivot table with conceptual dates, revenue and customers with the More Options dialog and the mouse method. A. Why Not Sort Using the Data Tab? Any sort property you set will remain in effect as you add and remove fields with the sort options in the PivotTable Field List Box.

Chapter 26: Using What-If, Scenario Manager, Goal Seek, and Solver

Using What-If There are new features that will help you find a specific answer like the Goal Seeker and Solver. A. Creating a Two-Variable What-If Table Copy the model, input the variables and formulas, reach a suitable solution for the Excel models. This is known as what if changes. By making multiple copies, you can create a What-If Table Create the table: 1. Enter a formula in the upper-left corner of the table. The formula will point out to two variables. 2. On the left column of the table (Column Input Cell, where you have a set of values aside from the table), enter various values for one of the input values. 3. Along the top row of the table, enter various values for the other input variable (Row Input Cell), for your end result, you would have inputted cells based on values. 4. Select the entire table 5. From the Data tab, select Data Tools, What-If Analysis, Data Table 6. In the Data Table dialog box, enter a row and column input cells, OK 7. To play with the variables: Select the entire table, use the Data Table command (Data tab, for the Data Table dialog box) by using formula, filling the various possible values for purchase price and the term of the loan for the PMT () function inputted in a cell. OK 8. Select the interior of the table. You can see that Excel represents the table with the TABLE () array function. Using Scenario Manager You can use multiple scenarios, each scenario changing up to 32 variables. Name the ranges before inputting the variables before defining the first scenario because you may have all 32 variables for only one scenario. To switch to a different set of input variables (values to formulas): Select Data, Data Tools, What-If Analysis, Scenario Manager for the Scenario Manager dialog Click Add, Edit Scenario dialog appears, In the dialog, enter a name for the scenario, ex. Best case scenario Choose the cells that will be changing (it can be an absolute reference range), press ctrl + click to add additional ranges. The Scenario Values dialog appears for you to enter the input cells. You could have entered the cells that are named instead of the addresses. You can switch between case scenarios by double clicking a scenario or clicking the scenario and show. If you are going to add a new scenario similar to the one existing, then show that scenario before clicking add. A. Creating a Scenario Summary Report In the Scenario Summary dialog, check the Scenario Summary checkbox Select one or more cells to represent the results of the model. To select more cells, hold the Ctrl. The report will be on a new worksheet. You may have the group and outline buttons as a result. You will probably make adjustments to your model to make it look better Every time you create a new scenario you can see your comments, name, date and time. B. Adding Multiple Scenarios To share your workbook for others to add more scenarios for other purposes: 1. Save the workbook with the starting scenario 2. Route the workbook to each person. In a hidden field, excel tracks who adds each scenario 3. When you get the routed workbook back, open both the original workbook and the routed workbook 4. Display the Scenario Manager in the original workbook 5. Click the Merge button to display the Merge Scenarios dialog 6. In the Book drop-down, select the name of the routed workbook. In the dialog, it shows that two scenarios are available in Sheet 1. 7. The dialog differentiates the identical sheet names by adding a date or name to the incoming scenarios. If the scenarios are identical, then delete them to remove duplicates. 8. In the Scenario Manager, click Summary. The Scenario Summary dialog appears. 9. In the Scenario Summary dialog, click Scenario PivotTable report. The initial pivot table appears, 10. Move the field from the Report Filter to the first row label to compare scenarios by person. Using Goal Seek =ARCSIN() is the opposite of =SIN() The Goal Seek dialog allows you to get the end result of a cell: 1. Select the cell that has the end result 2. Data tab, Data Tools group, What If Analysis drop-down, select Goal Seek 3. The Goal Seek dialog appears: Set Cell: the result in the cell E4 To value: the numeric value you would like to set that you are changing the cell By Changing Cell: the variable that influences the set cell You can use this feature more than once and the variables (term or interest rate) do not matter to reach the end result. =NPER() function is an alternative and provides an accurate answer than the Goal Seek when it does not function properly. Using Solver Excel Solver add-in when your problem has multiple independent variables and constraints Specify certain constraints on the solution Do you want your cell to be either maximized, minimized, or set to a particular value To solve complex modeling systems, you can install it from Solver.com A. Installing Solver To install: 1. File menu, Options, Options dialog appears 2. Select Add-Ins 3. At the bottom of the page, select Excel Add-Ins from the Manage drop-down 4. The Add-Ins dialog appears. Go 5. Check the Solver in the Add-Ins dialog B. Solving a Model Using Solver Identify the input range, the output cell, and the constraints Do you want a particular value in your output cell? Do you want to minimize or maximize your input cell? 1. As with the Goal Seek, start by telling Solver Parameter dialog that you want your targeted cell set to maximum for the maximum widget production. Set Target Cell: the cell that will output your result Equal to: maximum By Changing Cells: the range of the number of manufacturing plant productivity (labor shifts) Add: Add Constraint dialog: Cell Reference is the cell that has the numeric value of the total amount to produce a widget. Constraint: indicates the product cost is equal to or no more than 2 dollars. 2. Then you may want to add another constraint to indicate the number of shifts cannot be a negative number 3. Then add another constraint to specify the shifts during the weekday cannot be no more than 5 and set the integer value because the total result cannot be a decimal. 4. Select the bin value in the comparison operator to specify that the values are limited to binary values. This means there are only two possible values for each cell to indicate if the store is either open or close during the weekends. This is called binary constraint. 5. Use the Solver Options dialog to fine-tune the process. Excel cannot have more than 100 seconds to formulate and calculate the Solver for a desired result. 6. Press OK, solve and you will have the Solver Results dialog appear to indicate success. 7. You can save all of the scenario in the worksheets. Later they will show in the Scenario Manager

Chapter 23: Using Pivot Tables to Analyze Data

You can slice and dice your data: Breakdown of sales by product Sales by month this year v. last year Percentage of sales by customer Customers who bought xyz in the east Sales by product my month Top five customers with products DAX formula language (a new PowerPivot add-in) allows you to create pivot tables from multiple tables on multiple worksheets. Creating Your First Pivot Table Follow these rules: Ensure each column has a one-cell heading. Or, if your header has two row, then type the first word press Atl + Enter, and then type the second word. Enter zeros instead of blanks for numeric data. If the total are embedded in your report, remove them. The workbook should be in Compatibility mode. Many new pivot table features will disable from Excel 2007 and 2010 will be disabled if the workbook is in Compatibility mode. If you add new data to the bottom of your data set each month, you should strong consider converting your data set to a table using Ctrl + T. Pivot tables created from tables automatically pick up new rows pasted to the bottom of the table after a refresh. If your data has month spread across many columns, you may want to see it from a different view of the data. A. When you have your data in the correct format, creating and changing a pivot table is easy. Select one cell in your data From the Insert tab, click the Pivot Table icon Excel displays the Create Pivot Table dialog In the top portion of the dialog, the table name or range appears In the lower portion of the dialog, you choose to create your pivot table on a new worksheet or in a blank portion of the existing worksheet. The PivotTable Field List has a list of fields from your original data set at the top and four drop zones at the bottom. To build the report, add field to the drop zones at the bottom. For fields that contain text, they are automatically moved to the Row area. For the fields that contain numeric, then they are automatically moved to the Values area. Dealing with the Compact Layout For Compact View: Make sure that the active cell is inside the pivot table Go to the Design tab in the Ribbon. Open the Report Layout drop-down. Select Show in Tabular Form. The totals move back to the bottom of each region. Product moves to Column B. Use the Design tab to return to a normal pivot table layout. A new feature: Open the Report Layout drop-down and select Repeat All Item Labels. This eliminates the blanks in column A of the pivot table. Repeat All Item Labels fills the outer row fields. Rearranging a Pivot Table The drop zone sections of the PivotTable Field List Box: Report Filter-To limit the report to only certain criteria. Row Labels-For fields that appear on the left side of the table. By default, all text fields move here when you select the check boxes in the top of the field list. Column Labels-For fields that stretch along the top rows of the columns of your table. Values-For all numeric fields that are summarized in the table. By default most fields are automatically summed, but you can change the default calculation to an average, minimum, maximum, or other calculations. To rearrange the pivot table, you drag fields from one drop zone to another. Your table appearance also depends on the arrangement of your fields per area in the PivotTable Field List dialog Finishing Touches: Numeric Formatting and Removing Blanks Formatting numeric fields: 1. Select ne cell that contains a revenue amount. If you look on the Options tab, you will see a box that reports to the Active Field box indicates Sum of Revenue is the active field 2. Click the Field Settings icon in the Active Field group of the Options tab. Excel displays the Value Field Settings dialog. 3. The label for this field appears in the Custom Name box at the top of the dialog. Change Sum of Revenue by typing a space and then the word Revenue. Note that the space is critical. You cannot use just the word Revenue without a space as this would create a duplicate field name. 4. Click the Number Format button in the bottom of the Value Field Settings dialog. Excel displays the familiar Number tab of the Format Cells dialog. 5. Select the Number category. Select 0 decimal places. Add a thousands separator. Click OK to close the Format Cells dialog. Click OK to close the Value Field Settings dialog. To fill in empty cells with zeros: Select any one cell inside the pivot table. On the Options tab, select the Options icon on the left side of the Ribbon On the Layout and Format tab of the PivotTable Options dialog, type zero next to For Empty Cells Show. Click Ok. Four Things You Have to Know When Using Pivot Tables Understand issues in the following: A. Your Pivot Table Is in Manual Calculation Mode Until You Click Refresh! If you build a pivot table and then change the underlying data, you must click the Refresh icon in the Options tab to have the change appear in the pivot table. B. One Blank Cell in a Value Column Causes Excel to Count Instead of Sum Excel no longer realizes the columns as numeric values. Two choices: Delete the pivot table, fill the blanks in the original data with zeros and re-create the pivot table. Select one cell that contains Count of Revenue. Select the Field Settings icon. Change from Count to Sum in that dialog. C. If You Click Outside of the Pivot Table, All the Pivot Table Tools Disappear If your field list disappeared and the Options and Design tabs are missing, it is likely that you clicked outside of the pivot table. D. You Cannot Change, move a Part of, or Insert Cells in a Pivot Table The solution is to copy the entire pivot table and then use Paste Values to convert the report to regular Excel data. You can either put this on a new worksheet or past the entire table back over itself. If you go to a new worksheet, you can continue to modify the original pivot table. If you paste values over the original worksheet, the pivot table converts to a range, and you cannot pivot it further. Calculating and Roll-Ups with Pivot Tables Offers many more calculation options. For example, to roll daily dates up to months, quarters, and years. A. Grouping Daily Dates to Months and Years To produce a summary by month, quarter and year, follow these steps: Build a pivot table with daily dates going down with row field Select either the Date heading or one of the cells containing a date. From the Options tab, select Group Field In the dialog, select Months, Quarter and Years. Click OK the basis are in row labels. Because of a strange bug, the year and quarter fields do not have subtotals. The cell pointer should be on the first year cell. Select Field Settings button in the Options tab. Select Automatic and click OK. Move the cell pointer to the first quarter cell. Select Field Settings and click Automatic. Click Ok. Take the Years field and drag it to the Column Labels drop zone in the Pivot Table Field List. You now have a report comparing year over year sales. B. Adding Calculations Outside the Pivot Table To have a percentage of growth rather than grand total column: Select the range of cells. Copy, Use Paste Special, Format to copy the table formatting to cell F3. In a blank cell type the formula and format the cell as percentage with one decimal place Copy the cell, select a range, open the paste drop-down and select the Formulas & Number formatting icon with % and fx symbols. Move the cell pointer back to Column blank so that you are inside the pivot table and have access to the pivot table tabs. On the Options tab, click the options icon. Go to the Total & Filters tab of the Pivot Table Options dialog. Clear the Show Grand Total for Rows check box. Click OK. Delete the now-empty column blank. C. Showing Percentage of Total New features by adding the Show Values As drop-down to the Pivot Table Options tab. Drag the revenue field to the Values drop zone three times. You will be comparing the different ways to show the Revenue Field Leave the first Revenue column in the pivot table alone Move the cell pointer to the second Revenue column in the pivot table. From the Show Values As drop-down, select % of Column Total. Move the cell pointer to the third Revenue Column. From the Show Values As drop-down, select % of the Column Total Move the cell pointer to the third Revenue column. From the Show Values As drop-down, select % of the Parent Row Total. This is one of the new calculation in Excel 2010. Type new heading for the three columns: something like Revenue (with a leading space), then % of Total, and % of Parent. D. Showing Running Totals and Rank This works best when there is only one field in the row area and no fileds in the column area: Drag the Revenue field to the Values area five times. Leave the first Revenue column showing a Normal calculation Go to the second column with Revenue. From the Show Values As drop-down, select % Running Total In. This time, Excel asks one additional question; specify that you are looking for Running Totals in the Date field Change the heading of this field to the YTD. Go to the fourth Revenue Column. From the Show Values As drop-down, select Rank Largest to Smallest. Select Date. Change the heading to Rank. Go to the fifth Revenue column. From the Show Values As drop-down, select % Of. In the Show Values As dialog that appears, select Date as the Based Field. Select (previous) as the Base Item. Click Ok. Change the heading to % Change from Previous Month E. Using a Formula to Add a Field to a Pivot Table The previous examples took an existing field and used the Show Values As drop-down to change how the data is presented in the pivot table. In this example, you learn how to add a brand a new calculated field to the pivot table. Follow the steps to add a calculated field: 1. Select one of the numeric cells in the pivot table 2. From the Options tab, open the Fields, Items & Sets drop-down. Select Calculated Field. Excel displays the Insert Calculated Field dialog. The default field name of Field 1 and the default formula of =0 appear in the dialog. 3. Type a new name such as GP% 4. The Formula field starts out as an equal sign, a space, and then a zero. You have to click in this field and backspace to remove the zero. 5. Build the formula by double clicking Profit, typing a slash, and then double-clicking Revenue. 6. Repeat steps 1 and 2. The calculation for Average Price is Revenue/ Quantity. Click Ok. 7. The headings for calculated fields always appear strange. Change Sum of % to GP% with a leading space. Change Sum of AvgPrice to Av.Price 8. Change the numeric format of the calculated fields. Formatting a Pivot Table Excel offers a PivotTable Styles gallery on the Design tab. A. Using the PivotTable Styles Apply style to a table without changing the structure Modify by using the four check boxes for Branded Rows, Banded Columns, Row Headers, and Column Headers The color themes are in the Page Layout tab Report layouts Blank rows Grand Totals on or off for rows or columns Subtotals Above or Below Build new styles Finding More Information on Pivot Tables Pivot Table Data Crunching (QUE, ISBN 978-0-789-74313-8) coauthored with Mike Alexander PowerPivot for the Excel Data Analyst (QUE ISBN 978-0-74315-2)

Chapter 25: Mashing Up Data with PowerPivot

PowerPivot is a free add-in by SQL Server Analysis Services team for analyses. Benefits and Drawbacks to PowerPivot A. Mega-Benefits of PowerPivot Process far more than a million rows of data - beyond the 1048576 Create pivot tables from multiple tables, without writing a VLOOKUP - You no longer have to write processor-intensive VLOOKUP formulas to join data from two worksheets before creating a pivot table. Mash-up data from disparate sources - Imports text, Access, RSS, SQL Server, Excel data and present it in a single pivot table. Get access to Sets - Allows asymmetric reporting. It works only with OLAP pivot tables. When you have your regular data with the PowerPivot. Do calculations that make Excel's Calculated Fields look like they were designed by someone in kindergarten - Data Analysis Expressions is composed of 117 functions to create a new measure in the pivot table. This allows 54 functions. B. Moderate Benefits of PowerPivot Compression- Excel workbooks with PowerPivot data are smaller than workbooks that use traditional PivotCache pivot tables. The data is still stored inside the workbook file, but the PowerPivot team came up with better ways to compress the data. Join two pivot tables with a single set of slicers - have one set of slicers that control two separate PowerPivot tables. Slicer Autolayout- Slicers created in regular Excel are always one column and always start at the same size. PivotCharts without PivotTables-Automatically build a chart on your presentation worksheet and then tuck the linked pivot table away on another worksheet. Why Is This Free? Pivot Tables-You never had to do @DSUM and Data Table 2 anymore VLOOKUP-Join data from two tables. IF/ SUMIFS/ AGGREGATE - These functions allow various conditional calculations 1048576 Rows in Excel 2007- You no longer have to depend on Access You can do all this in a pivot table. A. Benefits of the Server Version of PowerPivot If you get your IT folks to install PowerPivot Server, you get these additional benefits: Automatic Refresh-you can automatically refresh your data with the new server version. Publish to Report Gallery-You can publish your pivot table with the Share Point server. B. Drawbacks to Using PowerPivot No Grouping-You can work around using the DAX language to define year, quarter, month columns. You lose Undo-When you run a macro or some external code, then you use the ability to undo No VBA-Cannot use VBA to control PowerPivot No Drilldown-You cannot double click a cell in a pivot table in order to see all of the rows that make up that cell. Excel 2010 only-PowerPivot only works with Excel 2010 and new versions. Installing a PowerPivot File Select Help The right side of the Backstage View shows a version number either 32 bit or 64 bit to install After installing the add-in, you should see a PowerPivot tab on the ribbon Case Study: Building a PowerPivot Report It is important to have column headings in row 1 of the CSV file. The point-of-sale vendor who provides that data usually had a "Run on mm/dd/yyyy" row at the top of the file, a blank row, and then headings in row 3. A. Import a Text File To import the main table that is the large CSV file Select the PowerPivot tab Select the PowerPivot Window icon. A new PowerPivot application window appears. The PowerPivot offers two Ribbon tabs: Home and Design. Get External Data Group, select From Text PowerPivot shows the Table Import Wizard In the Table Import Wizard dialog, select a Friendly Connection Name. Click the Browse button and locate your text file. PowerPivot does not default to see the first row as column headers, so the data preview offers five unfriendly name column names of F1, F2, F3 and so on. In the Column Separator, verify that your delimiter is a comma. Select the check box for Use First Row as Column Headers. The preview now shows the real column names. If there are any columns that you do not need to import, clear tem. The entire file is going to be a read into memory. If you open a filter field, you can choose to exclude certain values from the import. Click Finish, and PowerPivot begins loading the file into memory. The wizard shows how many rows have been fetched so far. When the file is imported, the wizard confirms how many rows have been imported. Click Close to return to the PowerPivot window. After the data set is shown in the PpowerPivot window. You can Sort, change the number format, or filter. At the bottom line, you have the total amount of rows. B. Add Excel Data by Copying and Pasting To add data in a new worksheet: Open this workbook in Excel Select the data with Ctrl + * Copy it with Ctrl + C Click the PowerPivot tab. On the left side of the Ribbon is an icon to return to PowerPivot Click the PowerPivot window icon. You will see you data set in PowerPivot Click the Paste icon on the PowerPivot Home tab. You will see a paste preview window. Give the new table a better name than "Table": perhaps StoreInfo Click Ok Then you will see you have joined two worksheets. C. Add Excel Data by Linking If you make changes to the original worksheet. Then those changes will not make through the PowerPivot table. An alternative is to link that data from Excel to PowerPivot. To link to Excel data, that data must be converted to the Table Format: In your worksheet, make sure you have single-row headings at the top with no blank rows nor columns. Select one cell in the worksheet, press Ctrl + T. Excel asks you to confirm the extent of your table and if your data has headers. Use the Table Tools Design tab to change that format Go to the Table Tools Design tab. On the left side of the Ribbon, you see that this table is called Table1. Type a new name such as BLANK. On the PowerPivot tab, select Create Linked Table. Then, the table appears in the PowerPivot window. D. Define Relationships In Excel, you will be creating VLOOKUPs to match the two tables. It is far easier in PowerPivot. You link from one column in your main table to a column in another table. To do so, do the following: Select a cell in the column from which you will link. Click the Design tab in the PowerPivot Ribbon. Select Create Relationship. Then, the dialog appears. By default, the selected table and column appear in the first two fields. Select demo if the table is not shown in the drop-down table. In the Column field, go to the drop-down menu to select the correct field. Open the Related Lookup table drop-down to select the correct field. Because the columns names match (the original worksheet to the PowerPivot table) , PowerPivot automatically changes the Related Lookup Column to read the column. Click Create, then you now created a relationship between the two tables. E. Add Calculated Columns Using DAX To add a calculated column to the Demo table, use the Data Analysis Expressions (DAX) formula language: Click the Demo worksheet tab at the bottom of the PowerPivot Window The column to the right of Revenue has a heading of Add Column. Click in the first cell of this blank column. Click the fx icon to the left of the formula bar. The Insert Function dialog appears with categories for All, Date & Time, Math & Trig, Statistical, Text, Logical and Filter In the Insert Function dialog, select Date and Time for the category. Excel offers new functions. Select the function Click the first cell in a column PowerPivot proposes a formula Type the closing parenthesis then Enter Excel fills in the column with the desired data elements. Right click the column and select the column Type a name You can continue adding columns. DISTINCT(Column) The column from which unique values or expression are to be returned. F. Build a Pivot Table Multiple tables can share the same data and slicers. You have many options if you open the PivotTable drop down menu from the Home tab. The PowerPivot Field List is a third variation of the pivot table field list. It is actually a new entry in the Task pane. Both tables are available in the top of the Field List. The main table is expanded to show the field names, but you can expand the other table and add those fields to this pivot table. Two new sections in the drop zones offer vertical or horizontal slicers. Because you are in a pivot table, the PivotTable Tools tabs are available. Select a column from a table from the PowerPivot Field List You can format and rename the column. Choose a format with banded rows G. Slicers in PowerPivot Slicers are offered in the PowerPivot Field List dialog Vertical Slicer will be placed to the bottom left of your pivot table. Horizontal slicer will go above your pivot table The big box surrounding the small slicer is the slicer parent control for the horizontal slicer. Add fields to the vertical slicer. Ctrl + click to select additional fields for a slicer. You can use the slicer with filters The slicer parent control box disappears after you click outside of the pivot table. However, you can have the slicers appear with the PowerPivot Field List displays. Click the box once and you can resize with the handles. Some Things Are Different The PowerPivot acts like the OLAP pivot table: Days of the week do not automatically sort into the proper sequence. You have to choose More Sort Options, Ascending, More Options, and Uncheck the AutoSort box. Open the First Key Sort Order drop down and choose Sunday, Monday, and Tuesday. There is a trick in regular Excel pivot tables that you can do instead of dragging field names to the right place. Decide between Compact, Tabular, and Outline layouts before you drag fields to the correct location. You cannot access filters, rearrange the field list, Use the PivotTable Tools Option to access the Value area of the dialog when you have multiple fields. You can use the mouse-method when creating a formula. To refresh, Click Refresh in the PivotTable Tools Option tab. Two Kinds of DAX Calculations The RELATED function can be used to add a calculated column from a different table. DAX can be used to create multiple measures in the pivot table. These functions are all aggregate functions that calculate a value for the filtered rows through the Pivot Table. A. DAX Calculations for Calculated Columns There are 54 functions that are similar to Excel, please refer to p771. You will have a list of all of the functions that you can use with the DAX, which covers must of the functions that I have reviewed. Use FORMAT instead of TEXT in this language in order to operate the functionality. To exclude rows from a calculation, use the BLANK () function in an IF () function. B. Using RELATED () to Base a Column Calculation on Another Table Calculated columns don't automatically use the defined relationship The related function tells DAX that you don't want to divide the numeric value by all values in table but by only one value that is related to the column. Using DAX to Create New Measures Filter first, then calculate A. Count Distinct Using DAX DAX lets you count how many distinct values meet the filter To create a new measure in DAX: Go to the PowerPivot tab, click New Measure icon, displays the Measure Settings dialog Table Name-The base table where your main numerical data is located, use the drop-down menu Measure Name- use a name that will measure all pivot tables. Custom Name-use the same name as the measure name Measures are always aggregate functions (SUM or COUNTROWS) and not cell level functions. The distinct function returns a value from the home table that matches the filter either in the record or field, you cannot link a table. Build a formula: =CountRows(Distinct(Sales[Date])) to measure the DayCount in the Sales table. In the formula, distinct returns one column with a list of the distinct values. However, please think about your table in a bigger picture in order to receive accurate data. You may have nesting functions in your Measure Settings dialog: LocationDayCount=Count Rows(Distinct(Sales[LocationDays])) ## This counts the days per location by counting the number of rows based on the number of days per location that the store was open. SalesPerStorePerDay=SUM(Sales[Net Sales])/CountRows(Distinct(Sales[LocationDays])) ## This sums the total amount of sales per store per day by summing the net sables by counting the number of rows that the store mades sales per location day. B. When "Filter, Then Calculate" Doesn't Work in DAX Measures Without specifying the filter, you may have inaccurately calculated. When you want to calculate the total sum for a value, you must use the DAX language to have an accurate calculation. This is inputted in the Measure Settings dialog. However if you calculate a function with the filter, then you may have the wrong answer because your formula needs to look at all the rows not just the filter. The formula must be an almost anti-filter in order to calculate all of the table and not just for the criteria. For the percentage of the Grand Total, =SUM(Sls[Sales])/Calculate(Sum(Sls[Saless]),All(Sls)) ALL(Sls) will throw out all the filters =Calculate(Expression,[filter1],[filter2],...) This is in DAX language to calculate =Calculate(Sls[Sales],Sls[Rep]= 'Amber' You can filter to match a value in table C. Mix In Those Amazing Time Intelligence Functions You can only use All() and AllExcept() functions in the home table. In the contrast, you can use the Related() function from a linked table for the home table. You can use previously defined measures (you already used the dialog and inputted a calculation that the measure is called blank) to simply the calculation for another measures (in another measure you use blank for your measure). In page 785, you have a list of time functions. Here are few: CALCULATETABLE(<expression>,<filter1>,<filter2>,...) FILTER(<table>,<filter>) RELATEDTABLE(<table>) Other Notes PowerPivot for the Excel Data Analyst book published by QUE A. Combination Layouts 1. Single pivot table 2. Flattened pivot table. This is located in the Outline layout. If you plan to convert the pivot table to values to reuse it because the Repeat All Rows Labels feature is turned on. 3. The other 6 options are the pivot charts. 4. When you choose to combine multiple elements, then you have multiple outlines on the worksheet. Any changes you made to the PowerPivot Field list affects the pivot table first. You can have one table displayed at a time. 5. To working another element, click it, so the Field List resets to blank and you can design that element. All elements share the same slicers 6. You have inserted a new worksheet to hold the actual pivot table for each chart on your layout. B. Report Formatting 1. Insert a new worksheet 2. Create a combot of two or five pivot charts. Choose a location rather than letting them default. Choose a spot on row 5 of the new workbook. 3. Add as many slicers as possible to top and left of the of the chart 4. Build the charts 5. Make row 1 very tall -perhaps 270 to 300 points tall. Use Insert, Screenshot to add an interesting graphic to row 1. 6. Add an interesting graphic below the charts to balance the graphic on the top of the charts. 7. Go to File, Options, Advanced, Display Options for This Worksheet. Clear the Gridlines checkbox. To go all out, scroll up and remove the scrollbars, sheet tabs and formula bars by minimizing the ribbon 8. Add a fill color behind the whole worksheet 9. When the pivot table is active, click the bounding box around each slicer. Right-click the border, Select Properties, select Move and Size with cells. 10. Click away from the pivot table. The dashboard is generated by the PowerPivot. Refreshing PowerPivot Versus Refreshing Pivot Table To refresh your table that is linked to another table, click PowerPivot tab and click Refresh. However, if the data was paste, then you can Paste Append new data or do a Paste Replace. However, whenever you paste, this does not refresh, so please ensure you click Refresh! Getting Your Data into PowerPivot with SQL Server The Powerpivot table reads all the data and outputs all the tables with predefined relationships. You can even add an extra Excel or text data with the SQL Server data. Find the main Fact table, select that table, and then click the button for the Select Related Tables. Other Issues If you need two relationships, import the lookup table twice and link the relationships to each copy separately. You cannot have multiple relationships exist between two tables. Do this by importing and linking the fields.