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!

Favorite Excel Shortcut Keys

Hello all,

Here are a few of my most used/favorite short cut keys for Excel.  Not having to always rely on using the mouse has made me much more efficient in my work.

Quick Functions:

Ctrl + V = Paste
Ctrl + X = Cut
Ctrl + C = Copy

Ctrl + Z = Undo
Ctrl + Y = Redo
Ctrl + S = Save
Ctrl + O = Open
Ctrl + P = Print

Ctrl + B = Bold
Ctrl + I = Italicize
Ctrl + U = Underline

Ctrl + T = Open the Insert Table Dialog box
Ctrl + F = Find
Ctrl + H = Find/Replace

F2 - Open/Activate a cells contents for editing
Alt + F4 = Close Current Window

Tab - Close Cell Editing and move 1 cell to the Right
Enter - Close Cell Editing and move 1 cell Down

For movement without using the Mouse:

Arrow directional Keys (Up, Down, Left, Right) = Move 1 cell in that direction
Ctrl + Home = Goto Upper-Left most cell in spreadsheet
Ctrl + End = Goto Lower-Right most cell in spreadsheet
Ctrl + [Arrow direction Key] = Goto the end of column/row or next empty cell in that direction
Ctrl + PageUp = Spreadsheet Tab to the Left
Ctrl + PageDown = Spreadsheet Tab to the Right

For Highlighting Cells:

Use the Movement Keys above, but hold "Shift" as well.


For a comprehensive list of shortcuts, go to the following Microsoft Link:
http://office.microsoft.com/en-us/excel-help/excel-shortcut-and-function-keys-HP010073848.aspx

Numbers as text??

I was asked the other day about data exported from an access data base where the numbers where displayed as text values in Excel.  If you have ever run into this, you know that it's not a super simple task to change the format of those cells to "Number", because after you change the format type of the cells to "Number", you then have to activate the cell (F2) and exit the cell (Enter/Tab) for every cell.  So how do you get around this?

Let's use this example below:

Column A has Ages that are in a text format (notice the green triangle at the top-left of each cell - if you hover the cursor over this, excel will display a message that the numbers are in text format)

Simply add a new column next to the column you're working on (column B). In cell B2, type the formula "=A2*1".  Auto-fill or copy the formula down the column.

The result of the formula will provide you with the numeric format of the age.  Now you can simply copy the values in column B and "Paste Special > Values Only" in column A.  The age column values are now Numeric and no longer Text.

Delete the new column (Column B) to restore your data to it's original structure.

Dates and Pivot Tables

I was asked the other day how to show just the month within a pivot table row using a field that has dates formatted like "1/1/2014" throughout the year.  As I discussed this further, I found out that it would be useful to have the month show and then all of the dates within that month below it.

Today we're going to address methods to accomplish these tasks:

#1 - In a cell or cells, how do I show only the month in a year?  Let's Use "1/1/2014":

• =Text([cell reference with date value],"mmm") = Jan
• "m" = 1
• "mm" = 01
• "mmm" = Jan
• "mmmm" = January

• =Month([cell reference with date value])
• This will give the number of the month = 1]

• Right-click on column with the dates > Format cells > Number Tab > Custom > Type = "mmm" (use one of the above)

#2 - In a pivot table, how do I group the dates by month in the row category?

First, let's check to see that our data table is ready for a pivot table... What are we missing?  Well, in order for a pivot table to function properly and include all columns selected, each column must have a column header.  In this data set, we're missing the header in column A.  Let's add one and then insert our pivot table.

To insert a pivot table: Highlight your data table (A1:D9) > Insert > Pivot Table > New Worksheet > OK

Now, we're ready to include our dates in the Rows.  Add the "Date of Purchase" field to the Rows area:

The Resulting table looks like this:

Now, lets group by the date:

1. Click on a date in the pivot table
2. Right-click the same date you just selected
3. Click "Group..."
4. For this example, choose both "Months" and "Years" (**to de-select an item just click it again!) 
5. Click OK

