Loading...

Follow Excel Champs Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

There are a lot of people out there who don’t like to use a PIE chart and they have their valid reasons for this.

And if you are one of those people, then let me introduce you with the WAFFLE CHART.

You can also call it a SQUARE PIE CHART. And today in this post, we will learn to create a WAFFLE CHART in Excel.

What is an Excel WAFFLE CHART?

In Excel, a Waffle chart is a set of grids (squares of equal area) which represents the entire chart. It works on a percentage basis where one square represents one percent of the whole.

Below is an example of a waffle chart which I have created in Excel. As I said it has a total of 100 square and each square represents one percent of the total value.

Components of a WAFFLE Chart

And, here are the main components of a WAFFLE Chart which you need to understand before you create it in Excel.

  • 100 Cells Grid: You need a square grid of 100 cells (10 X 10).
  • Data Point: A data point from where we can take the completion percentage or achievement percentage.
  • Data Label: A data label to show the percentage of completion or percentage achievement.

So now it’s time to learn how to create it in Excel. Well, you can use a static or a Dynamic (as per your need).

Let's get started.

Steps to Create a Static Waffle Chart in Excel

WAFFLE chart is not in the list of Excel’s default charts, in fact, one of the ADVANCED charts which you create of your own.

Below are the steps you need to follow to create a Waffle Chart in Excel:

  • First of all, you need a grid of 100 cells (10 X 10) and the height and the width of each cell should be the same. The overall grid of cells should be in square shape and that’s the reason it’s called the square pie chart.
  • After that, you need to enter values from 1% to 100% in cells starting from the first cell of the last row in the grid. You can use the following formula to insert the percentage from 1% to 100% in the grid (all you need to do is enter this formula in the first cell of the last row and after that copy that formula to the entire grid).
    =(COLUMNS($A10:A$10)+10*(ROWS($A10:A$10)-1))/100
  • Next, you need a cell for the data point in which you can capture the percentage of completion or achievement. You need to link this cell in waffle chart further.
  • Once you create a data point, next you need to apply the conditional formatting rule on this grid and for this, please follow these simple steps to apply a rule.
    • Select the entire grid and go to → Home Tab → Styles →Conditional Formatting → New Rule.
    • In new rule window, select “Format only cells that contain”.
    • Now in “format only cells with” specify the values 0 as a minimum and $M$4 as maximum values.
    • This rule will apply the conditional formatting to the cells which have the value between this value range.
    • Click on format button to specify a format to apply.
    • Make sure to apply the same color for font and cell color to hide fonts when conditional will apply.
  • From here, you need to apply a final formatting touch and for this select the grid and do the following:
    • Change font color to white.
    • Apply a white border to cells in the grid.
    • Apply a solid outer border to grid with black color.
  • After doing this you will get a waffle chart which is linked to a cell and when you change data in that cell the chart will get automatically updated.
  • Now, you need to create a label for the chart and for this you need to insert a simple text box and connect it to the cell. Follow the below steps for this.
    • Insert a text box in your worksheet from Go to → Insert Tab → Text → TextBox.
    • Now, select the text box and click inside the formula bar.
    • Enter cell reference of data point cell and press enter.
    • Increase the size of the font and place the text box on your chart.

Now your WAFFLE chart is ready and you can use it anywhere, but, I want to add something more and I’m sure you’ll like it.

In the below chart, apart from the main label, we have a small label at the last square of the grid which can help the viewer to instantly identify the chart’s value.

What do you think? Say “Yes” in the comment section if you like this small label.

To add this label we need to follow below steps:

  • First of all, select the entire chart grid and go to the Highlight Cell Rules ➜ Equals.
  • Now in the equals to the dialog box, in “Format cells that EQUALS to” select the cell where we have our percentage value.
  • After that, open “Custom Formatting” and go to the “Font” tab.
  • From here in the font tab, select the “White” font color and click OK.

The moment you click OK it’ll add a small label (which is actually the cell value) in the last square.

Congratulations! your first Excel waffle chart is ready to rock and make sure to download this sample file from here to try it yourself.

Steps to Create an INTERACTIVE Waffle Chart in Excel

At this point, you know how to create a WAFFLE chart but there’s a lot of questions I got about making it an interactive one.

