Category Archives: Excel & Access Tips and Tricks

If you are a Microsoft Excel power user then you have probably been using pivot tables for quite some time. Pivot table functionality is among some of the most powerful features within Excel because it can turn a flat table of raw data into a powerful database with the ability to move data from rows to columns and to the page by position with just a couple of clicks of a mouse. In addition, a good pivot table user can do some interesting things such as creating formulas that exist within the pivot table or taking a field such as dollar sales and creating a dollar share with that same field. If you are a really creative Excel pivot table user then you have probably created various templates using, but hiding, pivot tables so that you can arrange your data in many creative ways such as a dashboard, a controlled tabular report or even a combination of both of these elements.

In Excel 2010 a new functionality was added… Data Slicers. Slicers are one-click filtering controls that narrow the data shown in PivotTables and PivotCharts. Slicers can be used interactively to display data changes when you apply filters. For example, you can create a PivotTable report or chart that shows sales by year, and then add a Slicer that represents promotions. The Slicer is added as an extra control in the PivotTable or chart, and lets you quickly select criteria and instantly show the changes. You could also embed the breakdown by promotion in the report itself, by including the field in the row or column heading, but Slicers do not add extra rows to the table, they only provide an interactive view into the data.

Data Slicers

The new Excel Data Slicer functionality really opens up how you can build a more intuitive templates that clearly identify which items are included or excluded from a particular pivot table view. Formerly, using only pivot table fields it was not as easy to see which items were included or excluded from a particular filtered view. In the following example it is easy to see which sizes, type, product category, flavors, geography and periods are included in the data portrayed on the left side of the report.

Data Slicers are available only in Excel 2010 and Excel 2013 and are very easy to use and create as the following steps will demonstrate:

Create your pivot table as you normally would do

Layout a consolidated report such as the following –

Click anywhere in the pivot table and click on the Pivot Table Options (Excel 2010) or Analyze (Excel 2013), and click on the Insert Slicer option –

Place a checkmark beside each pivot table element you want to have displayed as a Slicer, by left clicking in the box to the left of the element and click the OK button when done –

Excel will insert a Slicer for each of the pivot table elements you placed a checkmark next to –

The Slicers can be moved by dragging them around on the screen and grabbing the handles on the sides, top, bottom or corner to change their size. You will note in the example above that all of the elements for each Slicer are highlighted so the data display on the left has no filter and is displaying all data for all geographies, time, sizes, type and product category at one time.

By clicking on the following criteria – Sizes = 16, Type = Half & Half, Geography = Denver and Time= Latest 52 Weeks…the data will be filtered now to display those specific settings, and you will see only two items in the Product Category are now highlighted –

Timelines

These days everybody boasts of a massive spreadsheet. But almost no one needs all the data at same time. We are always filtering data for the latest quarter, 6 months starting Mother’s day or 8 weeks from November 1st etc. Of course, you can use auto-filter and select all the dates. But it is a pain. Thanks to Timelines, filtering for dates is a breeze. You can add timelines for any date column in a pivot table / pivot chart. I am sure your internal clients & bosses will love it.

Really, the Timeline (only available in Excel 2013) is just another type of Slicer that only operates on date formatted fields. You can set up and use the Timeline Slicer in the following manner:

Set up your pivot table and Data Slicers in a manner outlined above

Click anywhere in your pivot table and click on Pivot Table Options (Excel 2010) or in Analyze (Excel 2013), and click on the Insert Timeline item in the Filter section of the ribbon. If you have pivot table fields that are date formatted those date fields will be presented as optional fields to create a Timeline from

Select the date formatted field you wish to make a Timeline from; click on the box to the left of the field to place a checkmark and then click on the OK button –

Excel 2013 Will Pl. a timeline Data Slicer onto your spreadsheet and you can move it around and change the size of it as we discussed above and the Data Slicer section. When the timeline is displayed all available fields are highlighted, however you can adjust and select only the periods that you want to display in the pivot table report.

