This article provides step by step instructions on how to use the Bar 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 or fields 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).
Currency
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.
When choosing an Average calculation, the following analysis types are disabled.
Pareto
Stacked Bar
100% Stacked Bar
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 bar 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
Choose from 7 additional analysis types.
Min/Max
Compare against Threshold/Target
Custom highlight
Pareto Analysis
Difference from Mean
Stacked Bar
100% Stacked Bar