If you think like this, one of the most important thing you should have in an interactive chart is how you control it and you should able to change data.

So in this section of the post, I’d like to share with you steps to create an interactive WAFFLE chart in which you can change data with OPTION Buttons.

Download this sample file where we have a STATIC WAFFLE CHART + achievement data for 5 products and we need to use create a WAFFLE chart where we can present all these in an interactively.

So let’s make it INTERACTIVE.

  • First of all, you need to insert five option buttons into the worksheet and for this go to the DEVELOPER Tab ➜ Insert Option Buttons.
  • After that, you need to connect those option buttons to a cell. So when you select a button that cell can have a number which we can use to extract data from the main table.
  • For this, select all the option buttons and right click and then select “Format Control” (You can also group all the option buttons by using the GROUP option).
  • Next, you need to name all the five option button as per the product names you have. Simply right-click and edit text (I’ve done this for you and you can download this file from here).
  • Now the next thing is to create a formula and insert it into the achievement cell so that when you select an option button it returns the value for that particular product.
  • So that formula we need here would be like below:
    =INDEX(R6:R10,P3)
  • Enter the above formulas in the achievement cell. In this formula, R6:R10 is the range where you have achievement values and P3 is the cell which is connected with the option buttons.
  • There’s one more thing which we need to do and that’s creating a dynamic label for the chart (at this point, we have a data label which is connected to the achievement cell but we need to make it dynamic).
  • For this, we need to enter the below formula in the cell next to the achievement cell.
    ="Target Achieved for "&INDEX(Q6:Q10,P3)
  • After that, insert a simple text box which you need to connect with the cell where you just added the above formula and for this select that text box and click on the formula bar and type the address of the cell where you have the formula.

Now you have an INTERACTIVE CHART in your worksheet which you can use and control with the option buttons.

And in future, if you want to update the data you just need to add a new option button and update the data ranges in the formulas.

To check out how it works you can download this sample file from here and make sure to share your views in the comment section.

Add a Waffle Chart in a Dashboard

WAFFLE chart looks good but inserting it in a dashboard can be tricky sometimes. But, you don’t have to worry about that.

The best way to add it is to create a linked image with Excel's Camera Tool or by pasting spacial. The benefit of using this technique that you can change the size of the chart.

  1. Select your waffle chart (Grid).
  2. Copy cells by using shortcuts key control + C.
  3. Go to your dashboard sheet & use shortcut key Alt-H-V-I or Go to ➜ Home Tab ➜ Clipboard ➜ Paste ➜ Linked Image.
Pros
  1. It gives a quick view of the progress of a project or about the achievement of the target.
  2. It looks good and you can easily use it in your dashboard.
  3. You can easily deliver your message to the user without any extra explanation.
Cons
  1. Using more than one data point in the waffle chart makes it complicated.
  2. You have to spend a couple of minutes to create a waffle chart.
  3. You can only present data in percentage.
In the End

There’s no doubt that a WAFFLE chart is easy to create, looks good and the user can easily understand it.

It is perfect if you need to track a KPI which is critical for your business and if you have more than one KPI then you can go with the INTERACTIVE version.

You can also use this chart to track your project completion.

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

The post How to Create Waffle Chart in Excel [Step by Step Guide] appeared first on Excel Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Last week one of my readers asked me for a simple formula which he can use to count cells with text.

Actually, he was trying to figure out which is the best method to use.

But if you think for a second... 

In Excel, you have different formulas to count all the cells where you have text values.

But the point is which one you should use.

I believe that it depends on the situation.

One thing which you need to understand here that cells with text mean all the cells where you don’t have numbers, blank cells or errors.

So today in this post, I’d like to share with 6 different ways to count cells with text in different situations.

…so let’s get started.

6 Ways to Count Cells with Text in Excel in Different Situations

As I said, all these 6 methods can be used in different situations.

  1. COUNTIF + Wildcard
  2. SUMPRODUCT + ISTEXT
  3. Count Specific Text Cells
  4. Counting number of Cells with a Partial Text
  5. Count Case Sensitive Text
  6. VBA to Count Cells with Text

…let’s explore each and every method and learn when we can use them and how they work.

1. Combo Formula using COUNTIF with Wildcard Characters to Count Cell with Text Values