The Timeline slicer can be viewed in year, quarter, months or day aggregations to limit the data viewed in the pivot table report. For the purposes of this example I will select the month option. In the following example we have filtered on one market and set the geography slicer and one product in the product slicer, and we have selected the months of May and June. We can control the slicer by grabbing the ends of the slide along the timeline so we could click on May and then grab the right end of the slider and slide it to June –

As you can quickly see, the Microsoft Excel Data Slicer and Timeline filter functionality provide a powerful user interface to allow users with little to no pivot table experience to quickly and intuitively filter on the data they want to see.

Insight, Information & Consulting Services, Inc. is a data visualization consulting firm that helps its Consumer Package Goods clients better “see” their data. We deliver data visualization tools from cost-effective Excel & PowerPoint templates to a turnkey data visualization tool that will allow your users to see their data in PowerPoint templates using only a web browser. For more information, please visit our website at www.insightinformation.net or call Rick Pensa at 770-425-4243.

I recently had an interesting challenge that required the passing of two date parameters from an Excel spreadsheet over to an Access database in order to return data that met the parameters of data between the first date and the second date. The Excel spreadsheet template reported the data in a variety of static reports based on the date range of the data returned from the Access database. The solution to this problem was to use the Microsoft Query software available in the Excel spreadsheet. The following is a demonstration of how you can pull data from a Access database back into an Excel spreadsheet.

The following is an example of the report we want to populate from the Access database with data that meets a certain date range:

Our Access database contains over four years of data at the weekly level, but we will only want to pull back certain weeks of data to populate our report. The first step in our process will be to set up an external data pull using Microsoft Query:

We simply need to follow the prompts to find the appropriate Access database:

The next step is to select the table we want to extract our data from and select the fields from that table that we want to display in our data extract:

Then we follow the prompts through the Microsoft Query Wizard until we get to the point where the Wizard asked us where we want to return the data to and select “View data or edit query in Microsoft Query”:

In Microsoft Query click on Show/Hide Criteria to make visible the criteria fields and then select from the criteria field the desired field that we will want to pass a parameter to:

In our example query we have selected the week ending field for which to develop our parameters to be passed and we will enter the Access formula for Between to enter the start date and the end date for our query to select data from the associated Access database table:

Please note that we used the opening and closing square brackets to indicate that the query will expect a parameter that we are naming Start and a second parameter that we are naming End as our starting and ending date parameters. When we have completed our parameter formula simply click on the File menu at the top and then click on the bottom option “Return data to Microsoft Excel” and we will be presented with the only option available which is to return the data as a Table. Select OK and the table will be populated in the Excel spreadsheet.

Now we are ready to set up our date parameter fields to pass to the access database, and we do this by navigating to the data tab and clicking on the Connections menu item. Select the two feels that we want to identify as our Start date and our End date at a convenient and strategic spot in the Excel spreadsheet; in our example we will go to Sheet2 and use cell A1 for our Start date and cell A2 for our End date. In our example we want to pull data from the Access database between the dates of October 9, 2010 (10/9/2010) and April 23, 2011 (4/23/2011).

The final step in the process is to now tell Microsoft Query which of these fields is related to the start date and which is related to the end date. We do this by navigating to the Data tab and then select Connection menu option:

From the Connections wizard click on the properties button on the right side of the wizard, select the Definitions tab and click on the Parameters button at the bottom of the dialog box.

In the parameters dialog box first select the Start date parameter and identify the cell associated with it (in our example cell A1 on Sheet2) then select the End date parameter (in our example cell A2 on Sheet2).

Click on the OK box and the processes now completed. There are a couple of caveats with this approach; for example you can only deliver the data back into a table and not a pivot table, however there are various formulas which we will cover in subsequent newsletters that will allow you to aggregate the data using SUMIFS and COUNTIFS formulas.

Insight, Information & Consulting Services is a data visualization consulting firm that can help your company set up Excel templates that connect to Access databases, SQL databases as well as other data sources to provide your field sales and marketing teams with actionable data delivery tools. Please contact Rick Pensa at 770-425-4243 for further information.

Have you ever found yourself needing to average 1,000 lines of data on a spreadsheet with a ton of zeros across a number of ranges such as price gap ranges? Maybe you are trying to figure out volume responses based on certain price thresholds or maybe the average lift at various promotion prices.

