This article provides step by step instructions on how to use the Column Chart Excel template.
The template uses a simple 3 step process.
Paste your data
When you open your template, you will see that the data sheet does not have any data. It will appear as below.
There are 25 columns available.
You can type the data in. Most likely you will be pasting the data from another spreadsheet.
Copy your data (including headers) and then right click on cell A2 and paste.
While pasting, please ensure that you paste as values. This is extremely important as this makes sure that only the values are copied over, and not any formulas and formatting you may have in your source spreadsheet.
Also, the reason for pasting in cell A2 is to ensure that the row 2 has the column headers (field names) for your data.
Configure
Next step is to inform the template which of the columns you pasted should be analyzed and how.
In the configuration sheet, choose a type for each column.
- Dimension: These are the categorical fields or variables. Some examples are typically Product, Sales Rep, Channel, Region, Department.
- Measure: these are the quantitative variables we would like to aggregate. Examples are Sales, Order Quantity, # of employees, Salary Amounts.
- Date: these are the date fields you would like to use for filtering the data to a certain date range.
- Exclude: these are fields you would like to not include for any analysis. By default all the fields are considered in this type.
After choosing the field types, it will appear as below.
In this example, I have 20 columns in my dataset (even though the template supports 25 fields).
Custom calculations
The template provides two custom calculations.
‘Count of rows’: This is just a count of number of rows in the dataset you provided in the Data sheet. You can rename this. In the above screenshot, I have named it # of orders, since my data is a set of orders.
Average calculation is customizable. You can choose any numerator and denominator and the template will now calculate that for us. You can also rename that.
Currency
For currency, you can type in any symbol. The default is $.
In the Insert ribbon, choose Symbol on the far right.
Now, choose any symbol of your choice.
Insights
In the Insights sheet, you can now go and choose the fields (dimension, measure and Date) for populating the Column Chart.
You can also control the formatting of the labels. If you choose to display the value, you can choose the formatting.
Multiple Currency and Number options are available.
If you choose to display the label with a Percentage calculation, you can control the number of decimals.
Analysis types
Choose from 7 additional analysis types.
Min/Max
Automatically highlight the minimum and maximum values.
Compare against Threshold/Target
You can customize the threshold amount.
Custom highlight
Use this to highlight a specific category.
Pareto Analysis
You can customize the Pareto %.
Difference from Mean
Stacked Column
When choosing 'Stacked Bar' as the Analysis type, please choose the Second Dimension from the drop down list.
100% Stacked Column
When choosing '100% Stacked Bar' as the Analysis type, please choose the Second Dimension from the drop down list.