The best formulas to count cells with text is COUNTIF with wildcard character (* - Asterisk).

In the below data table, you have a list of names and mobile numbers in a single column.​

And from this list, you need to count the number cells with names (text).

The formula will be:

​=COUNTIF(A2:A20,"*")

When you enter it, it will return the count of the cells with text only.

...here's how this formula works

Here we have used COUNTIF to count cells with specific criteria and for criteria we have used an asterisk.

So, when you use an asterisk it will count cells with any number of characters other than logical values, numbers (if they are not entered as text) and errors.

Important Note: If there are some cells where you just have a blank space, will be counted as text.

So for this, you can amend your formula like this.

=COUNTIFS(A2:A20,"*",A2:A20,"<> ")

2. Using SUMPRODUCT and ISTEXT to Count Number of Cells with Text

To count the number of cells with text in them you can also create a formula by combining SUMPRODUCT and ISTEXT.

And, the formula will be:

=SUMPRODUCT(--ISTEXT(A2:A20))

When you enter it, it will return the number of cells that contain text.

...here's how this formula works

To understand this formula, you need to split it into three different parts.

First, you have used ISTEXT to check if a cell contains a text or not. Here ISTEXT returns an array where for all the cells in which you have TRUE for the cells with text and FALSE for all other cells.

Second, you have added double minus sign to convert the TRUE/FALSE array into 1/0. Now, for all the cells with the text, you have 1 and 0 for all others.

Third, you have used SUMPRODUCT to sum that array. And that sum is the number of cells which have text in them.

Using SUMPRODUCT here has a benefit that you don’t need to enter your formulas as an array.

3. Formula to Count Cells with Specific Text

Now, let’s dig a deeper little bit. Suppose you need to count the cells where you have a specific text.

Here is the example.

In the below data table, you need to count the cell where you have “John”. And for this, the formula will be:

=COUNTIF(A2:A20,"John")

This formula will return the number cells where the text you have specified.

4. Cells with a Partial Text

It happens sometimes when you need to count cells by matching a partial text. And, COUNTIF and wildcard characters can help you in this.

Check out the below table where we have the product name and invoice numbers combined.

And from this, you need to count the number of cells with “Product-A”.

Use below formula:

​=COUNTIF(A2:A20,"Product-A*")

This formula will return the number cells where you have “Product-A”.

5. Case Sensitive Text

Now here is a different thing. You need to count the cells with a specific text and that should be a case sensitive count.

From below table, you need to count the cells with name “JOHN” but here you have the same name in the small case letters but count should be for capital letters.

For this, you can combine SUMPRODUCT with EXACT.

=SUMPRODUCT(--EXACT("JOHN",A1:A20))

It will return the numbers of cells where you have “JOHN”.

...here's how this formula works

To understand this formula, you need to split it into three different parts.

First, you have used EXACT to compare the entire range of cells with the text “JOHN”. It returns an array where you have TRUE for the cells where “JOHN”, ignoring “john”, “John” and all other cells.

Second, you have added double minus sign to convert that TRUE/FALSE array into 1/0. Now, for all the cells with the “JOHN”, you have 1 and 0 for all others.

Third, you have used SUMPRODUCT to sum that array. And that sum is the number of cells which have “JOHN” in them.

5. VBA to Count Cells with Text

Using a macro code is a big time saver. Below codes will help you to count the number of cells with a text in the selection and in entire worksheet.

For selection:

Sub countTextSelection() 
Dim rng As Range
Dim i As Integer
For Each rng In Selection
If Application.WorksheetFunction.IsText(rng) Then
i = i + 1
End If
Next rng
MsgBox i
End Sub

Entire Worksheet:

Sub countTextWorksheet() 
Dim rng As Range
Dim i As Integer
For Each rng In ActiveSheet.UsedRange
If Application.WorksheetFunction.IsText(rng) Then
i = i + 1
End If
Next rng
MsgBox i
End Sub
Conclusion

I am sure all these methods are sufficient for your to count cells with text in any given situation.

You can use any of the methods which you think is perfect for you.

For most of the situations, the first method is applicable without any problem.

But, if you want to go specific you can use other methods. For me, VBA is the big time saver as I don’t have to apply formulas. 