You can do this with some new functions in Excel 2007 or by simply creating a custom function in earlier versions of Excel (I will provide the syntax later in this article). Suppose you start out with 1,000 lines of data in a spreadsheet that looks like the example in Exhibit 1:

The left column is the price gap between your brand and the Private Label and the right column is the volume you achieved at that gap for that week. We want to know on average what the volume expectations might be at various gap levels i.e. 10¢, 20¢, 30¢ etc.

You can't do a simple average but there is a way to accomplish this calculation using Excel 2007's Sumifs and Countifs. If we want to know the average sales for all instances when the price gap is between 10¢ and 20¢ the formula will look like this:

Exhibit 1This formula could be copied and you would simply adjust the bottom and top ranges of the price gap for each gap threshold i.e. 20¢ to 30¢, 30¢ to 40¢ etc.

If you are still in Excel 2003 or earlier versions you might want to try the following custom function syntax (CountBetween and SumBetween) entered in the Excel Visual Basic editor; simply cut and paste the following sentences into your Visual Basic editor:

Did you know you can produce custom market shares in an Excel pivot table which reflect how you identify the category segment? Last month, we learned what a terrific tool Excel can be for data analysis, by building a pivot table to display a pie chart for brand share and a bar chart to depict percent change vs. year ago. This month, I want to expand upon the data manipulation capabilities of a pivot table, by showing how we can produce a custom market share. It is often difficult to create dynamic brand shares based on segmenting a category. For example if we want to show Private Label's share of Low Fat Milk for Large Sizes (half gallons & gallon sizes) and then quickly see Private Label's share of single serve milk. With a pivot table we can set the size and fat level characteristics to create the dynamic segment definition but how can we create a dynamic brand share calculation to work just as quickly?

The following examples shows you how to build a dynamic share calculation in a pivot table:

Step1: Start your pivot table with a list style "flat file" as the following example illustrates.

Step 2: In the upper left hand corner (cell A1) of the data set, click on the Insert tab in Excel 2007 (pictured below) and in Excel 2003 (Data/Pivot Tables) to produce the pivot table (not shown). Excel will automatically select the data range for you. Select "New Worksheet" and "OK" from the radio buttons to get to the section where you arrange the data in the pivot table.

Now, build the pivot table using the market and some characteristics from your data. This segmentation is determined by how your company views the category. For example, the dairy industry might consider the milk category to be segmented by fat content (Whole Milk vs. 2% Milk vs. Skim Milk), size (Gallons, Half Gallons, Quarts, Single Sale), and then by brand. In this exercise, our objective is to report the brand share for each segment as we drill down through the category definition.

Step 3: Select "Market" and "Fat Content," and "Size Category" and "Period" by dragging them into the Report Filter box on the right side. Drag "Brand" into the Row Labels box and "Dollars" into the Values box as illustrated below.

Step 4: In Column B1, select Charlotte from the drop down menu. In B2, select Fat Free from the drop down menu. In B3, select Large from the drop down menu, and in cell B4, select Period-12 weeks ending June 2009 from the drop down menu.

We want to know the brand dollar share of the Fat Free (Skim) Milk segment of the Large Size (Half Gallons & Gallons), in the Charlotte market. Excel allows us to use the Dollar Sales measure again in the Values box and make it a Dollar Share measure rather than a Dollar Sales measure.

Step 5: Drag a second "Dollars" into the Values box (lower right).

Click on the second "Dollars" drop down menu and select "Value Field Settings." Select the "Show Value As" tab and change the "Normal" setting to "% of Column," as in the following screenshots. Excel automatically converts the dollars to percent of total. (By clicking on the second "Dollars" listed, you will get dollars on the left and percentage share on the right. You can also use the "Value Field Settings" screen to change the number format to reflect currency (not shown) by clicking on the "Number Format" button. You can also change the column headings in the Pivot Table on the "Value Field Settings" screen (not shown) by typing in the Custom Name box.)

Excel displays the share value instantly for the brands that make up whatever segment we have defined.

In our example, you can easily see that Private Label dominates Large Sized, Fat Free Milk with an 83% share of Fat Free/Large Milk. Using the drop down menus in Column B to select Single Serve and All Fat Contents, you can view your data in a new way.

