How to list relationships between Slicers and Tables/Charts VBA

Yep, ANOTHER post... To be honest, I've been working on this solution for a while and was quite excited when I finally got it working.  So, now I get to share it with you!

Have you ever created a monstrosity of a dashboard that has MANY slicers and MANY charts?  Well, I have, and it gets to be unruly quite fast.

OR, have you ever had to take over a dashboard developed by someone who is no longer around to make modifications or support the file?  Again, I have and it can be a bit daunting to have to figure out what they were thinking and their organizational strategies. 

One of the biggest issues that I have had is knowing which slicers are connected to which charts.  Yes, I know you can look at each and every slicer, go to "Report Connections" and see what is checked, but how do you then quickly look at the other related slicers that should be connected to the same charts as the first? 

Sure you could grab a couple of snapshots or write them down, but I must admit, that doesn't work for me.  Or to quote a viral YouTube sensation "Ain't nobody got time for that!"

So, after much complaining and digging through the VBA object library and looking around the internet (where I found the incomplete start for this code), I have developed the following:

Option Explicit


Sub MultiplePivotSlicerCaches()
    Dim oSlicer As Slicer
    Dim oSlicercache As SlicerCache
    Dim oPT As PivotTable
    Dim oSh As Worksheet
    Dim Row As Integer
    Dim ObjChart As Chart
    Dim x As String
'Create New Worksheet & Table Structure
Sheets.Add.Name = "HIDDENSlicerConnections"
Sheets("HIDDENSlicerConnections").Cells(1, 1).Value = "Slicer Name"
Sheets("HIDDENSlicerConnections").Cells(1, 2).Value = "Pivot Parent Name"
Sheets("HIDDENSlicerConnections").Cells(1, 3).Value = "Pivot Name"
Sheets("HIDDENSlicerConnections").Cells(1, 4).Value = "Chart Title"
'Add Slicer Cache Name and Pivot Table Name
Row = 2
    For Each oSlicercache In ThisWorkbook.SlicerCaches
        For Each oPT In oSlicercache.PivotTables
            oPT.Parent.Activate
            Sheets("HIDDENSlicerConnections").Cells(Row, 1).Value = oSlicercache.Name
            Sheets("HIDDENSlicerConnections").Cells(Row, 2).Value = oPT.Parent.Name
            Sheets("HIDDENSlicerConnections").Cells(Row, 3).Value = oPT.Name

            If InStr(oPT.Name, "Chart") < 1 Then GoTo Skip
            Sheets("HIDDENSlicerConnections").Cells(Row, 4).Value = oPT.PivotChart.Chart.ChartTitle.Caption

Skip:
            Row = Row + 1
        Next
    Next
   
End Sub

This code will create a table on a new spreadsheet called "HIDDENSlicerConnections" and list out every slicer and the chart/table that it is connected to. 

The first file that I used this on, I discovered that with all of the filters and charts and tables, I had a combined 238 connections in my file.  Again, "Ain't nobody got time for that!" - Okay, I'm done quoting....maybe.

Hold on, this is a cool macro, but it's no more than a raw data puke! We need to make sense of it. 

Next step is to create a pivot table based on the dataset.  Highlight the whole dataset, insert > PivotTable.  I prefer to keep the table on the same sheet as the dataset so choose a location and click OK. 

Now, to organize the data.  Do the following:
Row = Pivot Name
Row = Chart Title
Column = Slicer Name
Value = Pivot Parent Name: COUNT()

This will now create a table that shows the chart/table name down the left and Slicer name across the top and show a "1" for each connection that exists. SWEET!  No more snapshots and no more writing!  I'm down with that.

Edit: To use the Macro, follow these steps:

• Open your file > Press Alt + F11 to Open the VBA Editor
• In the left-hand pane, find the VBAProject([YOUR FILE NAME HERE]).  
• Right-click the "Microsoft Excel Objects" Folder and Select Insert > Module
• Copy/Paste the code into the module.  
• Click the "Play button" or Press F5 on the Keyboard to run the Macro.

If you are deploying your dashboard to SharePoint using Excel Services, your files cannot use Macros.  Simply continue to save as a .xlsx file and all will be well.  The Macro may not save, but you've already run it, and you can always find again right here on the blog!

Rolling 12 Months Chart with PowerPivot

Once again, work has provided me with a great article topic!  A request recently came across my desk to create a rolling 12 & 24 months average.

Here is how to do it:

First thing is first: You will need to have the “Last Updated” Table&Column in your Model.

 If you can connect to a SQL Server database, I create this by adding a table "Last Updated" with the following SQL as source (This also tells the users how new the data is):

Select GetDate() as ‘Last Updated'

This method is preferred to simply using the Today() formula in a linked table because your source data may not be refreshed daily.  For example, we have some financial dashboards that are only refreshed once per month on the first day of the month.

Next, create a calculated measure on the "Last updated" table with the following formula:

LastUpdatedMeasure:=sum([Last Updated])

Next, on your Fact table that you are going to use to create the rolling average (or whatever rolling metric you desire), add the following Calculated Measures to determine start and end dates for the rolling year ([last updated date] - [12 months]):

RollingYearEnd:=EOMonth([LastUpdatedMeasure],-1)

RollingYearBegin:=1+edate([RollingYearEnd],-12) - You can make this 2 years by changing "-12" to "-24".

Finally, use the following calculate formula to run whatever calculation you are looking for within that rolling year. This is the measure you will use as the “Value” on your chart or pivot table.

The formula below has multiple parts that I will explain:

=Calculate(expression, filter1, filter2..)

=Average(Column)

=Filter(TableName, TableName[Filter Criteria])

"&&" - This allows you to use an "AND" in your Filter Criteria.  Example: Event Date is >= RollingYearBegin "AND" Event Date is <= RollingYearEnd  - This ensures your data is between the start and end date. You can also include a column to qualify the event to be included by simply adding another "&&"

RollingYear_Avg:=calculate(average([ColumnToBeAveraged]),filter(FactTableName,FactTableName[EventDateColumn]>=[RollingYearBegin]&&FactTableName[EventDateColumn]<=[RollingYearEnd]&&FactTableName[QualifyingEventColumn]="Y"))