Now it’s your time to tell me something.

Do you have any other method which we can use to count the number of cells with the text?

Make sure to share your views with me in the comment section, I'd love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.

Related Tips

  1. RANKIF: Have you ever faced a situation where you have to rank values by using some specific [...]
  2. Remove First Character: To remove first or n characters from a string you can use these 5 methods and the best part is […]

About the Author

Puneet Gogia

He is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

The post How to Count Cells with Text in Excel appeared first on Excel Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Top 100 Excel Functions with Examples - A Complete List Including Samples Files

I’m sure you agree with me that "Functions can make you great in Excel".

Well, once you master Excel basics this is the next thing you need to master. But the question is:

How you can learn any of these functions in the easiest way?

For this, I have this comprehensive list for you. In this list, you can find Top 100 Excel Function with Examples.

Every single function in this list is explained in a way that you can learn it in no time.

Here’s how this function guide can help you to master some of the most useful functions.

  • First, you will learn what's the purpose of a function and its syntax.
  • Next thing is arguments and how to declare an argument.
  • And in the end, its usage through a simple example so that you can use it in real life.

For example, if you want to learn about IF function, you have a quick intro, syntax description, some additional information and then an example.

In the end, you have links to the relevant sources to learn further. And all the functions are structured in the same way.

What’s an Excel Function (Worksheet)?

In Excel, a function is a predefined formula which performs a specific calculation by using values which a user input as arguments.

Every Excel function has a specific purpose, in simple words, it calculates a specific value.

Each function has its own arguments which you need to specify to get the result value in the cell.

Components of an Excel Function

To understand an Excel function you need to understand its components:

  • Function Name
  • Parentheses
  • Arguments

Every Excel function has its name, which actually helps you to identify its purpose, then you need to enter opening parentheses after that specify it’s arguments, and in the end, enter closing parentheses.

Type of Arguments

There are two different types of arguments:

  • Required: A required argument is compulsory for a user to specify and without which a function can’t calculate its result.
  • Optional: If you skip specifying these arguments it will not stop a function to calculate its result value.

In this functions guide, I’ll be using the square bracket for every optional argument to make you understand.

Quick Note: There are some functions in Excel which don’t require any arguments, in that case, all you need to do is create type opening and closing parentheses and hit enter.

How to Insert a Function in Excel

The easiest way to insert a function in a cell in Excel is to type the name of the function you want to insert starting with a equals to sign.

So if you want to insert the SUM function:

  • First of all, you need to type = and the then type SUM.
  • After that, enter the opening parentheses.
  • Specify the arguments (refer to a cell or you can directly enter values into the function).
  • In the end, type closing parentheses and hit enter.
Sample Files
1-Click Download
A Complete List of Top 100 Excel Functions with Examples which YOU should Learn this Year to 10X YOUR SKILLS in No Time
Text Functions

If you deal with data where you have text, then below are some of the functions which you need to learn to work efficiently.

1. LEFT Function

The LEFT function can help you to get a specific number of characters from a text starting from the left side of the text (starting position of text).

You can enter the text directly into the function or refer to the cell where you have text.

Syntax

LEFT(text,num_chars)

Arguments
  • text is a text or number from which you want to extract characters.
  • [num_char] is the number of characters you want to extract.

In the LEFT function, there are two arguments. the first is where need specify the text and the second where you need to specify the number of character of specify.

The [num_char] argument is optional and if you skip specifying it, Excel take 1 as default and will return the first character from the text.

Examples

In the below example, we have extracted the first five digits from the text string using LEFT by specifying the number of characters to extract.

In the below example, we have used LEN and FIND along with the LEFT to create a formula which extracts the name from the cell.

It first counts total length of the text string and then finds the position of space between first and last name.

And in the end, it extracts the first name from the test string.

Notes
  • num_char can be zero or greater than zero.
  • If you skip specifying num_char, it will assume 1 as default.
  • You can’t refer to more than one cell. If you do that it will return the characters from the top right cell of the selected range.
  • It can work with numbers also in the same manner.
  • It can also work as an array formula.
  • With negative num_char, it will return an error.
  • You can also enter a text string directly into the formula.

sample file

2. RIGHT Function

