2014 Indian Lok Sabha Elections Results in Excel

Posted on

It is election season now, with results of 2019 Lok Sabha Elections in India to be announced in a few days on 23rd May 2019.

I wanted to develop a simple template in Excel that can allow us to view 2009 and 2014 Election results, and gain insights through simple data visualization techniques.

Download this free Excel template to view the results of 2009 and 2014 Lok Sabha Elections. You may also find data visualization techniques that you can apply to other topics or datasets. If you have any suggestions to make it better, please post them in Comments section below.

2014 Lok Sabha Election Results in Excel
2014 Lok Sabha Election Results in Excel

Features of Template

  • Easily view 2009 and 2014 Lok Sabha results and compare 2014 vs 2009
  • Visualize the data in 3 different aspects – Parliament View, State View and Constituency View
    • Parliament View – 543 seats visualization by State, Party, Alliance & Win Margin %
    • State View – Filter by state to see top parties and their vote share 
    • Constituency view – choose one constituency and see the top 5 candidates

Free Download

Video Demo

Video Demo – 2014 Lok Sabha Election results in Excelhttps://youtu.be/9hQtZ44gvKE

Parliament View

In the Parliament view, each cell represents a constituency and there are 543 constituencies. You can use 4 options available to you, to create a visual that specifically addresses a question.

Lok Sabha Elections - 543 Seats
Lok Sabha Elections – 543 Seats

First, let me explain the four options to control to visual.

1. DISPLAY: This determines what value is displayed on each cell.

Display Filter options
Display Filter options

The four options within DISPLAY are

  1. STATE – State to which the constituency belongs
  2. PARTY – (Abbreviated) Name of Party that won that constituency in the election.
  3. ALLIANCE – Alliance (UPA or NDA or Others) that won that constituency in the election
  4. WIN MARGIN % – The Winning Margin % (difference between winner and runner up as a % of all votes polled in that constituency) in that constituency

2. COLOR: This determines what color is applied to each cell.

Color Filter options
Color Filter options

The four options are

  • STATE – State to which the constituency belongs. Each state and union territory is given a different color
States and Union Territories - By Color
States and Union Territories – By Color
  • PARTY – (Abbreviated) Name of Party that won that constituency in the election. To simplify the visual, INC (Indian National Congress) and BJP (Bharatiya Janatha Party) are shown separate colors and all others are combined to one color.
Party Colors INC, BJP and Others
Party Colors INC, BJP and Others
  • ALLIANCE – Alliance (UPA or NDA or Others) that won that constituency in the election. Each alliance is given a different color.

Alliance Colors NDA, UPA and Others
  • WIN MARGIN % – The Winning Margin % (difference between winner and runner up as a % of all votes polled in that constituency) in that constituency. This is grouped into 5 categories. 1) 0 to 1% 2) >=1% and <5% 3) >=5% and <10% 4) >=10% and <20% 5) >=20%
Win Margin % in 5 categories
Win Margin % in 5 categories

3. SORT: This determines how the 543 constituencies are sorted and thus which constituency represents which cell. This helps in organizing or grouping constituencies together by State or Party or Alliance.

Sort Filter options
Sort Filter options

The three options are

  1. STATE – State to which the constituency belongs
  2. PARTY – (Abbreviated) Name of Party that won that constituency in the election. To simplify the visual, all parties except INC (Indian National Congress) and BJP (Bharatiya Janatha Party) are combined as one unit.
  3. ALLIANCE – Alliance (UPA or NDA or Others) that won that constituency in the election

4. YEAR: As the template has data for 2009 and 2014 elections, you can choose Year to view results of that specific year’s elections.

Year Filter options
Year Filter options

Using the above 4 choices, you can come up with many combinations to create different visuals. I will share a few common ones that I find interesting below. When you download the template, please feel free to change the filters to create your own visuals.

Lok Sabha Representation by Alliance

 2009 Lok Sabha by Alliance
2009 Lok Sabha by Alliance

UPA had only 262 seats in 2009, not enough to win majority (272) needed to form government. But it had outside support from some of the ‘Others’ to get it cross over the majority mark.

2014 Lok Sabha by Alliance
2014 Lok Sabha by Alliance

NDA had 336 seats in 2014 to clearly win majority (272) needed to form government.

Lok Sabha Representation by Party

2009 Lok Sabha by Party
2009 Lok Sabha by Party

INC by itself did not have enough seats (206) in 2009 to win majority (272) needed to form government. But it had outside support from some of the ‘Others’ to get it cross over the majority mark.

2014 Lok Sabha by Party
2014 Lok Sabha by Party

BJP by itself had enough seats (282) in 2014 to win majority (272) needed to form government.

Lok Sabha Representation by State/Union Territory

This view shows how many seats each State/Union Territory contributes to 543. UP is clearly the state that gets the most seats, while MH (48), AP (42), WB (42), BR (40), TN (39) are the next 5 states with a lot of seats.

Note: AP is split to AP and TG (Telangana) from 2019 Lok Sabha elections.

Lok Sabha by State/Union Territory
Lok Sabha by State/Union Territory
Colors of State/Union Territory
Colors of State/Union Territory

Top Parties in State/Union Territory

2009 Lok Sabha - Display State - Color by Party

2009 Lok Sabha – Display State – Color by Party

In 2009, INC was strong in AP, RJ, HR, DL and KL. It also had good representation in MP, MH, GJ and UT.

2014 Lok Sabha - Display State - Color by Party
2014 Lok Sabha – Display State – Color by Party

BJP almost swept UP in 2014. It also had good representation in MP, RJ, GJ, DL, JH, HR and UT.

Top Parties in State/Union Territory

This is related to the previous set of views. But here, we are displaying Party name and hence can see other parties that won.

2009 Lok Sabha - Display Party Name - Color by State
2009 Lok Sabha – Display Party Name – Color by State
2014 Lok Sabha Elections - Display Party - Color by State
2014 Lok Sabha Elections – Display Party – Color by State

BJP almost swept Uttar Pradesh, Madhya Pradesh, Gujarat and Rajasthan. Among other parties, AITC in West Bengal and ADMK in Tamil Nadu had big wins as well.

Win Margin % by Alliance

The next two views show the Win Margin % by each alliance in 2009 (first image) and 2014 (second image).

2009 Win Margin % by Alliance
2009 Win Margin % by Alliance
2014 Win Margin % by Alliance
2014 Win Margin % by Alliance

You can see that the 2014 NDA has a lot more green (Dark Green is >=20% win margin) than the UPA in 2009. This indicates that NDA in 2014 had a clearer mandate from the people with a much bigger win margin in each constituency it won.

State View

State view is much simpler to explain. You can view the top parties and their performance either for all states or for chosen states. You can use the filter on the left to choose states.

State View of 2009 and 2014 Lok Sabha Elections
State View of 2009 and 2014 Lok Sabha Elections

The view shows 2009 results, 2014 results and compares 2014 vs 2009. Let’s consider 2009 results first.

 State View of 2009 Lok Sabha Elections
State View of 2009 Lok Sabha Elections

You can see Parties sorted by Seats Won and when there is a tie, they are sorted by Votes Polled.

For example, in 2009, INC had 206 seats won, that is 47% of 440 seats it contested. It received 11,91,11,019 votes that is 29% of all votes polled in 2009.

Calculations

  • Seats Win % = Seats Won by Party / Seats Contested
  • Vote Share % = Votes Received by Party / Total Votes Polled in all Seats
State View of 2014 Lok Sabha Elections
State View of 2014 Lok Sabha Elections

You can see the same metrics for 2014. In addition, you can see the change in 2014 compared to 2009.

For example, BJP won 282 seats out of 428 it contested (at 66%). It had a vote share of 31% of all votes in entire country. It had 166 more seats in 2014 compared to 2009. It increased its votes by 9,32,22,168 and 12.2% increase in Vote share% (31% in 2014 compared to 19% in 2009).

As mentioned earlier, you can view this information for one or multiple states. Let me show one example of that. If I filter on Tamil Nadu as State, this is what I will get.

Tamil Nadu - 2009 Lok Sabha Elections
Tamil Nadu – 2009 Lok Sabha Elections

DMK had 18 seats, ADMK 9 and INC 8 in 2009.

Tamil Nadu - 2014 Lok Sabha Elections
Tamil Nadu – 2014 Lok Sabha Elections

In 2014, It was almost a sweep where ADMK had 37 seats. BJP and PMK won 1 each. ADMK increased vote share by 21.4% gaining mostly from INC and DMDK.

NOTA (None of the Above) option was introduced in 2014 and made it to top 10 parties’ list.

Constituency View

In this view, you can choose a specific constituency and view the top 5 candidates in 2009 and in 2014.

First, choose the State which will narrow down the Constituency drop down.

Select State to narrow the values in Constituency drop down
Select State to narrow the values in Constituency drop down

Then, you can view the details of that specific constituency. An example is shown below with call-outs of information presented.

Example of Constituency view result
Example of Constituency view result
  • You can see the votes polled, number of contestants (including NOTA in 2014) and winning margin as votes and %.
  • If the win is a close win (<1%), it will be highlighted.
  • In 2014 section, you can see the same metrics but also the increase (%) in votes polled.
  • The winning parties’ symbol is shown if available.
  • Message at top right indicating if the seat was retained by same party in 2014 or it switched from one party in 2009 to another in 2014.

Let me share screenshots of some constituencies.

The most famous constituency from 2014 – Varanasi in UP.

2014 Varanasi - Lok Sabha Election Result
2014 Varanasi – Lok Sabha Election Result

An example seat that switched from one party to another in 2014.

2014 Chennai North - Lok Sabha Election Result
2014 Chennai North – Lok Sabha Election Result

One of the closest wins with 36 votes making the difference – in Ladakh in 2014.

2014 Ladakh - Lok Sabha Election Result
2014 Ladakh – Lok Sabha Election Result

One of the most comfortable wins – Surat in 2014 – Winning Margin was 5.3 lakh votes (56.2%).

About Elections Data

Elections data could be considered as an ideal dataset for visualizations and analysis in Excel, as they are usually smaller in size. However, what makes it challenging, especially Indian elections data, is the quality of data available.

New states are created, constituencies are re-organized and renamed, new parties are formed, new alliances are created every time, and so on. They make it hard to expand the scope of analysis to more than 1 or 2 election years. Candidate names and Party Names are not entered in a consistent way. That makes historical analysis by candidate and party almost impossible, unless you spend significant time in improving data quality.

Source

I have tried my best to source data from official Indian election commission website and where needed, I leveraged Wikipedia. If you find any inaccuracies in the template, please post in comments below and and I will be glad to correct. My goal is to make such public data available in usable formats to all.

Elections and Me

Since my school days (a long time ago 😊), I have always been fascinated with elections. I never followed any political news for 5 years (which is the term of a government in India), but still sat in front of TV on the counting day to watch the results live. I used to view it as a race or a live sporting event where results come in every minute and the winner can change. The excitement lasted only for a few hours as the winner will be usually clear after that. I never bothered to follow politics after that day until the next counting day came up 5 years later.

As I grew up, my views on politics and elections have changed for the better (at least that’s what I think). I do follow everyday politics day and understand the nuances slightly better now. Though I get disgusted at the current state of political activities, it’s hard for me to stay away from it.

If you find this template useful, please share with your friends. Please provide your feedback in the comments section below. I look forward to hearing from you.

Related templates

  1. 2015 Cricket World Cup Template
  2. India Geographical Heat Map
  3. 2016 US Presidential Heat Map

World Heat Map by Continent – Free Excel Template for Data Visualisation

Posted on
World Heat Map by Continent - Color by Data Ranges - Only Names

World Heat Map by Continents Excel Template is a free Excel template that you can use to create heat maps of the world instantly. You will learn how you can enter your own data in the template to create heat maps. You will also learn the features and options available within the template to customize and extend.

World Heat Map by Continent - Gradient Color Scheme - Names
World Heat Map by Continent – Gradient Color Scheme – Names

 

What is a Heat Map?

A heat map (Wikipedia – What is a Heat Map?) is a form of data visualization, where the data is presented in the form of varying levels of colors. We use colors to understand the data easily and quickly.

Here we will be creating a geographical heat map where the area we will be coloring is a geographic division (by Continent) of the World. Each continent can have a different color based on the data value for that specific district. For example, we can visualize the population distribution by Continent, or any numeric data we have at the Continent level.

There are other types of use cases too, which we will discuss later.

Our Excel Template allows you to enter your own data and visualize that instantly.

 

FEATURES

  • Enter your own data by Continent and visualize in a heat map instantly
  • Turn Continent names on or off
  • Turn data values on or off
  • Choose default gradient color scale
  • Option to choose custom colors by data ranges (7 data ranges and 10 colors)
  • Supports various types of data
  • Enter title and notes to go on the map
  • Readily printable or export to PDF
  • Copy image and paste in any application such as PowerPoint
  • Data can be entered in any order
  • Open for editing and customizing for your needs

 

ABOUT THE DATA

7 Continents. We have used https://en.wikipedia.org/wiki/List_of_continents_by_population  as source.

Asia, Africa, Europe, North America, South America, Australia and Antarctica

Disclaimer: We have done our best to create the map outlines as accurately as we can. However, doing it in Excel has its limitations. The map is just for illustrations and is not expected to be 100% accurate with respect to the exact size and outlines of Continents and World.

 

VIDEO DEMO

 

FREE DOWNLOAD

Download World Heat Map by Continents – Excel Template

 

How to use the template?

The template is very simple to use with just three steps.

  1. Enter Continent level data in the data table
Excel Template - Enter Continent level data in Data Table
Excel Template – Enter Continent level data in Data Table

 

  • You can type in just the data column and replace the data that is in the template by default.
  • If you have data already in a digital (from some website or spreadsheet) form, then you can copy and paste the district names and data values. Please make sure that you are pasting as values.

 

Right click and Paste Values
Right click and Paste Values

 

2. Edit the Settings to customize the heat map

Settings - Choose Name and Data Value display
Settings – Choose Name and Data Value display

 

  1. Choose to display the Continent name (YES or NO)
  2. Choose whether to display the data value (YES or NO)
  3. Choose which color scheme to use

There are two options here.

Choose color scheme for heat map - Gradient color or by Data ranges
Choose color scheme for heat map – Gradient color or by Data ranges

 

  • Gradient Colour scheme will apply the 3-color scale with lowest value being Red and mid-point being Gold and highest value being Green.
Gradient color scale in Excel for Heat Map
Gradient color scale in Excel for Heat Map

 

  • Colour by Range scheme will allow you to enter data ranges and choose color for each range.
Enter Colour by Data Ranges
Enter Colour by Data Ranges

 

In the table above, we are setting the Continents with data values from 1 to 29,999,999 to be Green. From 30,000,000 to 399,99,999 to be Brown and so on. Any Continent with value from 4,000,000,000 will be colored in Pink.

You can customize the ranges as well as the colors. You can create up to 7 ranges and choose from 10 color options.

In your case, let us assume that you want to create a data range from 1 to 1000 as Red and 1001 to 2000 as Blue and anything from 2001 as Orange. The following is the data we should enter in the table.

Customizing data ranges
Customizing data ranges

 

To enter the data ranges, just type over the default numbers. Also, if you don’t need all the seven ranges, then just click on the cell and press Delete key to clear contents. Do not delete the row.

You can change colors by selecting from the drop down. The 10 color choices available are Red, Green, Blue, Yellow, Pink, Violet, Brown, Gold, Orange & Gray.

Change colors from the drop down from 10 options
Change colors from the drop down from 10 options

 

Any Continent with value less than the first value will use the gradient color scheme by default. In our examples here, we started with 1. So, any negative value will use the gradient color scheme. But you are not limited to only positive values.  Here is how you can also use negative values in the data ranges.

Customizing data ranges - Negative values
Customizing data ranges – Negative values

 

Values from -100 to 1000 will be Green and 1001 to 2000 will be Blue.

You can also enter Title and Comments that will be displayed on the Map. You can choose to leave them as blank if you want to not have Title and comments.

Enter Title and Comments for Map display
Enter Title and Comments for Map display

 

3. View/Print/Export Screenshots of Heat Maps

Let’s look at the screenshots of heat maps with different options in the settings we discussed in Step 2.

In the first set of examples, we will be using the Gradient color scheme option.

 

1. If we choose only the Continent names to be displayed

World Heat Map by Continent - Gradient Color Scheme - Names
World Heat Map by Continent – Gradient Color Scheme – Names

 

2. If we choose both Continent name and data value to be displayed

World Heat Map by Continent - Gradient Color Names and Data Values
World Heat Map by Continent – Gradient Color Names and Data Values

 

Now with the Colour by Data Ranges option,

3. If we choose only the Continent name to be displayed

World Heat Map by Continent - Color by Data Ranges - Only Names
World Heat Map by Continent – Color by Data Ranges – Only Names

 

4. Now when we choose both names and data values

World Heat Map by Continent - Color by Data Ranges - Names and Data Values
World Heat Map by Continent – Color by Data Ranges – Names and Data Values

 

Now that we know how the heat map will look, let’s talk about printing.

 

Printing

In Excel, when you try to print, the print area in the file is already pre-set to print the map and the settings section.

Print Preview of World Heat Map
Print Preview of World Heat Map

 

If you would like to print only the map image, then please select the area around the map image and then click on Set Print Area from the Page Layout ribbon.

Heat Map Image as Print Area
Heat Map Image as Print Area

 

Now, when you print, you will print only the map image.

 

Export the heat map image as PDF

Similarly, you can save as PDF.

Export Heat Map as PDF
Export Heat Map as PDF

 

What is in the print area will now be saved as a PDF.

 

Copy-Paste Image

Instead of printing or exporting, you can also copy the image and paste in other applications directly as needed. You can copy the image by right clicking on the map and choosing ‘Copy’.

Copy Map as Image
Copy Map as Image

 

You can then paste in other applications such as PowerPoint and Word.

Paste options in PowerPoint
Paste options in PowerPoint

 


Recommended

 


If you find the template useful, please share with your friends. If you have any feedback or suggestions, please post in comments section below.

More uses of Geographic Heat Map Excel Templates

Posted on

We have already published some Geographic Excel heat map templates on indzara.com. The primary purpose of the templates is to visualize data by geographical region. For example, we have country templates (India, United States, Canada, Australia) which allow visualizing data by state in that country. We have state templates (Tamil Nadu) which allow visualizing data by district in that state. There are also several options and settings that the user can customize to create the heat maps.

In this blog post, we will see some of the uses of our heat map templates for purposes other than visualizing state/district level data.

Specifically, we will learn the following

  1. How to make the entire map single color and change color
  2. How to group some regions together and color them
  3. How to play ‘Find the District’ or ‘Find the State’ game

We will use the Tamil Nadu heat map to illustrate. The concept is applicable to all heat map templates available on indzara.com

 

VIDEO DEMO

 

1. Make entire map single color

This is quite straight forward. We want to show the entire map in a single color and to be able to change the color.

Enter 1 as data value for all the districts.

Single Color Map - Enter data - Enter 1 for all districts
Single Color Map – Enter data – Enter 1 for all districts

 

Choose Colour by Range for Colour Scheme

Single Color Map - Settings - Color by Range
Single Color Map – Settings – Color by Range

 

In the data ranges table, enter only one range as shown. This means that all the districts with values from 1 will be shown in red color. Now, the entire map will be Red.

To change the color, change the red to another color from the drop down.

Single Color Map - Settings - Choose Color
Single Color Map – Settings – Choose Color

If I choose BLUE from the drop down, the map image changes to blue as shown below.

Single Color Map - Blue color across all districts
Single Color Map – Blue color across all districts

 

2. How to group districts together into regions?

Sometimes, we would want to group certain districts together in same color.

Enter data such that the districts in a group will have same value. For example, Northern districts are given 1, Central districts as 2, Eastern districts as 3, Western districts as 4 and Southern districts as 5.

Regions by Color - Enter data
Regions by Color – Enter data

 

In the Settings, choose Colour by Range scheme and then enter data ranges as shown below.

Regions by Color - Settings - Color by Range
Regions by Color – Settings – Color by Range

This will apply colors such that districts with 1 will be Blue color, 2 will be Green, 3 will be Yellow, 4 will be Pink and 5 will be Brown.
And the map will appear as below.

Regions by Color - Tamil Nadu Regions
Regions by Color – Tamil Nadu Regions

If we choose not to display District Names, then the map will appear as below.

Regions by Color - Tamil Nadu Regions - No names
Regions by Color – Tamil Nadu Regions – No names

You can, of course, change colors as needed.

 

3. Find a district

First, we should make the entire map in one color. Enter 0 as data for all districts.

Find a District - Enter data for all districts as Zero
Find a District – Enter data for all districts as Zero

 

Let’s remove the district names from display. Then, enter the data ranges as shown below.

Find a District - Settings - Colors
Find a District – Settings – Colors

 

Give the name of a district to your friend and ask to guess where that district is. For example, let’s say Tiruchirappalli district.

Find a District - Where is Tiruchirappalli in Tamil Nadu
Find a District – Where is Tiruchirappalli in Tamil Nadu

 

Then, you can reveal the correct location by typing 1 as data for that one district.

Find a District - Enter data for one district as One
Find a District – Enter data for one district as One

 

The map will display the district of Tiruchirappalli in Gold color as that is the color we chose for data values from 1.

Find a District - Tamil Nadu
Find a District – Tamil Nadu

You can even play this by yourself. It will be a good learning tool.

You can also play the game in reverse if you are playing with a friend. You can first show one district location on map and then ask friend to guess the name.

It is a good tool to use in schools teaching the location of a district in the state.

If you have used the heat map templates in other interesting ways, please share in the comments section below.

 

Recommended

Tamil Nadu Heat Map by District – Free Excel Template for Data Visualisation

Posted on
Tamil Nadu Population by District Heat Map - Excel Template - Gradient

In this blog post, we will discuss the Tamil Nadu State Heat Map by District Excel template. This is a free Excel template that you can use to create heat maps instantly. You will learn how you can enter your own data in the template to create heat maps. You will also learn the features and options available within the template to customize and extend.

 

What is a Heat Map?

A heat map (Wikipedia – What is a Heat Map?) is a form of data visualization, where the data is presented in the form of varying levels of colors. We use colors to understand the data easily and quickly.

Here we will be creating a geographical heat map where the area we will be coloring is a geographic division (by district) of the state of Tamil Nadu. Each district can have a different color based on the data value for that specific district. For example, we can visualize the population distribution by district, literacy rate by district, or any numeric data we have at the district level. There are other types of use cases too, which we will discuss later.
Our Excel Template allows you to enter your own data and visualize that instantly.

Tamil Nadu Population by District Heat Map Excel Template - Gradient Color Scheme
Tamil Nadu Population by District Heat Map Excel Template – Gradient Color Scheme

 

FEATURES

  • Enter your own data by district and visualize in a heat map instantly
  • Turn district names on or off
  • Turn data values on or off
  • Choose default gradient color scale
  • Option to choose custom colors by data ranges (7 data ranges and 10 colors)
  • Supports various types of data
  • Enter title and notes to go on the map
  • Readily printable or export to PDF
  • Copy image and paste in any application such as PowerPoint
  • Data can be entered in any order
  • Open for editing and customizing for your needs

 

ABOUT THE DATA

There are currently 32 districts in Tamil Nadu (as of March 2018). The names in English are sometimes different depending on which source you choose. We have used Wikipedia – List of Districts as source for the names of districts in English.

Ariyalur, Chennai, Coimbatore, Cuddalore, Dharmapuri, Dindigul, Erode, Kancheepuram, Kanniyakumari, Karur, Krishnagiri, Madurai, Nagapattinam, Namakkal, Perambalur, Pudukkottai, Ramanathapuram, Salem, Sivaganga, Thanjavur, The Nilgiris, Theni, Tiruvallur, Tiruvarur, Thoothukudi, Tiruchirappalli, Tirunelveli, Tiruppur, Tiruvannamalai, Vellore, Viluppuram, Virudhunagar

 

Disclaimer: We have done our best to create the map outlines as accurately as we can. However, doing it in Excel has its limitations. The map is just for illustrations and is not expected to be 100% accurate with respect to the exact size and outlines of district and state.

VIDEO DEMO

 

 

FREE TEMPLATE DOWNLOAD

Tamil Nadu Heat Map by District v1.0

 

 

How to use the template?

The template is very simple to use with just three steps.

1. Enter district level data in the data table

Enter Tamil Nadu District level Data in Table
Enter Tamil Nadu District level Data in Table

 

  • You can type in just the data column and replace the data that is in the template by default.
  • If you have data already in a digital (from some website or spreadsheet) form, then you can copy and paste the district names and data values. Please make sure that you are pasting as values.
Right click and Paste Values
Right click and Paste Values

 

2. Edit the Settings to customize the heat map

  • Choose to display the district name (YES or NO)
  • Choose whether to display the data value (YES or NO)
Settings - Choose District Name and Data Value to display on map
Settings – Choose District Name and Data Value to display on map

 

  • Choose which color scheme to use.
    • There are two options here.
Choose color scheme for heat map - Gradient color or by Data ranges
Choose color scheme for heat map – Gradient color or by Data ranges

 

Gradient Colour scheme will apply the 3-color scale with lowest value being Red and mid-point being Gold and highest value being Green.

Gradient color scale in Excel for Heat Map
Gradient color scale in Excel for Heat Map

Colour by Range scheme will allow you to enter data ranges and choose color for each range.

Tamil Nadu Heat Map - Color by range in Excel
Tamil Nadu Heat Map – Color by range in Excel

 

In the table above, we are setting the districts with data values from 1 to less than 10,00,000 to be Red. From 10,00,000 to less than 15,00,000 to be Brown and so on. Any district with value from 40,00,000 will be colored in Gold.

You can customize the ranges as well as the colors. You can create up to 7 ranges and choose from 10 color options.

In your case, let us assume that you want to create a data range from 1 to 1000 as Red and 1001 to 2000 as Blue and anything from 2001 as Orange. The following is the data we should enter in the table.

Customizing data ranges
Customizing data ranges

 

To enter the data ranges, just type over the default numbers. Also, if you don’t need all the seven ranges, then just click on the cell and press Delete key to clear contents. Do not delete the row.

You can change colors by selecting from the drop down. The 10 color choices available are Red, Green, Blue, Yellow, Pink, Violet, Brown, Gold, Orange & Gray. (In a separate post, I will explain how to change or add colors).

Change colors from the drop down from 10 options
Change colors from the drop down from 10 options

 

Any district with value less than the first value will use the gradient color scheme by default. In our examples here, we started with 1. So, any negative value will use the gradient color scheme. But you are not limited to only positive values.

Here is how you can also use negative values in the data ranges.

Customizing data ranges - Negative values
Customizing data ranges – Negative values

 

Values from -100 to 1000 will be Green and 1001 to 2000 will be Blue.

You can also enter Title and Comments that will be displayed on the Map. You can choose to leave them as blank if you want to not have Title and comments.

Enter Title and Comments
Enter Title and Comments

 

3. View/Print/Export Screenshots of Heat Maps

Let’s look at the screenshots of heat maps with different options in the settings we discussed in Step 2.

Screenshots of Heat Maps with different settings

In the first set of examples, we will be using the Gradient color scheme option.

1. If we choose only the district name to be displayed

Tamil Nadu Population by District Heat Map - Excel Template - Gradient
Tamil Nadu Population by District Heat Map – Excel Template – Gradient

 

2. If we choose only the data value to be displayed

Tamil Nadu Heat Map -Excel Template -Gradient with data values
Tamil Nadu Heat Map -Excel Template -Gradient with data values

 

3. If we choose both district name and data value to be displayed

TamilNadu District Heat Map - Excel Template - Gradient - Names and Data Values
Tamil Nadu District Heat Map – Excel Template – Gradient – Names and Data Values

 

The map will look busy with this option, especially since we have large numbers. However, you can convert the numbers to be shorter in length and that would make it more readable. You can also change the font size, which we will cover later in a separate post.

Now with the ‘Colour by Data Ranges’ option,

 

4. If we choose only the district name to be displayed

Tamil Nadu Population by District -Heat Map - Excel Template - Color Range
Tamil Nadu Population by District -Heat Map – Excel Template – Color Range

 

5. If we choose only the data values to be displayed

Tamil Nadu Heat Map - Excel Template - Color by Data Ranges with data values
Tamil Nadu Heat Map – Excel Template – Color by Data Ranges with data values

 

6. Now when we choose both names and data values

Tamil Nadu District Heat Map - Excel Template - Color by Data Ranges - Names and Data Values
Tamil Nadu District Heat Map – Excel Template – Color by Data Ranges – Names and Data Values

 

Now that we know how the heat map will look, let’s talk about printing..

 

PRINTING

In Excel, when you try to print, the print area in the file is already pre-set to print the map and the settings section.

Print Preview
Print Preview

 

If you would like to print only the map image, then please select the area around the map image and then click on Set Print Area from the Page Layout ribbon.

Heat Map Image as Print Area
Heat Map Image as Print Area

 

Now, when you print, you will print only the map image.

 

Export the heat map image as PDF

Similarly, you can save as PDF.

Export Heat Map as PDF
Export Heat Map as PDF

What is in the print area will now be saved as a PDF.

 

Copy-Paste Image

Instead of printing or exporting, you can also copy the image and paste in other applications directly as needed. You can copy the image by right clicking on the map and choosing ‘Copy’.

Copy Heat Map image
Copy Heat Map image

You can then paste in other applications such as PowerPoint and Word.

Paste options in PowerPoint
Paste options in PowerPoint

 

In addition to creating heat maps, this template can be used for other purposes too. In the next article, we will discuss some of those special use cases of this template.

 

If you find the template useful, please share this post with your friends. If you have any feedback or suggestions, please post in comments section below.

2016 U.S. Presidential Election Tracker – Excel Template

Posted on
2016 U.S. Presidential Elections - Map with Electoral votes total

It’s election day in the U.S. and almost everyone is interested in knowing the results. Surveys say that more than 80% of U.S. voters are not pleased with the campaigns so far. My opinion about politics continues to go lower and lower with every election cycle (India & U.S.). No, this post is not about my political view. 🙂 It is about Excel, of course.

A couple of years ago, I wanted to build a tracker during 2014 elections in India, but couldn’t complete it in time. U.S. election system is relatively easier on many aspects. So, here we go. A simple Excel template where you can enter the winner of each state and view the results on map. The electoral votes get calculated for each candidate. You can download and edit to apply techniques to other elections or competitions.

It should be noted that I have simplified the framework by only considering 2 candidates/parties (though there are others on ballot).

 

2016 US Presidential Election - Results on Map - Sample Data
2016 US Presidential Election – Results on Map – Sample Data

 

 

FREE DOWNLOAD

2016 U.S. Election Tracker

 

VIDEO DEMO

 

HOW TO USE THE U.S. ELECTION TRACKER TEMPLATE

All the states (and D.C.) are listed on the left side.

2016 US Presidential Election - Enter Winner of Each State
2016 US Presidential Election – Enter Winner of Each State

 

The electoral votes for each state are also listed.

Please choose the winner of each state. Select Clinton, Trump or NoResult.

 

On the right side, you can see the results update.

2016 U.S. Presidential Elections - Excel Template
2016 U.S. Presidential Elections – Excel Template

 

At the top is the Race to White House.

2016 Elections - Race to White House
2016 Elections – Race to White House

 

The bars represent the electoral votes won by each candidate and they increase as the candidates win states. Red color is for Republican party (Trump) and the Blue for the Democratic party (Clinton). The Green line is the magic 270 mark which is needed to become President.

Then, we have the map view.

2016 US Presidential Election - Results on Map - Sample Data
2016 US Presidential Election – Results on Map – Sample Data

 

The Initial D (Democrat) or R (Republican) represents the party that won the state.

The candidates’ total electoral votes are also shown.

2016 U.S. Presidential Elections - Map with Electoral votes total
2016 U.S. Presidential Elections – Map with Electoral votes total

 

When the candidate’s total touches 270, we can see the winner. I don’t have the right to vote and am not picking any sides here. So, let’s see winning scenarios for both parties. 🙂

A completely random scenario where Clinton wins.

 

2016 U.S. Presidential Elections - Winning Scenario for Clinton
2016 U.S. Presidential Elections – Winning Scenario for Clinton

 

Here is a random scenario for Trump’s win.

2016 U.S. Presidential Elections - Winning Scenario for Trump
2016 U.S. Presidential Elections – Winning Scenario for Trump

 

At the bottom we have the state results in Alphabetical order.

Election results by State in Alphabetical Order
Election results by State in Alphabetical Order

 

The width for each state is proportional to the electoral votes.

 

If you would like to edit the formulas, please unprotect the sheet using indzara as password.

 

Please share this with your friends if you like the templates available on this site.


RELEVANT EXCEL TEMPLATES

Sales Report Excel Template – Top 10 & Bottom 10 Products by Sales

Posted on
Top 10 Products by Change in Sales Amount

This Sales Report Excel template is designed to help identify the best and worst performing products easily. Enter your sales data and instantly identify the top selling products. Compare Week over Week, Month over Month and Year over Year.

If you are a business owner, you can find which products are selling the most (Top 10 products) so that you can continue to have more of them in stock and promote them. By knowing which products are in the ‘Bottom 10’, you can either stop purchasing more of them or find innovative ways to promote them to your customers.

If you are a data analyst in a company, it is quite likely that you have had the need to analyze product sales from this perspective. Just download this template, paste your data and see the top 10 and bottom products by sales instantly.

Sales Report - Top 10 & Bottom 10 Products - Excel Template
Sales Report – Top 10 & Bottom 10 Products – Excel Template

 

FEATURES OF SALES REPORT

  • Automated report with a lot of customization options
  • Easy to use – Just 2 quick steps to enter data
  • Customize date parameters (Month, Week, Day, Custom) of the report
  • Compare a period’s sales versus previous period or previous year or custom dates
  • Choose to sort by Sales, Change in Sales Amount or % Change in Sales
  • Control the thresholds for alerts

 

DOWNLOAD SALES REPORT

Sales Report – Top 10 & Bottom 10 Products – Excel Template

 

VIDEO DEMO

 

HOW TO ENTER DATA IN SALES REPORT TEMPLATE

With all the templates published on indzara.com, the goal is to have a simple and effective solution to a problem. Keeping up with the same theme, this template is very simple to use. Just 2 steps.

STEP 1: Enter a list of products in HOME sheet. (If you are new to Excel tables, please see article on how to enter data in Excel tables).

Enter List of Products for Sales Report
Enter List of Products for Sales Report

 

STEP 2: Enter Sales data in SALES_DATA sheet.

Enter Product Sales Data for Sales Report - Date, Product ans Sales Quantity or Amount
Enter Product Sales Data for Sales Report – Date, Product ans Sales Quantity or Amount

 

There are only three columns of data. DATE of sale, PRODUCT sold and Quantity or Amount of SALES. You can just paste this data (in the same order of columns).

Tip: Generally, it is recommended to paste special as values.

Click on cell A2. Right Click and choose the Values shortcut.

How to Paste data - Paste Special as Values
How to Paste data – Paste Special as Values

 

That’s it. We are ready to see the report. How easy is that? 🙂

 

The report is a single page but it is packed with options to customize.
On the left of the sheet, we have user control options. There are three components (Date parameters, Sort Metric and Thresholds).

Input Options for Sales Report - Date parameters, Sort Metric and Thresholds
Input Options for Sales Report – Date parameters, Sort Metric and Thresholds

 

CHOOSE DATE PARAMETERS

First is the ‘Date Parameters’. Using the date parameters, we can inform the template for the date range for which we want to see the sales report for. We can choose to view from a Monthly/Weekly/Daily perspective or choose Custom to enter custom dates.

Sales Report - Date Parameters - Options
Sales Report – Date Parameters – Options

 

Let’s look at each of the options.

Day

When we choose Day, we see the following options for date parameters.

Sales Report - Data Parameters with Day Option
Sales Report – Data Parameters with Day Option

 

We can choose Today or Yesterday or Day Before Yesterday. The template will then calculate the sales for that specific day. It can automatically determine today’s date from our computers and use in calculations.

We can also set comparison periods. In this template, we can not only calculate sales for a specific date range, we can also compare with another period easily. For example, we can compare today’s sales with ‘Previous Day’ or ‘Same Day Last week’. You may have seen something similar in Google Analytics.

It is better to compare this Monday’s sales with Last Monday’s, instead of Sunday (as sales patterns for most businesses vary over the weekend).

 

Week

When we choose Week, we have the options for ‘Last 7 days’, ‘This Week’ or ‘Last week’. We can set comparison period as ‘Previous Week’ or ‘4 Weeks ago’.

Sales Report - Date Parameters with Week Option
Sales Report – Date Parameters with Week Option

 

Month

Month option works similar to Week. If we choose Month, we have the options for ‘Last 30 days’, ‘This Month’ or ‘Last Month’. We can set comparison period as ‘Previous Week’ or ‘4 Weeks ago’.

Sales Report - Date Parameters with Month Option
Sales Report – Date Parameters with Month Option

 

Custom

We also have the option of ‘Custom’ where we can enter any date range for current period and any date range for Comparison period.

Sales Report - Date Parameters with Custom Option
Sales Report – Date Parameters with Custom Option

 

We can use this to do Year over Year, or Quarter over Quarter comparison.

 

CHOOSE METRIC

After we set the ‘Date Parameters’, we can decide which sales metric we want to use on the report. The three options are ‘Sales Amount’, ‘Change in Sales Amount’ and ‘% Change in Sales Amount’.

Sort Sales Report by Sales Amount, Change in Sales Amount or % Change in Sales
Sort Sales Report by Sales Amount, Change in Sales Amount or % Change in Sales

 

METRIC 1: SALES AMOUNT

When we choose Sales Amount, the report would appear like this.

Sales Report - Top 10 and Bottom 10 Products by Sales Amount
Sales Report – Top 10 and Bottom 10 Products by Sales Amount

 

The header section has the total sales for the current period and comparison period.
The Top 10 Products table will list the top 10 products by Sales in the Current period. Comparison period does not matter in this setting. Similarly, the bottom 10 products table will list the products with the least sales amount.

 

METRIC 2: CHANGE IN SALES AMOUNT

If we choose metric as ‘Change in Sales Amount’, the report will show the products which had the top 10 sales increases from the comparison period to the current period, and the top 10 sales declines.
We can also set thresholds for change.

Sort by Change in Sales Amount - Set thresholds
Sort by Change in Sales Amount – Set thresholds

 

In the image above, we have set thresholds of 250 and -150. Let’s see how it works.
In the report, the Top 10 Products table shows the products which had the top 10 sales increases from the comparison period to the current period.

Top 10 Products by Change in Sales Amount
Top 10 Products by Change in Sales Amount

 

The top 10 products with increases in sales by 250 or greater display a green upward arrow.
The Bottom 10 Products table shows the products which had the lowest sales increases (or highest declines) from the comparison period to the current period.

Bottom 10 Products by Change in Sales Amount
Bottom 10 Products by Change in Sales Amount

 

Products with decline in sales amount of 150 or greater will have a red downward arrow.

 

METRIC 3: % CHANGE IN SALES AMOUNT

Similarly, we can also use a metric of ‘% Change in Sales Amount’.

Sort by % Change in Sales Amount - Set thresholds
Sort by % Change in Sales Amount – Set thresholds

 

Now, when we set thresholds, we have to enter % values and we do that by entering decimal values. For example, we can enter 20% as 0.2 and -15% as -0.15.

 

In the report, the Top 10 Products table shows the products which had the top 10 sales increases (as %) from the comparison period to the current period.

Top 10 products by % Change in Sales Amount
Top 10 products by % Change in Sales Amount

 

The products with increases in sales by 35% or greater display a green upward arrow.
The Bottom 10 Products table shows the 10 products which had the lowest sales increases (as %) from the comparison period to the current period.

Bottom 10 Products by % Change in Sales Amount
Bottom 10 Products by % Change in Sales Amount

 

The products with decline in sales by 15% or greater display a red downward arrow.

 

HOW TO EXTEND THE SALES REPORT TEMPLATE

How to expand to more than 100 products

There is no limit on size of the sales data set. The larger the data set, when we change the settings, Excel might think for a few seconds before refreshing the report.

 

However, the template is set up to handle 100 products in order to keep the file small and fast. If you have more than 100 products, you can follow the steps below.

 

Right Click on any sheet name and choose Unhide.

Right Click on a sheet name and choose Unhide
Right Click on a sheet name and choose Unhide

 

Choose H_PRODUCTS sheet to unhide.

Choose H_PRODUCTS sheet to unhide
Choose H_PRODUCTS sheet to unhide

 

Now in the H_PRODUCTS sheet, go to cell E102.

Expanding the table of calculations to handle more than 100 products
Expanding the table of calculations to handle more than 100 products

 

  • First, click outside the table. For example, click in cell F105.
  • Then, click the little arrow in cell E102 and drag down to expand the table to more products (as many as you need).
  • The report will now reflect more than 100 products. Easy.

 

CHANGING TODAY

The template calculates TODAY’s date using a formula and then uses that date in calculations such as ‘Today’, ‘This Week’, ‘Last 7 Days’, ‘Last Week’, ‘Last 30 Days’, ‘This Month’ and ‘Last Month’.

Change Report Date from Today to Any date
Change Report Date from Today to Any date

 

If there is a need to create a report that is a snapshot of the past (how the report would have looked if we had exported on any date in the past), we can easily do that by replacing the formula with a specific date.

 

PRINT OR EXPORT

You can print the report sheet or export to PDF using Excel’s standard settings.

 

CHANGE CONDITIONAL FORMATTING

If you prefer to change the arrow styles or colors, it is easy to do.
First, click on Conditional Formatting in the Home ribbon and select ‘Manage Rules’.

Choose Conditional Formatting - Manage Rules
Choose Conditional Formatting – Manage Rules

 

Then, select ‘This Worksheet’ from the drop down menu so that we can see all the rules applied.

Select 'This Worksheet' in the Conditional Formatting drop down menu
Select ‘This Worksheet’ in the Conditional Formatting drop down menu

 

Now, scroll down and see the last rule where we have applied the arrows/icons.

Select the specific rule and click on Edit Rule
Select the specific rule and click on Edit Rule

 

When we click on the rule and then click on Edit rule, we see the following window.

Conditional Formatting rule used for arrows
Conditional Formatting rule used for arrows

 

We can change the icons by clicking on the arrow next to the icon.

Change the icon as preferred
Change the icon as preferred

 

Once you choose the icon you prefer, just click on OK in the dialog boxes that appear.  This is how you can modify the icons.


RELATED EXCEL TEMPLATES IN DATA VISUALIZATION

Social Media Dashboard – Free Excel Template to report social media metrics

Posted on
Social Media Dashboard Excel Template - Metrics - Sample for August 2016

Social Media Dashboard (Excel Template) is designed to consolidate metrics across multiple Social networks such as Facebook, Twitter and YouTube and present in a single-page dashboard. If you are trying to create a monthly view of business impact of content posted across all the social networks by your business, then download this free template to create an instant monthly dashboard.

This template does not automate the data collection process. Once we are able to compile the monthly aggregated data from the different social networks, we can use this template to instantly build the dashboard as shown below.

Social Media Dashboard Excel Template - Metrics - Sample for August 2016
Social Media Dashboard Excel Template – Metrics – Sample for August 2016

 

FEATURES OF SOCIAL MEDIA DASHBOARD

The Social Media Dashboard template has the following features

  • Add up to 8 social media channels. Choose only ones relevant to your business.
  • Designed for Facebook, Twitter, Google+, YouTube, Blog, LinkedIn, Pinterest and Instagram
  • 7 social media metrics (Audience, Activity, Applause, Amplification, Conversation, Conversion and Revenue)
  • Choose to display metrics as either absolute volumes or rate per activity
  • Add each month’s data and store all data in one file
  • Automatically calculates Month over Month (MOM) change %
  • Control alerts by setting thresholds on each metric
  • Alerts highlight only metrics which exceed thresholds (with red or green arrows)
  • Dashboard displays large numbers with M (millions) or K (thousands) for better readability

 

FREE DOWNLOAD

Social Media Dashboard – Excel Template

 

VIDEO DEMO

 

INSTRUCTIONS (HOW TO USE SOCIAL MEDIA DASHBOARD)

The template is very easy to use.

Step 1: Choose the social networks to include in dashboard

Choose Social Media Networks to include in Dashboard
Choose Social Media Networks to include in Dashboard

 

Check the boxes next to social networks you would like to display in the dashboard.

 

Step 2: Set Thresholds for change in metrics

Before we talk about these thresholds, let’s discuss the metrics the dashboard presents.

  1. Audience: Number of Fans/Followers/Page Likes (depending on the network)
  2. Activity Number of Posts/Updates
  3. Applause: Number of Likes; Applause Rate: Average number of Likes per Activity
  4. Amplification: Number of Shares; Amplification Rate: Average number of Shares per Activity
  5. Conversations: Number of Comments; Conversation Rate: Average number of Comments per Activity
  6. Conversions: Number of Conversions; Conversion Rate: Average number of Conversions per Activity
  7. Revenue: Total Revenue Generated; Revenue per Activity: Revenue generated per Activity

Recommended read on Social Media Metrics: Best Social Media Metrics – by Avinash Kaushik

Some of these metrics (example: Revenue) may be harder to capture. You may have to get them from your web analytics tool. Each social network (examples: Facebook Insights, Twitter Analytics) allows different levels of access to its analytics data. As mentioned earlier, this template does not address the data collection process. If there is enough interest, please add your thoughts to the comments section below and I will do some research on the data collection process as well.

Set Threshold for Social Media Metrics change Month over Month
Set Threshold for Social Media Metrics change Month over Month

 

Why thresholds?

When we display the Month over Month change for each metric, the dashboard can become very distracting with a lot of numbers.

The purpose of the dashboard is to allow us to focus only on metrics that require our attention and action.

So we use thresholds to only flag metrics that exceed our thresholds. Once a certain metric’s month over month (MOM) change exceeds the threshold in positive direction, Green arrows appear on dashboard. If the change exceeds the threshold in negative direction, Red arrows appear on the dashboard.

Here is an example:

Dashboard highlights only metrics that need attention (only those that exceed thresholds we set)
Dashboard highlights only metrics that need attention (only those that exceed thresholds we set)

 

The metrics that are within the thresholds are grayed out so that they do not distract us from the action-required metrics.

Recommended Reads: Tim Wilson’s custom number formatting tip to show arrows and Allen Wyatt’s custom number formatting for Millions and Thousands

 

Step 3: Enter monthly data for social media channels in DATA sheet

In the DATA sheet, we can enter each month’s data for each social media network.

For example, Facebook data can be entered like this.

Enter Monthly data for each social media network (Example: Facebook)
Enter Monthly data for each social media network (Example: Facebook)

 

Data entry for Twitter data:

Enter Monthly data for each social media network (Example: Twitter)
Enter Monthly data for each social media network (Example: Twitter)

 

Data for all the 8 social networks are stored in the same table. You can add a new row for each month. (How to enter data in Excel Tables?)

This setup allows you to continue to add any number of months’ data in one place. Thus, you can store a long history of the metrics, all in one file.

 

Step 4: View DASHBOARD sheet

Now that we have entered our data, we can view the Dashboard in the DASHBOARD sheet.
Change Month drop down to view stats for that month

Choose Month from drop down to update Dashboard
Choose Month from drop down to update Dashboard

 

Change Metric type to show absolute volumes or rate per activity

Choose Metric type as either Absolute Volumes or Rate per activity
Choose Metric type as either Absolute Volumes or Rate per activity

 

For example, if there are 200 conversations from 10 Facebook posts in Mar 2016, 200 is the absolute volume of Conversations and 200/10 = 20 is the Conversation Rate. 

Social Media Dashboard - Monthly - Excel Template - Animated Demo
Social Media Dashboard – Monthly – Excel Template – Animated Demo

 

Print or share DASHBOARD sheet as PDF, if needed

The Dashboard sheet is set up as print-friendly. Using the in-built Excel features, we can either print the sheet or export to PDF and share with our colleagues or clients.

 

If you find the template useful, please share with your friends. If you have any suggestions to improve the template, I would love to hear from you. Please post your thoughts in the comments. Thank you.


More Excel Dashboards & Templates

2016 Olympics Dashboard – Explore Rio Olympics data in Excel

Posted on
2016 Olympics - Excel Dashboard - Olympic View

The purpose of this 2016 Olympics Dashboard is to create an interactive tool to access and explore results of the recently concluded Rio Olympics (link to Wikipedia).   This was the first Olympics that I watched with my son and so it’s very special to me. 🙂

 

Another important objective is to openly share the data on the results with the users in a consumable format so that others can use the data for various analyses. I will share more about my challenges in data collection later in this post.

2016 Olympics - Excel Dashboard - Olympic View
2016 Olympics – Excel Dashboard – Olympic View

 

Viewpoint

As with any such dataset which spans a variety of data elements, the possibilities are endless. (My attempt to capture Cricket World Cup 2015 in Excel Dashboard) I decided to approach the Olympics data set in the following dimensions.

  1. Olympic View
    • Which countries won the most medals overall in 2016 Olympics?
    • Who are the top athletes and how many medals did they win?
    • Which sports contributed to most medals?
  2. Country View
    • In which sports did a country win the most medals?
    • Who are the top athletes in a country and how many medals did they win?
    • Which sports were dominated by the country?
  3. Sports View
    • Which countries won the most medals in a Sport?
    • Who are the top athletes in a Sport and how many medals did they win?
    • Which countries are most dependent on the Sport for their medals?
  4. Event Results
    1. Who won the medals in a specific event in Rio Olympics?

 

Having access to such rich data, while still keeping the tool simple and compact was a challenge. I ended up with a one-page compact dashboard which allows the user to switch between 3 views (Olympic View, Country View and Sports View).

 

Key Features of 2016 Olympics Dashboard

  • Dynamic One-Page dashboard that adapts to user selections
  • All the medal counts are calculated with formulas based on raw data
  • Flags of countries and logos of Sports
  • Scroll bars to let user see more data
  • Ranking Method Choice: Choose whether Total medal count or Gold medal count should be considered for ranking the countries.
  • Calculations on Sports Domination and Sports Dependence of countries.
  • Accounts for the unusual scenario such as 2 Gold medals, 3 Silver Medals & 2 Bronze medals
  • Raw Data can be viewed and used to create analyses and dashboards

 

FREE DOWNLOAD

2016 Olympics – Excel Dashboard

 

Now, let’s see how the dashboard works. If you prefer the video, please watch this video demo. If you prefer text and screenshots, please continue reading.

VIDEO DEMO

 

At the top of the Dashboard, we choose one of the three views. Let’s start with Olympic View.

Olympic View

2016 Olympics Dashboard - Olympics View Summary
2016 Olympics Dashboard – Olympics View Summary

 

There were 306 events that awarded 307 Gold, 307 Silver and 360 Bronze medals – totaling to 974 medals.

Top Countries with Overall Medal Count in Rio Olympics
Top Countries with Overall Medal Count in Rio Olympics

 

In total, 87 countries won at least one medal and the top countries are displayed along with their rank, flag, number of Gold, Silver and Bronze medals. You can scroll using the scroll bar to see the next set of countries. The Ranking method used is Total, where Total medal count is given the most importance. So, China is ranked #2 based on its higher medal count of 70 compare to 67 from Great Britain.  (Reference: 2016 Olympics Medal Tally  in Wikipedia)

We have an option to choose the Ranking Method of Gold to rank countries based on Gold-Silver-Bronze medal count and ignoring Total medal count.

Ranking Method for ranking Countries and Athletes
Ranking Method for ranking Countries and Athletes

 

Top Countries with Medal tally - Ranking based on Gold medal counts
Top Countries with Medal tally – Ranking based on Gold medal counts

 

Now, China moves to 3rd, as it has only 26 Gold medals compared to Great Britain’s 27.

Let’s get back to the Total ranking method and view the next table.

Olympic View - Top Athletes - Ranking Method based on Total medal count
Olympic View – Top Athletes – Ranking Method based on Total medal count

 

When we look from the athletes’ perspective, there were 1859 athletes who received at least one medal and totally 2026 medals were given to all athletes. You can use the scroll bar to view more athletes. (Reference: Medal Count by Athletes)

We have to remember here that this athlete level medal count will not match the country level medal count. For example, a Swimming 4 X 100 Relay team that won the Gold medal will count as 1 medal for its Country, but each of the 4 swimmers of that team will individually receive a Gold Medal – counting as 4 medals in total.

 

Usain Bolt doesn’t appear in the top 5 athletes based on the total medal count. If we switch the ranking method to Gold, then, he comes at #5 as he has 3 Gold medals. (4 athletes including Usain) have 3 Gold medals.

Olympic View - Top Athletes - Ranking Method based on Gold medal count
Olympic View – Top Athletes – Ranking Method based on Gold medal count

 

The chart below shows which sports contribute to most medals.

% of Total Medals by Sport
% of Total Medals by Sport

 

Athletics and Swimming add up to about quarter of all medals.

 

Country View

Let’s switch to Country View.

Choose Country from drop down list to refresh dashboard
Choose Country from drop down list to refresh dashboard

 

I have chosen United States, as the country with most medals.

Country View - United States - Total Medal Count, Gold, Silver and Bronze
Country View – United States – Total Medal Count, Gold, Silver and Bronze

 

The top section updates to show the number of events where United States has won at least one medal, along with Gold, Silver, Bronze and Total medal counts. Flag appears as well.

Country View - United States - Medal Count, Gold, Silver and Bronze for each Sport
Country View – United States – Medal Count, Gold, Silver and Bronze for each Sport

 

This table above helps us understand which Sports United States got its medals from.

Country View - United States - Top Athletes - Medal Count, Gold, Silver and Bronze
Country View – United States – Top Athletes – Medal Count, Gold, Silver and Bronze

 

212 Athletes received at least one medal and we can see the top 5 medal winners including Michael Phelps. The medals won by the athlete are shown using the circles of corresponding color (Gold/Silver/Bronze).

The next chart is very interesting to me. I was curious to know which sports were dominated by a country.

Country View - Sports Dominated by United States in Medal Count
Country View – Sports Dominated by United States in Medal Count

 

We see that of all medals in Swimming, 34% is won by United States. 23% of Athletics medals is won by United States.

 

Sport View

We can switch to Sport View and then choose a Sport from the drop down list.

Sport View - Choose Sport from Drop down
Sport View – Choose Sport from Drop down

 

I have chosen Athletics here.

Sports View - Athletics - Dashboard Summary
Sports View – Athletics – Dashboard Summary

 

Sports View - Top Countries with Medal Tally in Athletics Sport - 2016 Olympics
Sports View – Top Countries with Medal Tally in Athletics Sport – 2016 Olympics

 

Sports View - Top Athletes with Medal Tally in Athletics Sport - 2016 Olympics
Sports View – Top Athletes with Medal Tally in Athletics Sport – 2016 Olympics

 

Another angle in which we can view is the dependency of a country on a specific Sport.

Sports View - Countries that depend on Atheltics for their Medal Tally
Sports View – Countries that depend on Atheltics for their Medal Tally

 

For Athletics, we see a lot of countries receiving all their medals (granted, there could be just one medal and 1 out of 1 is 100%) from Athletics alone. Let’s take Swimming as an example.

Sports View - Countries that depend on Swimming for their Medal Tally
Sports View – Countries that depend on Swimming for their Medal Tally

 

Singapore received 100% of its medals from Swimming (Singapore’s Gold medal), while USA received 27% of its medals from Swimming.

 

Event Results

In the bottom right of the Dashboard, we can view any event’s results. The drop down list is dependent on which Sport you have selected at the top of the dashboard. In this example below, we have selected Badminton as Sport.

Choose Event for Results - Badminton- Women Singles - Normal Results - Drop Down
Choose Event for Results – Badminton- Women Singles – Normal Results – Drop Down

 

We can view the Gold, Silver and Bronze winners – Flag, Country and Athlete name. The above results are in the usually expected format. Sometimes we see ties and here are some examples.

3 Silver medal winners and no Bronze winners in Men’s 100m Butterfly Swimming

3 Silver medal winners and no Bronze winners in Men’s 100m Butterfly Swimming
3 Silver medal winners and no Bronze winners in Men’s 100m Butterfly Swimming

 

2 Bronze winners in Women’s 100 m Backstroke in Swimming

Event Results - Swimming - Women 100m Backstroke- 2 Bronze Medals
Event Results – Swimming – Women 100m Backstroke- 2 Bronze Medals

 

2 Gold medal winners and no Silver medal in Women’s 100 m Freestyle in Swimming

Event Results - Swimming - Women 100m Freestyle - 2 Gold Medals
Event Results – Swimming – Women 100m Freestyle – 2 Gold Medals

 

Data Collection

The collection of data for this Dashboard was a huge challenge. When I started looking at 2016 Olympics’ results, I found cleaner data on events’ results in the official Rio Olympics site. There are some minor issues with events where there are 2 Bronze winners (Example: Judo, Taekwondo), but that was manageable.

After compiling the event results, I needed the data on Athlete medals. For example, a Swimming 4 X 100 Relay event will list United States as the Gold medal winner, instead of the swimmers’ names. But I need each of the swimmers’ names so that I can count their individual Gold Medals. This count is important as Michael Phelps needs to be assigned that medal. Otherwise, his medal count will be incorrect.

Unfortunately, the official Rio Olympics site does not have this data in an easily exportable format. I went back to Wikipedia (for 2016 Olympics) to see if they have better data structure. They did, but I couldn’t use it. It wouldn’t be so much fun if everything was so easy. 🙂

Wikipedia lists Athlete names differently (First name first) compared to Olympics’ site (Last name first). Without going further in detail on my challenges with data collection, it should be obvious already that the data collection was very painful. I had to finally collect the data by few different ways (copy/paste from web page, PDF, etc.) – 99% from the Rio site and rest from Wikipedia.

 

I want to make this data easily accessible for anyone looking to use it. You can unhide the hidden sheets 1) 2016_Events_Results – which has the results of 306 events (one row for each event) and 2) 2016_Athletes_Medals – all the 2026 medals received by athletes.

 

Please share

I would like to place 2 requests to you.

  • I have spent a lot of time to ensure that the data is accurate. However, if you notice any bugs, please post in the comments and I will fix it as soon as I can.
  • Please share the link to this post with your friends. If you know of any places to post such data so that anyone who is interested in using this data can download, please let me know.

Related Templates

How to edit colors by data range in U.S. State Heat Map Excel Template?

Posted on
U.S. State Heat Map - Updated Map

In the previous article, we learnt how to change the gradient colors used in the U.S. Geographic State Heat Map Excel Template. In this article, we will edit the colors used in the ‘Colors by Data Range’ option in the same template.

Please visit product page to understand the features of the U.S. Geographic State Heat Map Excel Template.

 

The template allows control over the color used for a range of data values. In order to enable that option, choose YES in the drop down menu for ‘USE DATA RANGES?”.

U.S. State Heat Map - Choose YES for Data Ranges
U.S. State Heat Map – Choose YES for Data Ranges

 

The map will update now to reflect the data range table. For example, states with data value from 0 to less than 1001 will be colored Green, while states from 1001 to less than 2501 will be colored blue.

U.S. State Heat Map - Updated Map
U.S. State Heat Map – Updated Map

 

You can have up to 6 data ranges (in the image above, we have used 5 ranges). To fill those 6 ranges, the template provides seven colors to choose from: Green, Blue, Gray, Orange, Yellow, Red and White.

U.S. State Heat Map - 7 Colors
U.S. State Heat Map – 7 Colors

 

It is natural for each of us to have different preferences when it comes to colors. If you would prefer to change one of the available colors, it is easy to do. Let’s see how to do that step by step. In this tutorial, we will replace Blue color with Purple.

 

VIDEO DEMO

 

  1. Select cells H8 to H13. These are six cells where we choose the colors for the 6 data ranges.
    U.S. State Heat Map - Select 6 cells
    U.S. State Heat Map – Select 6 cells

     

  2. Click on ‘Data’ ribbon and then choose Data Validation –> Data Validation..
    U.S. State Heat Map - Data Validation Menu
    U.S. State Heat Map – Data Validation Menu

     

  3. Let’s edit the ‘Source’ field and replace BLUE with PURPLE (or any other color name you would prefer). Then, click OK. This step makes PURPLE one of the colors in the drop down.
    U.S. State Heat Map - Replace color in Data Validation
    U.S. State Heat Map – Replace color in Data Validation

     

  4. From the ‘Home’ ribbon, choose Conditional Formatting menu and choose ‘Manage Rules’
    U.S. State Heat Map - Conditional Formatting menu
    U.S. State Heat Map – Conditional Formatting menu

     

  5. Choose Sheet Map
    U.S. State Heat Map - Choose Sheet Map
    U.S. State Heat Map – Choose Sheet Map

     

  6. Click on the rule that has ‘Blue’ Color, as we are trying to replace Blue with Purple.
    U.S. State Heat Map - Select Conditional Formatting Rule
    U.S. State Heat Map – Select Conditional Formatting Rule

     

  7. Edit the formula and replace ‘BLUE’ with ‘PURPLE’. After that, click on the ‘Format’ button.
    U.S. State Heat Map - Change Formula
    U.S. State Heat Map – Change Formula

     

  8. Choose the Purple color in the Fill section.  Then click OK and OK in the next 2 windows that pop up.
    U.S. State Heat Map - Choose Purple Fill color
    U.S. State Heat Map – Choose Purple Fill color

     

  9. Now we have implemented the new Purple color. In order to see this in action, choose PURPLE for one of the ranges.
    U.S. State Heat Map - Use the new color
    U.S. State Heat Map – Use the new color

     

  10. The map will appear like this now, with all the states with data value from 1001 to less than 2501 colored in Purple.

    U.S. State Heat Map - Updated Map with Purple
    U.S. State Heat Map – Updated Map with Purple

 

I am sure for many of you, Purple may not be your color of choice. 🙂 Please follow the steps above and use any color of your choice to visualize data on the map as you prefer. If you have any questions or suggestions, please leave them in the comments. I look forward to hearing from you.

How to change Gradient colors in U.S. State Heat Map Excel Template?

Posted on
U.S. State Heat Map with 2 Color Scale
In this article, we will learn how to change the gradient colors used in the U.S. Geographic State Heat Map Excel Template. Using the template, you can create a geographic heat map at the state level for 50 states of U.S using your own data.

HOW TO CHANGE GRADIENT COLORS

The template comes with the default red to green gradient colors. If you like the gradient style of colors, but would like to change the actual gradient colours used, please follow the steps outlined below.

VIDEO DEMO

1. Open the Conditional formatting menu in Home ribbon and choose ‘Manage Rules’.

U.S. State Heat Map - Conditional Formatting Menu
U.S. State Heat Map – Conditional Formatting Menu

2. Choose the sheet: Map

U.S. State Heat Map - Conditional Formatting Choose Sheet Map
U.S. State Heat Map – Conditional Formatting Choose Sheet Map

 

3. Select the Gradient Color Scale rule and then click Edit Rule.

U.S. State Heat Map - Conditional Formatting Choose Graded Color Scale Rule
U.S. State Heat Map – Conditional Formatting Choose Graded Color Scale Rule

 

4. Change the colors (for minimum, midpoint and maximum) as needed.

U.S. State Heat Map - Conditional Formatting - Change Colors
U.S. State Heat Map – Conditional Formatting – Change Colors

 

If you are familiar with Excel, you can use other conditional formatting rules and settings too. For example, you can also choose the format style be 2-Color Scale instead of the 3-Color Scale.

U.S. State Heat Map - 2 Color Scale
U.S. State Heat Map – 2 Color Scale

 

That will change the map visualization to appear like this.

U.S. State Heat Map with 2 Color Scale
U.S. State Heat Map with 2 Color Scale

 

Whenever you change colors, please update the NOTE so that the footnote that appears on the map aligns with the color rule applied.

U.S. State Heat Map - Note
U.S. State Heat Map – Note

 

If you have any questions, please leave them in the comments below.