**Tip: If you are getting an error when trying to click "Group...", check to make sure that none of your cells in your date column are blank.  Excel will not allow you to group with blank cells.

Now, Add the Price field to the values field in your pivot table.  Your pivot table will now look like this:

The above example shows us how to group the data by year and month.  

Now, how do we add the individual dates underneath the month?  For this, we will need to make some additions to our table.  Add the following formulas into the columns next to the table:

Auto-fill or copy/paste the formulas down to the end of the table.  

*I'm also going to change the bottom date value to 2/2/2014 for purposes of this example.

Use the following steps to create the desired table:

• Highlight the data table
• Insert Pivot Table on a New Worksheet
• Add (in this order) Year, Month, Date of Purchase to the rows section of the pivot table
• Add Price to the values section of the pivot table

You now have a pivot table that groups the data By Year, By Month AND has individual line items under the month.

Last, since we're looking at dollars, let's format the "Sum of Price" column as currency:

• Double-click on "Sum of Price" column header in the pivot table
• Click the "Number Format" button at the bottom of the Value Field Settings pop-up window
• Choose "Currency" and your preferred format for negative numbers
• Click "OK"

You now have this:

There are lots of great ways to make data more meaningful through the use of pivot tables.  In the future I will discuss using Power Pivot, data models, and slicers to be more efficient and effective in your work.

Creating a useful Process Chart (Using Excel 2013)

Today's post is about creating a Process Chart that will help you to visually identify areas of opportunity within a process.  I work within the healthcare industry.  It is often very important that we provide timely care in an effective manner.  The chart example below is something that I developed  for our team as we were trying to track times from patient arrival to the hospital to when each step was completed in our process, and ultimately, the time a given treatment was administered.  When you already have an idea of how long a process should take, it becomes a matter of comparing expected times with actual times and identifying where the delays occurred - these are the opportunities that need to be investigated further to see what was different/caused a variation in the process for that individual case.

Alright, enough chit-chat - Let's DO IT!

Example Data Set:
It is important to note, that when tracking times, the time for each step needs to be in relation to the "start time" or a common point of reference. In this case, start time = 0.

Create the Chart:
Highlight the Data Set (Cells A3:G7).
Click "Insert" Tab at the top
In "Charts", select the "Line With Markers" chart

Right-click the white chart area (Up near the title works)
Click "Select Data"
Click "Switch Row/Column" button - this will place the process steps in the "Series" column and the attempts in the "Category" column.

Look at the chart:

Add Lines to indicate time between steps:
Left-click the chart
Go up to "Chart Tools" > Design
Click "Add Chart Element" on the Ribbon > Lines > Drop Lines

Remove series lines but leave the markers:
Click a Series line (Step1, Step2, etc...) > Right-click on the series line
Click  "Format Data Series"

Click the paint can image and select "Line" in the options below the paint can.

Click the "No line" radio button

Look at the chart:

Repeat steps to remove line for remaining series (steps).

**Tip: If you do not close the "Format" pane after you select "No line", you can simply click on the next series line and format pane will remain open.  This will allow you to quickly go through each series and remove the lines without the "Right-click" steps.

Changing the Markers:
Click on a series marker on the chart - *Allow excel to select all of the markers in the series
Right-click on the marker
Click "Format Data Series"
Click the paint can image and select "Line" in the options below the paint can.
Below the paint can, click on "Marker"

Under "Marker Options" click the "Built-in" radio button

Change the "Type" to a square marker

**Tip: You may also adjust the size of the marker and in the "Fill" section below "Marker Options" you can change the marker fill as well as adjust the border in the "Border" section - Play around with it, see what you like!

Repeat above steps to make each series' marker unique (but the same across each series).

You chart should look similar to this:

Add Data Labels to the final step, or top data series, in the process to show total time:
Click the data series at the top of the chart (Mine is the green triangle)
Right-click the series
Click "Add Data Labels" > "Add Data Labels"
Click on a data label to select all of the labels
Right-click on the data label
Click "Format Data Labels..."
In the format pane on the right, select the bar graph icon
Under "Label Position" select the "Above" radio button.
The labels are now "above" the markers.

**Tip: You can format data labels by clicking on them and using the ribbon at the top "Home" > "Font"

Last, Lets jazz up the chart a bit and make it a bit more presentation ready:

Chart Title:
[Insert name of the process here]
Source: [name of data source]
Last Updated: [Date/Time data was last updated]

**All of this goes into the single title text box - Just add a carriage return to add a new line.  Once you've added the text, highlight the bottom two lines and change the font size to something like "8" to make it smaller.  It's important information, but we don't want it to distract from the chart itself.

Adding Axis Labels - Be sure to label your axes appropriately!
Click on chart
Go up to "Chart Tools" > Design
Click "Add Chart Element" on the Ribbon > Axis Titles > Primary Horizontal/Primary Vertical
Change the Vertical Axis' text to "Time (Minutes)" or something similar.

**I am not including a horizontal axis label since the data and legend are self explanatory

The Final Chart will look something like this:

As you can see, this can be a very useful chart that allows us to identify where opportunities exist within a time driven process.  It is very simple visually, and very easy to identify steps that are taking longer than others.  If you have used something similar or have another spin on this, please share in the comments below: how it was used and what industry you're working in.

VBA: Select Case

Alright VBA users... Have you ever needed to to use and if/then statement with multiple criteria and need to run a lot of code after the criteria is met?  Well, Select Case is the code for you!

It looks like this:

Select Case [What you are evaluating]
   Case is = [potential value]
       'your code goes here
   Case is = [potential value]
       'your code goes here
   Case is = [potential value]
       'your code goes here
   Case Else
       'your code goes here
End Select

Let's put it to use.  In this example, I am going to evaluate the value within cell B1.  In my code, I personally prefer using the "Cells()" reference method over the "Range()" reference method.  Cells(row, col) is what you'll see.

Select Case Sheets("Sheet1").cells(1, 2).value - This opens the "Select Case" Statement and shows that we're looking at the value of cell B1 or row1, column2.

Next, if the cell value is 1, 2, or 3 I want run specific code depending on the value.
Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]

So what if the cell value isn't 1-3?  Well, in that "case" (Pun!) you will use the "Case Else" Statement.

Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]
   Case Else
      [insert code here]

Finally, when you are done adding cases and code, you need to close the Select Case Statement with "End Select"

Select Case Sheets("Sheet1").cells(1, 2).value
   Case is = 1
      [insert code here]
   Case is = 2
      [insert code here]
   Case is = 3   
      [insert code here]
   Case Else
      [insert code here]
End Select

**Tips and tricks:
#1 - if you are going to be using VBA, pay attention to the formatting of the code.  Think of your code as an onion (multiple layers, not that they stink or make you cry).  Your opening statement is the outer layer (no indentation).  As you move into the statement - for example from the "Select Case ..." to "Case is =..." you will press tab and indent to indicate that what is happening is happening within the Select Case Statement. The code you include if a statement is True would be indented twice.  The "End Select" statement is referring to the "outer layer" so it is not indented to complete visual.

My code above in Blue is an example of this.

#2 - If you do not intend for anything to happen when a condition is met using the Select Case statement, simply do not place any code in the [insert code here] section and the program will resume by ending the select statement and continuing to the next line.

Locked out of my file!

Have you ever password protected an excel file (Sheet Protection, Workbook Protection) only to forget the password when someone needs you to make a change 2 years later?  Well, here's how to open it back up.

Open the file with Open Office Calc.  Yep, that's it! Simple as that. You will be able to un-protect what you need to and get on with your day.

Nice and easy, right?  On the flip side, it hopefully helps you realize just how vulnerable Excel data can be, so be careful.