The RIGHT function can help you to get a specific number of characters from a text starting from the right side of the text (ending position of text).

You can enter the text directly into the function or refer to the cell where you have the text.

Syntax

LEFT(text,num_chars)

Arguments

  • text is a text or number from which you want to extract characters.
  • [num_char] is a number of characters you want to extract.
Example

In the below example, we have extracted 6 characters using right function.

If you know, how many characters you need to extract from the string, you can simply extract them by using a number.

Now, if you look at the below example, where we have to extract the last name from the cell, but we are not confirmed about the number characters in the last name.

So, we are using LEN and FIND to get the name.

Let me show you, how we have done this.

First of all, we have used the LEN to get the length of that entire text string, then we used the FIND to get the position number of space between first and last name.

And in the end, we have used both the figures to get the last name.

Notes
  • You will get an #VALUE! error on using negative numbers in num_char.
  • If you skip specifying num_char, it will assume 1 as default.
  • You can’t refer to more than one cell and if you do that, it will return the characters from the top-left cell of the range.
3. MID Function

The MID function can help you to get a specific number of characters from another text using a starting from a specific position.

You enter the main text into the function directly or you can refer to a cell where you have text.

Syntax

MID(text,start_num,num_chars)

Arguments

  • text is a text or number from which you want to extract characters.
  • start_char is a number for the position of character from where you want to extract characters.
  • num_chars is a number of characters you want to extract from the start_char.
Examples

In the below example, I have used different values in function arguments for your better understanding.

  1. From 6th character to next 6 characters extracted.
  2. From 6th character to next 10 characters extracted.
  3. We have used starting character in negative and it has returned an error.
  4. By using 0 for the number of characters to extract & it has returned a blank.
  5. With negative number for the number of characters to extract & it has returned an error.
  6. Starting number is zero and it has returned an error.
  7. Text string directly into the function.
Notes
  • You can’t specify start_char less than 1 and if you do, it will return an error.
  • If you specify num_chars as 0, it will return nothing & if you specify it as a negative number it will return an error.
  • You can’t refer to more than one cell and If you do that it will return the characters from the top right cell of the range.
  • If the start_char is greater than the length of the text, then it will return and blank.
4. LOWER Function

The LOWER function can convert a normal text into a lower-case text where you have all the characters in small letters.

In this function, you can directly enter the text into the function, or you can simply refer to a cell where you have text.

Syntax

LOWER(text)

Arguments

  • text is that text which you want converts to lower case.
Examples

In the below example, I have compared lower case, upper case, proper case, and sentence case with each other.

A lower case text has all the letters in a small case compared to others.

Notes
  • Anything other than letters will remain unaffected.
  • If the referred text is an error it will return an error in the result.
  • You can also insert text string directly into the function by using double quotation marks.
5. UPPER Function

The UPPER function can convert a normal text into a upper-case text where you have all the characters in capital letters.

In this function, you can directly enter the text into the function, or you can simply refer to a cell where you have text.

Syntax

UPPER(text)

Arguments

  • text is that text which you want converts to upper case.
Examples

As I said, in an upper case text we have all the letters in capital form.

In the below example, I have used the upper function to convert name text to capital letters from the text in which characters are in different cases.

Notes
  • Characters other than alphabets remain unchanged.
  • If the referred text contains an error it returns an error in the result.
  • You can also insert text string directly into the function by using double quotation marks.
6. PROPER Function

The PROPER function can convert a normal text into a proper case text where you have first letter of a word in capital and rest all in small case.

In this function, you can directly enter the text into the function, or you can simply refer to a cell where you have text.

Syntax

PROPER(text)

Arguments

  • text is that text string which you want converts to upper case.
Examples

In the below example, I have proper case has first letter in the capital case in a word and rest of the letters are in the lower case comparing to other two cases lower case and upper case.

In the below example, I have used the proper function to streamline first name and last name into the proper case.

Notes
  • It can only convert text into the proper case, any value which is not text will not change.
  • It capitalizes the first letter the word that follows a non-text character. =Proper(“excel-champs”) = Excel-Champs.


7. REPT Function

The REPT function can repeat a specific (by you) character a number of time.

You need to specify the character and a number to repeat that character. The best use of this function is for creating IN-CELL charts.