Now you see that Private Label is dominant, but not as dominant in the Single Serve/All Fat Contents segment with a 39% share of category. You can see what a powerful tool Excel Pivot Tables can be to analyze your company's information. You can look at your data from many perspectives with the click of a button.

Insight, Information & Consulting Services, Inc. can help your company develop a powerful data visualization strategy using simple readily available tools such as Excel or we can help your company deliver data in a powerful thin client tool designed to build and update thousands of charts with the click of a button. Visit our website at www.insightinformation.net or call us at 770-425-4243.

Did you know that you can develop a Microsoft Excel template that you can distribute to your extended users such as the field sales force? You can leverage Pivot Tables within Microsoft Excel to develop impactful tools to drive actionable insights to the ultimate end users in your sales and marketing organizations.

Let me show you how to build an Excel template to distribute syndicated data to your sales team who don't have access to the data. Since most people have Excel on their computers this provides an easy way to get specific issue driven templates and information into the hands of the end users of the data.

The first step is to organize your data in a flat file. A flat file is defined as an unformatted square "chunk" of data as the following example will show:

We can now go to cell A1, from the insert menu select the pivot table option and chose the entire set of data to build a Pivot Table (I am using Excel 2007 to build our example). We can choose to create a new worksheet or we can decide to place the pivot table on the current worksheet.If you select ok, it will automatically define the range it interprets and default to a new sheet within the same Excel file:

We can drag fields from the pivot table field list to the various elements of the pivot table report to build our report layout:

Drag Markets (MKT) and Periods (PER) in the Report Filter section of the report so we can select one or more markets to view and a time frame (4 Weeks, 12 Weeks or 52 Weeks).

Drag the Products (PROD) field to the row labels box and since we want to see dollar shares and the impact of trended dollar sales.

Our report output needs to be presentation ready so we will want to have two separate graphs populated when we select a market. For example I want a pie chart to show the shares of the brands in a selected market and the bar chart to show the dollar trend changes for each brand versus the same period one year ago. To do this we can build data ranges outside of the pivot table to be charted in our template. First, let's set up the table for our pie chart:

Move to a separate place on your worksheet, beneath or beside your Pivot Table.

Use the "=" function to reference the data from the pivot table and fill the table you are creating.

Place an "=" in the new "pie chart" table, in the product name cell (A15 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.

Repeat until all products are represented.

Next, we will reference the dollar sales fields in the same manner

Place an "=" in the dollar sales value cell (B15) and then clicking on cell B6.

Repeat until all of the product's dollar sales are represented.

Now, let's create the table to be used for the bar chart.

Reference the percent change in dollar sales like we did in the pie chart, starting with the product name.

Place an "=" in the new "bar chart" table, in the product name cell (A23 in my example) and click in the cell of the first product (A6 in my example) in the pivot table.

Repeat until all products are represented.

Now, reference the percent change in dollar sales.

We will then produce each of two graphs (a Pie Chart and a Bar Chart) on one page of our template in the following manner:

Highlight the area to be graphed

Select the insert menu and pick the pie chart option

Format a pie chart as you normally would

Create a second chart in the same manner selecting the bar chart option

Now, for with a little cleanup effort such as:

Allowing hidden data to display in the graph

Right mouse on the graph object and pick "Select Data" for the menu

Click on "Hidden and Empty Cells" (bottom left in the dialogue box)

Click "On" the "Show Data In Hidden Row & Columns"

Align graphs under the pivot table drop downs

Hide the rows that show the data set for the pie chart and bar chart

Turn off gridlines and headings on the Page Layout Ribbon (Excel 207)

Your template is now completed and you can see market shares and volume trends in one easy to use template that can be printed off and presented to your customers:

The above template is a very simple example of creating an Excel tool to better visualize your data as real and actionable information. Additional levels of sophistication can be added such as intelligent text bullet points, dynamic rank reports, navigation buttons to navigate the user between multiple charts and tabular data visualization charts. In future editions of the Insight newsletter we will demonstrate how to build these enhancements into the template.