The final part of the formula &&FactTableName[QualifyingEventColumn]="Y" was required in my case because not all of the rows in my dataset qualified to be included in the average.  This is not required to make this formula work.

So, now that you are an expert in rolling year formulas, go out and impress the world!

Dynamic Chart Axis with Slicer!!! - Switching between Year/Quarter/Month/Week views

OH MY GOODNESS!!!!!

I've been looking for this for so long it's ridiculous...  I've always wanted to be able to simply use a slicer to change the row values on my pivot tables/charts from Year to Quarter to Month without needing multiple tables/charts. 

This guy is brilliant and very kind to share with all of us.  Please check his article.  It's awesome.

http://tinylizard.com/dynamically-changing-chart-axis/ 

Oh man, my job is so much easier now... :)

Pulling Table Data from PDF & Merging Multiple Sheets Into One

Okay all, this is a common problem that I've run into before... However, with updated software, the methodologies change a bit.  Here is my latest method of collecting table data from a PDF without purchasing adobe or another 3rd party software solution.

**NOTE: These methods were discovered online by searching.  I did not come up with them.  I'm just sharing, because that's how we get smarter together!

How to collect the data using PowerQuery:
http://simblais.com/simblogue/blog/2013/12/16/using-power-query-on-a-pdf-with-some-help-from-word/

**I use the "Load To..." option to drop the tables directly into a worksheet instead of the data model.

How to Consolidate the many worksheets into one sheet (I used the VBA code in the article):
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html

**I added the following line at the bottom of the code to show the final worksheet:  Sheets("Combined").activate

Again, these solutions are not my work, but definitely useful.  Please give these sites their due respect if you find this information useful.

How to show all dates in a powerpivot pivot table even when there is no data...

So, I've heard about this issue for a while now, but I never had to actually deal with it myself - until now, that is..

We have a system that records when a specific significant event occurs - we'll call this Event A.
Well, Event A doesn't always happen and frankly, we NEVER want it to happen.  So, when reporting month-to-month numbers, there are some times when a month shows "0" for the denominator and "0" for the numerator.  In a typical Excel pivot table, this isn't really a big deal you simply go into the properties and set the "Show items with no data on rows/columns" option to TRUE.

Unfortunately, with my particular power pivot model, it wasn't so simple.

My online investigation took me to this forum.  It was a lengthy conversation where multiple people offered the "easy" solution that ultimately didn't work.  However, there was one contributor that was spot on in understanding the issue.  It took me a while to pick through the conversation and get past the suggestions that still resulted errors to finally get at the solution I was looking for. So, I am going to attempt to explain what I did to save time:

The solution is this formula:

# of Event A:=sumx(filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

I have two tables involved:

• DimDateEvent - Date dimension table that consists of only monthly data - I will only ever report Event A on a monthly basis so no need to get more granular that Monthly.
• Note - I have a column in my table to measure the number of months from current month.  The results are both positive and negative.  
• =(YEAR(TODAY())-YEAR([@[Beginning of Month]]))*12+MONTH(TODAY())-MONTH([@[Beginning of Month]])
• Event - My fact table that has the Event information.

Formulas Used in the Solution:

• Sumx(<table>, <expression>)

• Filter(<table>, <filter>)

Calculated Measure used in Solution:

• Event[ID#] - this is simply Counta(Event[ID])

Normally, when creating a pivot table, the source data is the table with your event information and you "Look up" the remaining details.  However, for this solution, we are going to use our DimDateEvent Table as the data and "Look Up" the event information.

So, lets walk through the formula:

=Sumx(

Sumx - allows you to apply an expression to the data being added.

=Sumx(Filter(DimDateEvent,DimDateEvent[Months from Current]>0)

Filter - provides the DimDateEvent table filtering it to show only last month and every month before it.

=Sumx(Filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

I didn't want the table to show blanks, so I showed 0+ (this guarantees a value of "0") and then the result of the calculated measure Event[#ID] or in other words, the formula "Counta(Event[ID])".

So now we have completed the formula:

=sumx(filter(DimDateEvent,DimDateEvent[Months from Current]>0),0+Event[#ID])

Here is what it looks like in a pivot table: 

Note: No real data is being used here... The fiscal Year resets because I'm not showing the entire pivot table there is a Group A, Group B, Group C

No Demo File will be provided for this example.

Enjoy!

Show two labels on a single chart series

Just learned how to add a denominator at the base of a column on a column chart that shows an average (average is simply one example, but this could be used in multiple ways):

Note: I removed all identifying info from the image on purpose... 

Anyway, here's how to do it:

Create a chart for Average times and add the data lables above the columns.  

Now, add the second series that shows the count.  You will now have two Columns showing.  

Right-click the count series and set the overlap property to 100% and change the Count series' color to match the Average series' color.  The columns now overlap and look the same.  

Last, set the data label position for the count to show inside base. 

Anyway, thought I'd share.

Catching those slicer selections: A macro to do the work for you...

I've been working with PowerPivot for some time now and one thing that I've noticed on each of my solutions that I create is that I spend too much time creating the hidden sheet that "catches" each of the slicer selections that are active.  This can sometimes take hours if the project is big enough.

So, this last time, I decided to take 20 minutes out of my day do a little research on the Google and write a macro that would do exactly that for me.  Too much time is now almost no time at all and I have a bit of my work day back.  

Here is what I use. If you have suggestions on how to make it more efficient or just better practice (self-taught here folks!), please share in the comments!

Summary of Macro: 

Create a worksheet called "HiddenFilterCatch".  

Add Cubeset and CubeRankedMember formulas to new sheet (Prompting user for max number of options per slicer to catch)

Please excuse the blog formatting as I use a bit more screen real estate when coding.  Enjoy!

*Disclaimer - this worked for me in my specific scenario. You may/may not need to adjust the code to fit your situation.  :)

Option Explicit

Sub MultiplePivotSlicerCaches()

    Dim oSlicer As Slicer

    Dim oSlicercache As SlicerCache

    Dim oSh As Worksheet

    Dim SlcrName As String

    Dim Col As Integer 'Column Number

    Dim Connect As String, Comma As String, oPrn As String, cPrn As String 'Formula pieces

    Dim i As Integer 'Number of Slicers

    Dim Fmla As String 'Completed Formula

    Dim ws As Worksheet

    Dim x As Integer 'Max # of Filters determined by user

    Dim z As Variant

    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    

'Add New Worksheet "HiddenFilterCatch"

    z = MsgBox("Do you have a 'HiddenFilterCatch' Worksheet already?", vbYesNo)

    If z = vbNo Then

        Set ws = Sheets.Add(After:=Sheets(Sheets.Count))

        ws.Name = "HiddenFilterCatch"

    End If

'Count Slicers in Workbook (For Trouble Shooting Code Only)

    'i = 0

    'For Each oSlicercache In ThisWorkbook.SlicerCaches

    'i = i + 1

    'Next

    

'Set Variables for "=Cubeset()"

    Col = 3 'Column Number

    Connect = "=Cubeset(" & Chr(34) & "ThisWorkBookDataModel" & Chr(34) & ","

    Comma = ","

    oPrn = "("

    cPrn = ")"

    

       

'Distribute Cubeset Formulas onto spreadsheet

    Sheets("HiddenFilterCatch").Activate

    For Each oSlicercache In ThisWorkbook.SlicerCaches

            SlcrName = oSlicercache.Name

            Fmla = Connect & SlcrName & Comma & Chr(34) & SlcrName & Chr(34) & cPrn

            Cells(9, Col).Formula = Fmla

            Col = Col + 1

    Next

    

'Set Variables for "=CubeRankedMember()"

    Connect = "=IFERROR(CUBERANKEDMEMBER(" & Chr(34) & "ThisWorkBookDataModel" & Chr(34) & ","

    

'Distribute CubeRankedMember Formulas onto spreadsheet

    Col = 3

    x = InputBox(Prompt:="What is the Maximum Number of filters you would like to show?", Title:="Maximum Number of Filters", Default:="4")

    For Each oSlicercache In ThisWorkbook.SlicerCaches

            'Add formulas for CubeRankedMembers 1 to "x"

                For i = 1 To x

                    Fmla = Connect & Cells(9, Col).Value & ",Row(HiddenFilterCatch!A" & i & "))," & Chr(34) & Chr(34) & ")"

                    Cells(9 + i, Col).Formula = Connect & Cells(9, Col).Value & ",Row(HiddenFilterCatch!A" & i & "))," & Chr(34) & Chr(34) & ")"

                Next

            'Add "More than 'x' Selected..." Text formula

                Cells(10 + x, Col).Formula = "=IF(" & Right(Fmla, Len(Fmla) - 1) & "=" & Chr(34) & Chr(34) & "," & Right(Fmla, Len(Fmla) - 1) & "," & Chr(34) & "More Than " & x & " Selected..." & Chr(34) & ")"

            'Add named Range on Cell below "More Than" formula

                Cells(11 + x, Col).Select

                With Selection

                    .Name = Right(Cells(9, Col).Value, Len(Cells(9, Col).Value) - 7)

                End With

                Col = Col + 1

    Next

    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

    

    

End Sub

SPEED UP YOUR VBA RunTime!

WOW!  I just found a nice little help that improved my vba run time from 30 minutes to less than 1 minute!

So, I had a bit of VBA that I wrote to clean up and group a transaction level data set from a 3rd party report.  I noticed that on average it was taking up to FOUR seconds per row to clean, identify, re-organize, validate for duplicate entries, and write to a new table.  Essentially, 600 rows would take at least 30 minutes for the vba code to complete its task.  I have NEVER run into this issue before - Turning off Screen Updating has resolved any issues with speed I've had up until this point.

30 minutes just won't do.

So, I went hunting for solutions.  But, before I can solve the problem, I had to FIND the problem.

What is the problem?  Well, I found through the use of breakpoints where the lag in time was.  It was taking up to 3 seconds to simply place a batch of values that were stored in variables into empty cells. 

example line: 

Sheets("Converted").Cells(Rowc, 1).Value = Location

The issue was that Excel was recalculating the sheet every time a new value was placed in a cell.  So, how do we stop that? 

At the beginning of your vba code (probably right along with your line to deactivate screen updating) insert the following line:

Application.Calculation = xlCalculationManual

Then, at the bottom right before you end sub, insert the line:

Application.Calculation = xlCalculationAutomatic

BINGO!  Problem solved.

Find n-th space from the right...

Another quick post:

Have you ever had the wonderful pleasure of needing to pull all characters to the right of the 2nd or 3rd space within the string?  I know I have..

Solution:
*Disclaimer*: I have no idea where I found this beauty, but again, I FOUND it because someone else was willing to share - and sharing is the whole point of this blog...  I'm posting this just as much for myself as for anyone else. :)

Assuming your string is in cell C2:

=FIND(CHAR(1),SUBSTITUTE(C2," ",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2," ",""))-n))

For n:
("n" determines the number of spaces)
0=1
1=2 etc...

Update On Progress While Macro Runs..

Okay, I know it's been FOREVER since I've posted.  Long story short, I don't make money on the blog, but I do make money working....

Anyway, I found this AWESOME little tidbit for those of us who use VBA.

Situation:
I have a macro that takes a large chunk of data from a 3rd party .csv file, cleans it, and organizes it into a format that is usable.  The problem I have is that it takes a long time for it to run and I have no idea if it's hung up somehow or just slow.  If any of you have ever used a loop before, when the code takes a long time to run, it's possible you screwed up and it will loop forever!  It's nice to have an update on progress.

My Online Search Criteria: "excel vba msgbox update during code"

Solution:
I did NOT come up with this solution on my own, but I did find it on the stackoverflow site when I searched it out:  http://stackoverflow.com/questions/5459734/using-messagebox-without-pausing-the-application-vba

Anyway, here is the answer I liked:

Application.StatusBar = "Please be patient..."
Application.StatusBar = iDone & " of " & iTotal & " items done."

Using the Application Status Bar (Bottom left corner of your Excel window) is fantastic!  You can simply use a counter to display the number of the record you're on.

Well, that's it for today.  Hoping to get back into this as things slow down.

Using VBA to Reformat a Report

This post is going to be somewhat short and sweet - I'm not going to explain every little part, but I will provide a demo file for you to play with.

The Problem:
A report from a 3rd party vendor can only be exported to Microsoft Word is very pretty and in a table-like format.  However, each individual data point was contained in what appeared to be a textbox. Therefore, when attempting to copy paste the results into excel, all headers, footers, and data points are in their own cell, but in the same column.

Example of pasting report in cell A1:

**Note: I have changed ALL of the data and this report does NOT reflect anything real.

The Solution:

My mind immediately went to VBA for the solution this time.  Here are a few concepts that you need to know about to accomplish this task:

• Option Explicit - always use this to ensure all variables are declared
• Screen Updating - I use this to keep the screen from flashing when switching sheets and to make the macro run faster
• Do until Loop - This is used to step down the worksheet row-by-row until a certain condition is met
• Select Case - This is like a big IF/Then statement. 

When I create a "Formatting" macro, I like to have one spreadsheet for the original "pasted" format, and worksheet for the result after the macro runs.  This allows you to quickly, visually compare original vs new to validate accuracy of your code. 

The Code:

Simplified Explanation:  The macro looks at each populated row looking for the department name.  Once found, it gathers the information for that departments encounter and then places it in a more tabular format on the "Final" worksheet. It will do this until the "LastRow" (assigned by counting the populated rows) is evaluated.

Option Explicit

Sub Reorganize()

Dim row As Variant, lastrow As Variant, Dept As Variant, Isle As Variant, ID As Variant, CartoonAccount As Variant, CartoonName As Variant, Order As Variant, Row1 As Integer

Application.ScreenUpdating = False

Sheets("Paste").Activate

lastrow = ActiveSheet.UsedRange.Rows.Count

row = 1

Do Until row = lastrow

    If row >= lastrow Then Exit Do

    Select Case Cells(row, 1).Value

        Case Is = "Health"

            Dept = Cells(row, 1).Value

            Isle = Cells(row + 1, 1).Value

            ID = Cells(row + 2, 1).Value

            Order = Cells(row + 3, 1).Value

            CartoonName = Cells(row + 4, 1).Value

            CartoonAccount = Cells(row + 5, 1).Value

                

                    Sheets("Final").Activate

                        Row1 = 2

                        Do Until Cells(Row1, 1).Value = ""

                            If Cells(Row1, 1).Value = "" Then Exit Do

                            Row1 = Row1 + 1

                        Loop

                        

                        Sheets("Final").Cells(Row1, 1).Value = Dept

                        Sheets("Final").Cells(Row1, 2).Value = Isle

                        Sheets("Final").Cells(Row1, 3).Value = ID

                        Sheets("Final").Cells(Row1, 4).Value = Order

                        Sheets("Final").Cells(Row1, 5).Value = CartoonName

                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount

                        Row1 = 2

                        Sheets("Paste").Activate

                        row = row + 1

                                     

        Case Is = "FurnitureDept"

            Dept = Cells(row, 1).Value

            Isle = Cells(row + 1, 1).Value

            ID = Cells(row + 2, 1).Value

            Order = Cells(row + 3, 1).Value

            CartoonName = Cells(row + 4, 1).Value

            CartoonAccount = Cells(row + 5, 1).Value

                

                    Sheets("Final").Activate

                        Row1 = 2

                        Do Until Cells(Row1, 1).Value = ""

                            If Cells(Row1, 1).Value = "" Then Exit Do

                            Row1 = Row1 + 1

                        Loop

                        

                        Sheets("Final").Cells(Row1, 1).Value = Dept

                        Sheets("Final").Cells(Row1, 2).Value = Isle

                        Sheets("Final").Cells(Row1, 3).Value = ID

                        Sheets("Final").Cells(Row1, 4).Value = Order

                        Sheets("Final").Cells(Row1, 5).Value = CartoonName

                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount

                        Row1 = 2

                        Sheets("Paste").Activate

                        row = row + 1

        Case Is = "Toys"

            Dept = Cells(row, 1).Value

            Isle = Cells(row + 1, 1).Value

            ID = Cells(row + 2, 1).Value

            Order = Cells(row + 3, 1).Value

            CartoonName = Cells(row + 4, 1).Value

            CartoonAccount = Cells(row + 5, 1).Value

                

                    Sheets("Final").Activate

                        Row1 = 2

                        Do Until Cells(Row1, 1).Value = ""

                            If Cells(Row1, 1).Value = "" Then Exit Do

                            Row1 = Row1 + 1

                        Loop

                        

                        Sheets("Final").Cells(Row1, 1).Value = Dept

                        Sheets("Final").Cells(Row1, 2).Value = Isle

                        Sheets("Final").Cells(Row1, 3).Value = ID

                        Sheets("Final").Cells(Row1, 4).Value = Order

                        Sheets("Final").Cells(Row1, 5).Value = CartoonName

                        Sheets("Final").Cells(Row1, 6).Value = CartoonAccount

                        Row1 = 2

                        Sheets("Paste").Activate

                        row = row + 1

        Case Else

            row = row + 1

    End Select

Loop

Application.ScreenUpdating = True

Sheets("Final").Activate

End Sub

I LOVE VBA - What can I say, it was my first programming language.. I'm self-taught so if you are an expert and have a better way to accomplish this, feel free to contribute to the conversation!  Again, we're here to learn together- from each other.  

Demo File (Does Contain Macros!): 

Rolling 12 Months Chart

I know I haven't posted in a while... I've been crazy busy with work, moving, and life in general.. But things are settling down and I hope to get back on track.

Recently I was asked to create a chart that could function as a "rolling 12 months" chart.  If using Power Pivot in Excel 2010 or 2013, this would have been quick and easy using a date/time dimension table to slice the data.  However, this needed to be supported in Excel 2007.  Soooo, I had to get creative.

Here is what I came up with -

Specific formulas needed:
=EDATE(start date, months)
=VLOOKUP(Lookup value, table array, col_index_num, [range_lookup])

To begin, we have our data set:

This is simply a random data set that I generated for this example with no true significance in the real world... Note: The dates in column "A" were entered as "6/1/2012" and then formatted within the cell to only show month/year.  I always use the first day of the month when working with month-year formats.  Not sure if this is best practice, but it is my own standard since there is always a day "1" in every month.

Next, I created a table that will show the 12 months' data:

Above the table I simply added "Last Month" and "Last Year" fields for data entry.  These will be used by formulas within the table.  The numbers in the yellow cells are manually entered/changed - although I suppose you could use a data validation drop-down if you wanted to limit what could be entered.

Anyway, Here's how I created the table:
In the bottom left cell of the table ("Jun-14") I added the formula: =DATE(I1, G1, 1) where G1 is the value entered in the yellow cells for "Last Month" and I1 is the value entered in the other yellow cell for "Last Year" - Last month and Last year meaning the last month/year you wish to be shown on the chart.

In the cell above the "Jun-14" cell, I use the formula: =EDATE(F14,-1) where F14 is the date that we just created in the bottom left cell of the table, and "-1" tells excel to subtract 1 month.

I simply continue or "Auto-Fill" the formula to the top of the table.  Now, when you change the date parts in the yellow cells, the dates in the table will automatically adjust.

Next, we use a vlookup formula to go and gather the data that corresponds with the date in the first column of the table. By using the "dynamic" dates we've created, our data will also be dynamically selected from the original data set. - Since I've addressed vlookup in past posts, I'll skip this part.  I will post a snapshot of the table showing the formulas at the end though.

The last column in the table is simply a preference.  I create the numerator/denominator for the data label on the chart.  I do this simply by concatenating the values using this formula: =H3&"/"&I3 (this results in the value at the top right of the table: 1091/1100).  I find it very helpful to include the n/d when displaying percentages in a chart.  This shows the reader if 50% is 1/2 or 500/1000.

Final Snapshot of table showing formulas:

Last, I create a simple line chart using the "Month" and "Success%" columns in our table.  Add data labels to each of the data points on the chart.  To create dynamic chart data point labels simply click on an individual label, and in the formula bar type "=" and the cell reference of the value you are looking to display - In this case, the first label on the left would be =$J$3.

Since we created the table using relative references instead of specific numbers and values, when the Last Month and Last Year are changed at the top, the data in the table as well as the data on the chart are instantly updated to show the "rolling 12 months".

I know this was a bit rushed...  That's why I'm including an example file this time!  You can get it here.

Page Filters on Pivot Tables

So, I learned something new about pivot tables today and thought that I'd share.  Perhaps you already know this, but I'm going to tell you anyway because I think it's awesome!

I'm going to use a free data set found on Azure Market Place for this example.  It is the GDP By State Estimates of Compensation (1997-2011) set.

Description of "Awesomeness":  I learned how to take a pivot table that has filtering options and create an individual spreadsheet with a table reflecting the data for each of the items listed in the filter(s).

**Update: this is not available for PowerPivot Pivot tables...  http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=78  **

Here's how:

Create a pivot table and add a filter:

In this case, I have used the "GeoName" (state name) as my filter.

Next, click anywhere on the pivot table and go up to the Ribbon at the top.  Click "Analyze" under "PIVOTTABLE TOOLS".  Click on the "Options" drop down in the pivot table option set on the left (See image below):

Now, Click on "Show Report Filter Pages..."

A pop-up will appear and ask you to select which filter to use.  I click on GeoName then OK.  The final result is a new spreadsheet for each of the filter options that has a pivot table based on the original data source!!

I know, it's pretty sweet.

INDEX() & MATCH() - the new VLOOKUP()!

Sorry it's been a while, but life and a full-time job have been crazy of late... Enough about me though, let's get to it!

The other day I was called into an issue where the user needed a formula that would look up the results of specific tests based on the month that they were run.  He wanted to enter a month into a cell and the formulas would return the values of the tests for that month.  In response, I created a demo spreadsheet (No Macros) to show him how to get the result he wanted.

His initial question was "Is there a way to do this VLOOKUP()?".  Yes, this could be accomplished with VLOOKUP() assuming the tables were organized appropriately, but I wanted to introduce a new method. VLOOKUP()is a great formula, but has it's limitations.  For example, Column1 of the table referenced when looking for a specific value MUST be the left-most array column that contains that value...

While I'm sure there would be a convoluted way to go about accomplishing that task by counting the month from an original start date and using that as the column reference in the VLOOKUP() formula, I felt it would be a better time to use the INDEX() and MATCH() formulas to find the intersections of row and column.

Here's how I accomplished it:

Formulas:
INDEX(array, row_num, [column_num])
MATCH(lookup_value, lookup_array, [match type])

Data Set:

The user's file was set up so the column contains the test name (run only once per month and not duplicated) and the following columns are the results of the tests under each month.

Now, off to the right of the data set, I created an example of how to set up a quick reference for the month's data:

The Yellow cell is where the month is entered and cells P2:P7 contain the formulas that refer to the month and test name to return the appropriate value.

On to the formula... In cell P2, Start with the INDEX() formula.  The array is the data table in cells A1:I7 so we now have:

=INDEX($A$2:$I$7,

The next step is to identify which row we're interested in.  We use the MATCH() formula for this.  The items we need for MATCH() are:

Lookup_value = O2 (this is the name of the test in question found in column "O")
Lookup_array = A2:A7 (This is the column containing the test names)
Match_type = 0 (zero - this means "Exact match with lookup_value)

So now we have:

=INDEX($A$2:$I$7,MATCH(O2,$A$2:$A$7,0)

Next, we need the column reference. Again, we use the MATCH() formula for this. The items we need for MATCH() are:

Lookup_value = M1 (this is the name of the test in question found in column "O")
Lookup_array = A1:I1 (This is the row containing the month names)
Match_type = 0 (zero - this means "Exact match with lookup_value)

Now, we have a completed formula in cell P2.  Prior to copying the formula down, be sure to add the "$" where you need it to keep the references from shifting when they shouldn't - See the "Dollar Signs in Formulas" post if this doesn't make sense.

Final Formula:

=INDEX($A$2:$I$7,MATCH(O2,$A$2:$A$7,0),MATCH($M$1,$A$1:$I$1,0))

Now, when you change the Month in the yellow cell, the formula will match that month with each of the results and return the intersection or result of the test!


Now, I understand that this is not the only way to accomplish this task, nor is it the only way to use these formulas, but it IS the way I chose to solve the user's issue.

Guest Contribution: Formula to Show Beginning of Week

Hey all!  Today's post is provided by guest contributor Jerry Shelton.  Jerry is a Project Manager at Hewlett Packard as well as an HP Sigma Plus Certified Black Belt.    Lucky for us, he has provided a wonderful example of how to take a date/time stamp from a data set that was exported from a database and determine the beginning of the week to allow for simple grouping within a pivot table/chart.

This solution is a real-world example that was born out of the need of others that were not too excel savvy to create reports from data sources with similar formatting.  This template allows them to simply copy/paste the formulas shown in this example into their own data and move from there with very little training or guidance from others.  I fully support making life easier for others!

Here's a link to the file with the "How To" included inside:

Beginning of Week - Formula Example

**Note: The above file does not contain Macros.

Cheers!

Concatenation and Searching for text in a string

Today I was asked to create a unique identifier by obtaining the initials of a name and adding on the last 4 digits of a specific encounter number.  This required the following functions:

Left(text, [num chars])
Mid(text, start_num, num_chars)
Right(text, [num_chars])
Search(find_text, within_text, [start_num])
and the symbol used to concatenate values/strings: "&"

Here's the dataset:

Here we have the names list by LastName,FirstName and the ID#'s.

The assignment: "Provide Initials (First then Last) and the last 4 digits in the ID#"

**For this example, we're going to assume all of the names are in the exact same format down every row.

Let's Begin:

In Cell C1 we'll provide a column header of "New ID".  In cell C2 we'll go to the formula bar and begin with our formula.  Since the First initial is in the MIDDLE of the text string and and be found after a comma, we'll use the Mid() and Search() formulas here.

For the MID() formula we need to assign values to the following requirements -

• text: the text we're referencing is the Name column and found in cell A2
• start_num: we need to search for the "," and start with the character immediately after it (+1)
• Search requires the following to be addressed:
• Find_text or Text to search for: "," (include the "" around the comma in the formula for this one)
• within_text: cell A2
• [start_num]: "1" - this is the first character in the cell A2
• num_chars: 1 - we only want the first letter of the first name

So, now that we have this written out in words, let's convert it to the formula:

=MID(A2,SEARCH(",",A2,1)+1,1)

This returns "S" in cell C2.  Next, we need the First letter of the Last Name.  This one is a bit more simple since it is the beginning letter of the text string.  We'll use the LEFT() formula

For the LEFT() formula, we need to assign values to the following requirements:

• Text: A2
• [num_chars]: 1

This will provide the first character from the left using the value in cell A2.  The formula looks like this:

=LEFT(A2,1)

So, we want all of this in one cell, right?  How do we enter more than one formula?  Concatenation! 

All we have to do is combine the two formulas by replacing the "=" at the beginning of the second formula with "&" and append it to the end of the first formula:

=MID(A2,SEARCH(",",A2,1)+1,1)&LEFT(A2,1)

This now returns "SD" in cell C2.  Last, we need the last four numbers in the ID#:  We'll use the RIGHT() formula to accomplish this task:

For the RIGHT() formula, we need to assign values to the following requirements:  

**Instead of looking at the left of the text, this formula starts at the right**

• Text: B2 - not A2, because the numbers are in B2!
• [num_chars]: 4

This will provide the first four characters from the right using the value in cell B2.  The formula looks like this:

=RIGHT(B2,4)

Again, we want all of this in one cell, right?  Let's concatenate by replacing the "=" with "&" and appending it to the end of the formula we've built so far:

=MID(A2,SEARCH(",",A2,1)+1,1)&LEFT(A2,1)&RIGHT(B2,4)

This now returns "SD3456" in cell C2.  Let's look back at the assignment: "Provide Initials (First then Last) and the last 4 digits in the ID#"

Now that we know we've accomplished what we wanted, we can copy the values down the column and click SAVE!

The final result:

AND the final result showing the formulas used:

The task I had to accomplish was a bit more complicated involving dates of service as well.  The last step that you would want to do is validate that the "New ID" was truly unique across your data set.  To accomplish this, you could simply create a pivot table, add the "New ID" and then Name as the Row values and add New ID to the values section as a count of New ID.  This would quickly show if you have any duplicates by listing multiple names under the same ID.

Hope you enjoyed this tutorial!  Instead of offering to donate lots of your money to me, let other people do it by clicking on an advertisement to the right!

PowerPivot: BLANK() & ISBLANK()

Today I ran into an issue when writing a formula in the calculated column area.  I needed to calculate the difference in minutes between two date/time columns.  However, I first wanted to check to be sure both columns in the row had an entry to avoid some outrageous number appearing as the result of the formula.

In excel I would have simply handled this issue with an If() Statement check to be sure the cell wasn't blank and then returning "" if it was.

The formula in an Excel spreadsheet looks like this:

=if(A1="","",if(A2="","",(A2-A1)*24*60))

Unfortunately, when used in the data model this will return "#Error" for all rows in the column instead of a blank value.

After consulting with a coworker, he showed me how he has resolved this issue:

=If(ISBLANK([Column1]),BLANK(),if(ISBLANK([Column2]),BLANK(),([Column2]-[Column1])*24*60))

The ISBLANK() formula simply checks to see if the value in the column is blank.  If it is, BLANK() returns the value as a blank cell - Much the same as using "" in an Excel Sheet formula.

To Explain the formula, I will write it in words:

If the value on this row of column1 is blank then return blank, otherwise go check if the value on this row of column2 is blank.  If it is, return blank, otherwise calculate the difference between Date2 and Date1.  Multiply the result by 24 to get hours.  Multply the hours by 60 to get minutes.

Hope this helps someone else save some time!

Question for the Audience...

Okay all, I have a problem that I don't know how to fix.  HUGE points to one who has an appropriate solution.

The Problem: How do I default a text value slicer selection WITHOUT using VBA?  Is it even possible?

Why no VBA? Our company is a bit behind the times in upgrading us all to the same version of office.  I have Excel 2013, but the rest of the world is using 2007 (No slicers available).  Therefore, I post my files to a SharePoint site and the end users are able to use them via Excel Services (hence the no VBA).

I am searching all over for this answer right now and will gladly share the how-to if/when I figure it out.  I would gladly host a "Guest Post" if you know how to do this already.

Thanks!

Convert Text to Columns - Separating Last, First Names

Have you ever had a set of data that you wanted to separate across multiple columns?  "Text to Columns" is just for you!

Here is our Data Set:

The Request: "Please separate the client names into separate last name and first name columns"

This is SUPER EASY, so here we go...

1. Highlight the data you wish to separate

2. On the Ribbon, go to "Data" > "Text to Columns" in the "Data Tools" category

3. You should see this:

4. Since our data is separated by commas, we will select "Delimited" and click "Next"

5. Place a check in the "Comma" Checkbox and deselect the others

6. Look at the Data Preview to see what the output will look like prior to proceding:

7. Click "Next"

8. This screen will allow you to format the text by column.  For this demonstration, we're happy with a               "General" format (the default).

9. In the Destination box, enter the location where you wish the first data point to appear.  In this case, let's select "$B$16" - just to the right of the names that we are using for this example. 

10. Click "Finish".

You should now see:

Enjoy!

UPPER CASE, lower case, Proper Case

Today's post is about changing the case of your source text to UPPER CASE, lower case, or Proper Case.

Formulas we'll need:
=Upper() - ALL CHARACTERS IN UPPER CASE
=Lower() - all characters in lower case
=Proper() - All Characters In Proper Case

Here we can see that we have a list of names.  Unfortunately, the names were simply entered without a specific format... Let's fix these:

In Cell B2 I'm entering "=Upper(A2)".
In Cell C2 I'm entering "=Lower(A2)".
In Cell D2 I'm entering "=Proper(A2)".

I then copy these formulas down to row 7 (or use auto-fill).  Here's what we have:

As you can see, the formulas convert the text just as they should.  So, how do we deal with this in a large table full of data?

1. Insert a new column (temporary) next to the column with the text you wish to convert
2. Use the desired formula in the uppermost cell (below the header) of the new column
3. Copy the formula down the column or use auto-fill
4. Copy the data in the new column
5. "Paste Special" > "Values Only" to the original column - this replaces the original data with the newly formatted data.
6. Delete the temporary column that you inserted,

Your data should now be formatted the way you want it.  As always, if there is a step that you are not familiar with, please let me know and I can further explain.  

Calendar Control

If you've ever had to create a data entry spreadsheet you know how important standardizing is.... That is, if you plan on having to do ANY analysis without having a huge clean-up job first.

Today's post is about dates and calendar controls.

For the longest time, I labeled my column with a header that looked something like this:

Date
(mm/dd/yyyy)

I was trying to indicate to the user that they should simply enter the date in the format that I had shown.  YEAH, RIGHT! Apparently, that is asking too much.  So what was I to do? Well, I looked online for solutions to my problem. Unfortunately, the solution often came as an add-in that would need to be locally installed/registered on every machine that would be using the calendar control.  To begin with, I can't download and install files on my machine at work due to company policies.  To add to the problem, my files are shared across our network and have been used by upwards of 100 different people. So, this was not going to work.

Therefore, I decided to create my own in VBA! Rough though it may appear, this little control does the trick nicely.

How it works:
I first built a calendar on the "CalendarControl" spreadsheet.  This has a list of years and Months and drop-downs for each.  The calendar days on the spreadsheet look at the year and month selected to determine what day the first date should appear on.  The rest then follow suit.  So, the calendar control is really just a prettied up Graphic User Interface (GUI) of the control on the spreadsheet.

Here is a pic of the calendar in action:

I have set up the calendar to initiate when the user double clicks in a cell that is in a date column (In this case, it is column E).  The following code was added to the worksheet to get the calendar to only appear when the user double-clicks in colums E, L, and M:

Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Select Case ActiveCell.Column
        Case Is = 5
            FrmCalendar.Show
        Case Is = 12
            FrmCalendar.Show
        Case Is = 13
            FrmCalendar.Show
        Case Else
    End Select

End Sub

All the End User has to do is choose the Month/Year and click on the day.  The control will automatically add the pre-formatted date to the active cell and then disappear to allow the user to continue entering data.

The link below will allow you to download the Calendar Control file.  I have successfully tested it in Excel Versions 2007, 2010, and 2013.

https://drive.google.com/file/d/0B_cZb61zmYEfY0NpbkFILUdZcUE/edit?usp=sharing

**NOTE: This is a "Bare Bones" copy of the tool ready for you to implement how you wish.  The downloadable file is currently set up to add a date to whatever cell is active - This is why I added the worksheet code to only allow the calendar to launch when double-clicking in predetermined columns.

Feel free to change the code/form in any way you like.  If you like these tools and/or posts, please refer you friends and colleagues!

As always, we're all working to be better at what we do, feel free to share comments and thoughts (But keep them professional, please!).

(SOLVED) Slicer Selection in Chart Title without VBA!!!

So, I've been looking ALL OVER the internet to find a solution to this problem... Everywhere I look I read something like, "No, it's not possible" or "In a word, no".  Well, that's just not good enough for me.  I specialize in interim solutions and work arounds... SOoooo, after much research and trial and error, I've come up with a way to do just what we're all asking about - "How do I show the slicers selected in my Power Pivot chart?"  Since we upload our files to SharePoint and the end users are using Excel Services because they're working with Excel 2007, we can't use VBA for any of our solutions that have a data model.

Here's what we'll need (Remember, I'm using Excel 2013, NOT 2010 with the Add-In):
Data in the Data Model
Pivot Table
Pivot Chart
Slicers (Connected to the Pivot Table)

Formulas:
Cubeset(Connection, Set Expression,[Caption],[Sort Order], [Sort By])
CubeRankedMember(Set)
IfError(Value, Value if Error)

Data:  I'm using World population Data found at: http://www.census.gov/population/international/data/worldpop/table_population.php

I added a Decade column to assist me in quickly grouping the data by decade.

Now, I'm adding this table to the data model simply by:

• Highlight the dataset
• Click the PowerPivot Tab at the top
• In the Ribbon, click "Add to Data Model"

There are multiple ways to add data to the model.  Typically I will connect directly to a data source instead of adding what I already have in the current spreadsheet, but that can be a discussion for another day.

Now that we have our data in the data model, let's stop and discuss a few very important details about the following steps.  It is imperative that we create a pivot table linked to the data model and NOT just a pivot chart.  The issue with linking a chart title or axis label to a cell can be bypassed simply by generating a pivot chart from a pivot table because it is then not directly linked to the data model.  This was probably the BIGGEST issue with the whole process.. I discovered the issue when I right-clicked and selected "Pivot Chart Options" for both a chart based on pivot table and a chart based on the model.  If you do this side-by-side, you will see that the chart connected to the data model has a window titled "PivotChart Options".  However, if you do this with the chart based on the Pivot Table, you will see "PivotTable Options".  Excel sees these charts differently.  To simplify and summarize, you can do all things to a chart based on a pivot table that you could do with the chart based on a dataset in a spreadsheet.  You cannot do this with a chart based on a data model.  

Back to the DataModel:

Within the Data Model window click the following:

• Home Tab
• PivotTable > Pivot Table
• New WorkSheet > OK

Create your pivot table as you need it.  If you're following me using the WorldPop data, I'm using:

• Rows = Decade
• Values = Average of Annual Growth Rate (%)

This is what we have:

Let's Add our Slicer. On the Ribbon Click:

• Insert
• Slicer (Found in the Filters Group)

If asked, link this to your data model.  Select "Decade" from the column list under the table and click OK

Your slicer will look similar to this:

Link the slicer to the Pivot table by:

• Right-Click the Slicer
• Report Connections
• Check the Pivot Table you want the slicer to control
• Click OK

Now, Let's create our Pivot Chart:

• Click somewhere on your Pivot Table
• Under "PIVOTTABLE TOOLS" On the Ribbon, click "Pivot Chart"

• Let's choose a Clustered Column Chart
• Click OK

We now have:

I've selected a few of the slicers to demonstrate that the Slicer is working properly.

Now, to the fun part!  How do we get the slicers selected to show on the chart?

Formulas:

CUBESET(Connection, Set Expression,[Caption],[Sort Order], [Sort By])
CUBERANKEDMEMBER(connection, set_expression, rank, [caption])
IFERROR(Value, Value if Error)

When I build scorecards and dashboards I prefer to have a control sheet that is hidden that contains formulas that I don't want other's to booger up.  So I'm inserting a new sheet titled "ControlSheet" From here, the cell references will be in reference to sheet "ControlSheet"

**The formulas that follow differ slightly if you are using Excel 2010 (PowerPivot v1 and v2)- The "Connection" referred to in the Cubeset formula will always be "PowerPivot Data". "Connection" for Excel2013 is "ThisWorkbookDataModel"

In Cell A1, type the following:

• =CUBESET("ThisWorkbookDataModel",Slicer_Decade, "Decade")

I simply leave the sort options blank. This will populate Cell A1 with a linked reference to the DOS Quarter Slicer.

Next, In Cell A2, type the following:

• =IFERROR(CUBERANKEDMEMBER("ThisWorkbookDataModel",$A$1,ROW(Sheet1!A1)),"") - Remember, "Sheet1!" is the name of whatever sheet you're working in

The formula does the following:

• If no slicers are selected, an error would be returned, so "IFERROR" returns a blank value.
• Otherwise, if all slicers are selected (no filter) the value will be "ALL"
• Otherwise, if one or more slicers are selected, the first value in the list of slicers will be returned

Now, since we have  10 decades in our slicer, let's use this formula 9 times (since 10 = "ALL").

• Validate that your formula in cell A2 has the dollar signs around A1 ($A$1).
• Copy the formula down to cell A10 (or Auto-Fill).
• I typically use the "All Borders" Border around the range to indicate that the cells are being used even if they are blank.

Next, test your Slicer by Selecting Multiple Values and see what is returned by the formulas you've just created.

 Now that we are "Catching" our slicers, we can Create a label like "Filters Used: " , concatenate the values and link to our chart. 

Let's do it!

In Cell A12 on ControlSheet, Type "Filters Used - " 

In Cell A13 Copy/Paste the following formula

• =A2&IF(A3="","",", "&A3)&IF(A4="","",", "&A4)&IF(A5="","",", "&A5)&IF(A6="","",", "&A6)&IF(A7="","",", "&A7)&IF(A8="","",", "&A8)&IF(A9="","",", "&A9)&IF(A10="","",", "&A10)

This formula will always return the value in Cell A2, but will check to see if the following cells are blank before returning the value.  If it's blank, then it returns a blank value.  If it's not, it adds a comma and then the value of the cell.

In cell A14 copy/paste the following formula:

• =A12&TRIM($A$1)&": "&TRIM($A$13)

You should see:

In cell A15 concatenate your Chart title with the value in Cell A14..  I prefer to add a line between (carriage return).  My formula looks something like this:

="Avg World Population by Decade "&char(10)&A14

*Char(10) will add the carriage return we need

Now, let's return to Sheet1 and our chart...

Click on the chart title and in the formula bar type "=ControlSheet!$A$15"

You will see the this:

As you change the filters in the slicer, the title will change with it!

For the longest time I thought "Let's put the filters in the Title" was the best idea EVER!.. However, once I did it, I realized it wasn't a great Idea.  If you have multiple slicers you've incorporated into your title it becomes WAY oversized and unruly.  You see, when you link the title to the cell, ALL of the text is the same font size - you can't make the filter text smaller than the title text.  For me, the title should be the focus and the filters just there if you need them.

Let's solve this: ADD A HORIZONTAL AXIS LABEL! - Since the data in this case is very self explanatory we don't need the axis label to describe the axis.

• Click on chart
• On Ribbon, Click "Design" in PivotChart Tools
• Click Add Chart Element
• Axis Titles > Primary Horizontal
• Click on the Axis Label on the chart
• Paste "=A12&TRIM($A$1)&": "&TRIM($A$13)" into the formula Bar
• Change the font size to "8"
• Change your Chart Title by simply typing the title in "Avg World Population Growth Rate (%) By Decade"

You should now see this:

I hope you all enjoy this!  It's been a royal pain trying to work without this solution, so if you share it, please share some of the credit with me if you reference this.

Thanks!