Syntax

REPT(text, number_times)

Arguments

  • text is that text string which you want to repeat. You can either insert a text in the function directly or you can also refer to a text which contains the text.
  • number_times is the number to specify the repetition of the text.
Examples
Notes

In the below example, we have used different types of text for repetition using with REPT function.

It can repeat any type of text or numbers and even symbols which you specify in function and the main use of REPT function is for creating in-cell charts.

In the below example, we have used REPT function to create an in-cell column chart. You can use REPT Function in different ways to create some good looking tiny charts.

  • Maximum numbers of repetition you can do is 32767, and after that, it will return an error.
  • If you skip specifying the number of repetition, the function will return blank.
  • If a number of repetition is not an integer it will return an error.
8. FIND Function

The FIND function can find a string from another text string and remember that this function is a case sensitive while find the text.

If you want to non-case sensitive search then use SEARCH Function.

Syntax

FIND(find_text,within_text,[start_num])

Arguments

  • find_text is that text which you want to find from another text.
  • within_text is that text from which you want to locate the text.
  • [start_num] is the number represent the starting position of the..
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

It happens sometimes when we need to track a single point target vs. its achievement.

And for this, it’s important to have a specific chart which can help you to present your data in an understandable way.

I believe that thermometer chart is one of the best charts for a single point target. It’s easy to create (simple steps) and easy to understand for a user.

A thermometer chart looks like a thermometer where entire chart presents the target and filled part is the achievement.

The filled part goes up as the achievement increase. In Excel, there is no default option to create a thermometer chart but you can create it by using simple steps.

So today in this post, we will learn how to create a thermometer chart in Excel. And, apart for this, we will also learn to create a dynamic thermometer chart.

So let’s get started…

Steps to Create a Thermometer Chart in Excel 

Below are the steps you need to follow to create a thermometer chart in Excel:

  • First of all, make sure you have data in the below format, where the target is 100 (in percentage) and achievement is 68 percent against the target.
  • After that, select entire data and go to Insert Tab ➜ Charts ➜ 2D-Column Chart and insert this chart.
  • From here, right click on your chart and click “Select Data”.
  • Now, from the select data window, click on “Switch Row/Column” and click OK.
  • Next right click on achievement data bar and open “Format Data Series”.
  • And now, go to “Series Options” and select “Secondary Axis” to convert the achievement bar into a secondary axis chart.
  • At this point, you have two data bars (overlapping each other) with a different axis. But you need to make their axis values the same.
  • Now from here, right-click on the first axis and select “Format Axis” and add maximum value “100” or the max target values you have. Do the same with the secondary axis as well.
  • At this point, your thermometer chart is almost ready, the only thing you need to do is a bit of formatting for the final touch.
    1. For target bar, use no fill for color and a solid line color border.
    2. Use the same color for achievement bar (fill and border) which you have used for target bar border.
    3. Delete chart title, horizontal axis, and right vertical axis.
    4. Select the left axis ➜ formatting pane ➜ Axis Options ➜ Tick Marks ➜ outside tick marks for major and minor.

Hurrah! We did it. Our thermometer chart is ready to rock.

You can also add a shape below your chart to make it looks like a real thermometer (I don’t like to add but, you can).

Download this file from here to check my ready to use excel thermometer chart.

Dynamic Thermometer Chart with Different Colors

When I was writing this post, one of my friends who was sitting beside me, asked: “Hey, why don’t you use different colors for different levels in this thermometer chart.”

So, I did.

This one is a little bit tricky but worth to learn & you’ll enjoy your final chart. I have used different colors for different levels.

It goes like this.

  • Up to 40 – Red color bar.
  • Less than 70 and more than 40 – Yellow color bar.
  • More than 70 – Green color bar.

So, let’s get started...

  • Enter following data in your worksheet.
  • Enter target value (I’m using 100 here) and in the achievement cell insert the actual achievement value.
  • After that, we have to insert three simple formulas in rest of the three cells.
  1. From Excellent: =IF(F4>=70,F4,””)
  2. For Good: =IF(AND(F4>=40,F4<70),F4,””)
  3. For Bad: =IF(F4<40,F4,””)

Note: These formulas will show achievement value if it is falling in their range.

  • Now select the target, excellent, good, and bad cells and insert a column chart.
  • At this point, we have a chart like below with four bars but two out them have no value.
  • After that, change “Switch Row/Column”.
  • Now we need to change three bars (Excellent, Good, and Bad) into secondary axis. This is little tricky because you can’t select bars easily which have zero value but I have a good solution for that.
  1. Select your chart ➜ Right click ➜ Format chart area.
  2. Click on the chart options drop down and select Series “Excellent”.
  3. Change the chart plot to secondary (Do this for excellent, good, and bad bars) .
  4. Make series overlap 100% (Do this for all four bars).
  5. Gap width 0% (Do this for all four bars).

Final touch:

  1. Match axis unit values for both of the axes.
  2. Delete chart title.
  3. Delete secondary vertical axis and horizontal axis.
  4. Add tick marks.

Bang! here is your thermometer chart.

Download this sample file from here to learn more about this.

Conclusion

Creating a basic thermometer chart in Excel is simple.

And, if you want to create this color changing thermometer chart you need some extra steps, I hope you don’t mind for that.

A few days back, I found some people saying that it’s better to use thermometer chart than using a gauge chart.

I have used a thermometer chart in my one page daily sales report template.

Now tell me one thing.

Have you ever tried to create a thermometer chart in Excel? What do you think about its application in your dashboards?

Please share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.

Must Read Excel Tutorials

  1. Bullet Chart in Excel: It's just like a thermometer chart with a bullet like line as a target line and different [...]
  2. Waffle Chart in Excel: It's a set of hundred small square boxes to present one box as 1% of achievement [...]
  3. Pictograph in Excel: It's a normal chart where you can use an image or icon to present data line or column [...]
  4. Population Pyramid in Excel: A specific chart to present population in different age groups using a comparison with previous period [...]
  5. Pivot Chart in Excel: It's a graphical representation of a pivot table. You can use all kind of charts as a pivot chart but [...] 

The post How to Create a Thermometer Chart in Excel appeared first on Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Ever faced a situation where you need to generate random letters?

Well, sometimes you do need to generate some random alphabets.

Right?

In Excel...

You have functions like RAND and RANBETWEEN to generate random numbers…

…but we don’t have any particular function to generate letters.

Today, I’d like to share with you an amazing formula trick to generate random letters in Excel.

And, the best part is it’s simple and easy to use.

So what's the Formula to Generate Random Letters in Excel?

As I said, there is no direct function in excel which can help you to get random letters/alphabets.

But, you can use a combination of RANDBETWEEN and CHAR to create a formula to get random letters.

If you want to Generate CAPITAL Random Letters

In this formula, you need to use 65 and 90 for the random letter to be in the capital letter.

=CHAR(RANDBETWEEN(65,90))

or if you want to Generate small Random Letters:

In this formula, you need to use 97 and 122 for the random letter to be in the capital letter.

=CHAR(RANDBETWEEN(97,122))

...how this formula works

Now, let me tell you how does this formula generate these letters.

First of all, you have to understand that this formula works in two different parts.

In the first part, you have RANDBETWEEN function which can generate random numbers for you.

You just have to specify the lowest number and highest number and it will return a random number from that range.

In short, you will get a random number in result within the range.

Now, in the second part, you have CHAR function which can return a specific character when you specify a number in it.

That means for each character there is a specific number you have to insert in CHAR function.

And numbers 65 to 90 represent capital letters and 97 to 122 small letters.

If you enter 65 in CHAR it will return “A” and for 97 it will return “a”.

The Bottom Line is:

  • When you specify any of the above range in RANDBETWEEN returns a random number.
  • And after that CHAR returns a character for that number.

This is the whole story.

In the end,

The formula you have used above is simple and easy to apply, there’s no doubt about it.

The best use of this method in the real world which I have found is…

…you can generate random groups for participants based on alphabets.

I hope this formula tip will help you to Get Better at Excel but now, you have to tell me one thing.

Do you have any other method to create random letters?

Make sure to share your views with me in the comment section, I'd love to hear from you and please, don’t forget to share this post with your friends, I am sure they will appreciate it.

The post How to Quickly Generate Random Letters in Excel appeared first on Blog.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview