Category: Excel Charts

A dot plot (also known as a strip plot or a dot chart) is a graphical representation of data plotted using dots in the x- and y-axes. It is commonly used in statistics to show data trends, frequencies, and groupings.

By default, a dot plot is not readily available in Excel. However, we can use the existing Excel charts to create one. In this article, I’ll show you how to do just that.

Note that dot plots are only ideal on smaller datasets. Large datasets will require more dots, making it more difficult to manage them.

How to Create a Dot Plot in Excel?

For this illustration, I’m going to use a dummy dataset showing the number of people (per group) that would choose a particular flavor over the other.

1. The first step we need to do is to count the number of columns inside the dataset that contains the numbers that we want to plot in our charts.

In my example above, I have only four columns for this (columns B to E).

Next to the dataset, add the numbers 1 to 4 (or up to the maximum number of columns you want to plot in your chart). See the example below.

2. Next, we’re going to add a Clustered Column Chart.

Select the headers of the columns you intend to plot along with their first data row.

Go to the Insert Tab and select the small arrow button inside the Charts section (see screenshot below).

Doing so will open the Insert Chart menu. Go to the All Charts tab.

From the list of charts, click “Column” and select the “Clustered Column”chart(the first one in the list).

Once selected, click OK.

The clustered column chart should now be added to your sheet.

3. Next, we’re going to add a blank series to our chart.

Right-click on this chart and click Select Data.

The Select Data Source menu will appear.

Click the Edit button under the Legend Entries (Series) section.

Change the Series values to 0,0,0,0.

Make sure that there’s one zero for each column you intend to plot.

So, if you have three columns, you should type 0,0,0.

After typing the correct Series Values, click OK and close the Select Data Source menu.

You should now see your chart emptied (as shown in the image below).

4. Next, we are going to add new series (one for each column).

Right-click again on the chart and click on Select Data.

Click the Add button under the Legend Entries (Series) section.

From the Edit Series menu, click the arrow button next to the Series Name.

Select the cell containing the header of the first column you want to plot and click OK.

We are going to leave the Series values as is for now.

Repeat these same steps until you’ve added a new series for each column.

Once done, your Select Data Source menu should look something like this:

Click OK to close the menu.

5. Next, we are going to change the Chart Type to “Combo”.

Right-click on the chart and select Change Chart Type.

From the list of charts, select Combo.

You will then see the series that you have added to the chart.

Change the Chart Type of all the series to Scatter (except for “Series1”).

Once done, click OK.

Your chart should now only have one dot inside the chart (see the example below).

6. As the final step, we are now going to update all the Series Values so that they point to the right numbers.

Right-click on the chart and click Select Data.

Select the second series (after “Series1”) and click the Edit button.

Change the Series X Values so that they point to the numbers that we have previously added (1 to 4).

Since we’re first working on the first column, select all the 1’s.

Next, change the Series Y Values so that it points to the corresponding numbers to be plotted.

Once done, click OK.

Repeat the same steps until you’ve updated the X and Y Values of all your series.

Once done, close the Select Data Series menu.

And that’s it! You should now have a dot plot in your sheet.

Change the Size of the Dots

If you want to enhance your dot plot, you can resize the dots so that they look bigger or smaller, whichever you prefer.

Right-click on one of the dots of the data series you want to resize and select Format Data Series.

The Format Data Series menu will appear on the right side of the screen.

1. Click the paint bucket button on top.
2. Select “Marker”.
3. Click Marker Options.
4. Select Built-in.
5. Change the size to whatever you like. You can use the up and down arrow to change it.

Repeat these steps with the other data series in your plot.

If you like, you can also add borders to your dots so that they stand out.

Simply go to the Format Data Series >> (paint bucket button) >> Marker >> Border.

You can select the Solid Line and change the color with whatever you like.

And that’s it! You’ll have your dot plot fully customized to your liking.

Conclusion

As you can see, even though dot plots are not available in Excel, we can still create one using the combination of a Clustered Column Chart and a Scatter chart.

Here is the summary of the steps of creating a dot plot in Excel:

1. At the right side of the dataset, add the numbers 1 up to 3 (or up to the total number of columns you want to plot).
2. Select the header row along with the first data row.
3. Insert a clustered column chart.
4. Edit “Series1” so that its values are 0,0,0. Remember that each column should have a corresponding zero.
5. Add new series to the chart. There should be one series for each column. Edit the Series Name so that they all point to the corresponding header. Leave the Series Values as is for now.
6. Change the chart type to Combo. Change the chart type of all the series (except for “Series1”) to Scatter.
7. Edit all the series (except for “Series1”) so that they point to the right numbers. Series X should be pointing to the numbers we have added in Step 1. Series Y should be pointing to the numbers to be plotted in the chart.
8. And that’s it! You’re all set.

Other Charts and Plots in Excel:

Tornado Chart in Excel
Contour Plot in Excel
Stem and Leaf Plot in Excel

Excel provides you with a lot of tools to visualize your data, but sometimes it is not easy to decide which tool would work better for you. The Quick Analysis Tool is one of its kind that lets you browse through various options for data visualization in Excel.

But what is Quick Analysis? Quick Analysis Tool is a collection of functions and features in Excel that enable Excel users to quickly analyze data.

In the Quick analysis tool, most options come fixed, but some options vary depending on the selected data. The best part of the Quick Analysis tool is that you don’t need to tabulate data in order to analyze the same; instead, just hovering over the data gives you a sneak peek into it.

Must note that the Quick analysis tool was only introduced in Excel in 2013. So, if you are using any Excel version that’s older than the 2013 version, you won’t be able to enjoy this tool.

Those using Excel 2013 and onwards can use the Quick Analysis Tool to quickly perform many functions like adding up values, inserting charts and icons for data analysis. To learn more about how you can exploit the Quick analysis tool to your benefit, stay tuned.

Where to Find Quick Analysis Tool in Excel?

It is important to know that you won’t find any tab or ribbon option for accessing the Quick analysis tool. In fact, you first have to select the data range that is to be analyzed.

Consider the following example:

Once you select the data range, a small icon will appear at the bottom right of the selection. This is the same icon that goes unnoticed by 80% of Excel users.

This is a Quick analysis tool. Not many Excel users make use of this powerful tool as they don’t even know about it.  

In addition to data selection, you can also access the Quick analysis tool by using the shortcut key ‘Ctrl +Q’.

Click on the Quick Analysis button at the bottom as shown above to win access to various options that the Quick Analysis Tool offers.

How to Use the Quick Analysis Tool in Excel?

You can only make the best use of this powerful tool when you are familiar with the data analysis options that it offers.

Let’s look into more details on how you can use the Quick Analysis Tool.

• First, select the data range you want to analyze.
• Click on the Quick Analysis button at the bottom right.
• Go to the tab you want to use to analyze your data.

Tabs:

The Quick Analysis tool has five tabs: Formatting, Charts, Totals, Tables, and Sparklines. Let us look into each of them below.

> Formatting Tab

As the name suggests, the formatting tab of the Quick Analysis Tool offers various data formatting options for your data.

1.       Putting Data Bars into your selected data

Form the Quick Analysis Tool, select the Formatting tab and hover the mouse over the Data Bars option. You will get your data formatted looking like this:

2.       Putting Colors Into Your Selected Data

Selecting the Color option under the formatting tab will give your data a colorful look like this.

Must Note: Pay closer attention to notice that the shading performed by Excel is based on the value i.e. the smallest value is colored in red whereas the greatest value is colored in green. The values in between are accordingly shaded depending upon how big or small they are in relation to the overall data.

3.       Putting An Icon Set with Your Selected Data

Selecting the ‘Icon Set’ option will add Arrow icons to your data, and your data will look like this.

4.       Using the “Greater Than” option

This option enables you to control the cell formatting according to your requirements. For example, if you want to format only those cells with a value greater than 30, select the option Greater than from the Quick Tool window.

A dialogue box will appear asking for the threshold value and formatting options as shown below.

Let’s insert the value 30 under the “Format cells that are GREATER THAN” option. From the drop-down list, you can choose the color combination for those cells which meet your condition.

The results look like as follows.

Since the condition was set to format only those cells with values greater than 30, Excel highlighted all those cells with values greater than 30.

The rest of the formatting options format the data based on the smallest and the greatest values.

> Charts Tab

The Charts tab helps you create colorful charts to visualize the selected data. Let us discover the various types of charts that this tool offers you.

1.       Line Chart

This option converts your data into line charts that look like this.

2.       Clustered Chart

Another useful chart offered by Excel for visualizing data is the clustered chart. Here is how a clustered chart looks like.

> Total Tab

The total tab is one of the most powerful options of the Quick Analysis tool. With a simple click, it provides you with sums, averages, percentages, and many more operation results performed on the underlying data.

1.       Sum

Selecting the Sum option will instantly provide with you the Sum of sales of each category at the bottom row. Take a look below.

2.       Average

The Average option instantly provides you with the average of all the data values columns. Select the Average option from the Totals tab to see the results as follows.

3.       Count

The Count option provides you with the total number of values in a single column. Select the Count option from the total tab to yield results as follows.

> Table Tab

The Table tab allows you to transform the underlying data into a table in an instant. Select the options table, and you get your data transformed in a tabular format like this.

Using the Blank option will create a pivot table in the new sheet that you can use to analyze your data quickly. You can also use the Sparkline option to analyze your data with sparklines.

> Sparklines Tab

The sparklines tab of the Quick Analysis Tool bar allows you to visualize your data in the form of mini charts encapsulated within a single cell. These can be line charts, column charts or win/loss charts.

Let us see how can we add sparklines to Excel. The data in the image below represents sales made by different employees during three different months.

Adding sparkline to Excel to visualize these sale trends requires you to take the following steps.

Step 1:

Select the data to be converted into a sparkline to activate the Quick Analysis Tool icon. Alternatively, you can select the data and press ‘Ctrl + Q’.

Step 2:

Click on the Quick Analysis tool icon and go to the sparkline tab. Under the sparkline tab, select the kind of sparkline i.e. Line, column, or win/loss that you want to add to Excel.

Step 3:

Following the above two steps, Excel would add sparklines for the selected data to the activated cells, as shown below.

Seems a little dull? You can format the sparklines in different ways to add more value to them. To explore the sparkline editing options, select any sparkline and go to the Sparkline tab on the Ribbon.

You can highlight the high and low points of each sparkline to analyze the trends better. Not limited to the above, you can also change the color, density and design of your sparklines using the options under the sparkline tab.

Conclusion

The Quick Analysis Tool is like its name: quickly analyze your data without putting it into a table. You won’t see the tool on the ribbon, but it’s not hard to access and makes it easier to analyze text and numerical values especially when you are short on time.

Hope this article enables you to use the Quick Analysis Tool to quickly calculate the sums and averages, and also add pivot tables and charts. Keep exploring Excel!

A contour plot is a visual representation of a 3-dimensional surface. It plots the contours (or the constant z slices) in a 2-dimensional format. Lines are drawn to connect the x- and y-coordinates where the z value occurs.

Contour plots or graphs are commonly used in cartography (the study and creation of maps). It can give a 3D representation of a particular area based on geographic coordinates.

Other fields like astrology, meteorology, and statistics make use of this chart too.

Whatever field you may be in, I’ll walk you through the steps in creating both the 2-D Contour Chart and the 3-D Surface Chart in Excel.

How to make a 2-D Contour Plot in Excel?

A contour chart (as seen in the image above), is the top view of the 3-D surface area.

To create one, you only need to select all the data in your dataset.

Go to the Insert tab. Click the small arrow inside the Charts section (as shown below).

The Insert Chart window will appear.

Go to the All Charts tab. From the list of chart types, click Surface.

Select the Contour chart (the third one in the list) and click OK.

And that’s it! You’ll have a Contour Chart added to your sheet.

If you like, you can change the color schemes applied to the chart.

With the chart selected, go to the Chart Design tab.

Click the Change Colors button and select your desired color scheme – it can either be colorful or monochromatic.

As you hover your mouse over each color scheme, notice that the chart also changes colors.

Once you’ve clicked your desired set of colors, the chart will automatically reflect these changes.

Don’t forget to rename the chart title (or remove it if you don’t need it).

Feel free to update all other aspects of the chart.

You can select from the preset styles by clicking on the paintbrush button next to the chart.

How to Transform a 2-D Contour Plot into a Wireframe Format?

In a wireframe format, only lines and vertices are shown. There will be no color on the surface.

If this is the format that you prefer, click on your existing contour chart.

Go to the Chart Design tab and click the Change Chart Type button.

The Change Chart Type window will appear.

From the list of Surface charts, select Wireframe Contour (the fourth one on the list) and click OK.

And that’s it! You’ll have your contour plot in wireframe format.

How to Make a 3-D Surface Chart in Excel?

If you prefer the 3-D front and side views of your contour plot, then the 3-D Surface Chart is what you need.

 To add one, simply select all the data in your dataset (including the headers).

Go to the Home tab and click the Recommend Charts button.

The Insert Chart window will appear. Go to the All Charts tab.

From the list of charts, select Surface. Click the 3-D Surface chart (the first chart type on the list) and click OK.

And that’s it! You’ll have your 3-D Surface chart added to your sheet.

Same with the Contour Plot, you can configure the color schemes, design, and other aspects of the chart.

How to Transform a 3-D Surface Chart into Wireframe Format?

If you prefer the wireframe format of your surface chart (just lines and no surface colors), select your current chart and click the Change Chart Type button from the Chart Design tab.

In the Change Chart Type window, select the Wireframe 3-D Surface chart (the second one from the list) and click OK.

And that’s it! You’ll have your surface chart in wireframe format.

Conclusion

As you can see, contour plots are not that hard to add in Excel. All you need is to have your dataset ready. Once that’s all set, you can easily add either a 2-D contour plot or a 3-D surface area chart to your sheet in just a few clicks. I hope you find this article helpful.

Related Tutorials:

How to Create a Stem and Leaf Plot in Excel
How to Make a Scatter Plot in Excel
Tornado Chart in Excel

Did you know that the Scatter Chart is the only chart in Excel having both X and Y axes?

We draft a scatter chart (also known as a scatter graph or plot) to showcase the relationship between two variables. We use it to identify the patterns within the data and see if there are any unexpected gaps or outlier points. All these can help in analyzing and interpreting our dataset.

We typically use a dot to represent the point of intersection between two numeric variables. But it can be other symbols too, like square, triangle, plus sign, etc.

The perpendicular lines on the graph refer to the x and y axes.

• The x-axis is the horizontal line.
• The y-axis is the vertical line.

In this tutorial, I will show you how to switch the x-axis and the y-axis in a Scatter Chart. 

Scatter Chart Default Settings

When you create a scatter chart in Excel, the leftmost numeric column in the dataset, by default, becomes the x-axis, while the column that comes after it becomes the y-axis.

In theory, the x-axis values refer to the independent variables, and the y-axis values refer to the dependent variables.

If for any reason, you need to swap the two, you can do the steps listed in the following options.

Change the X-axis and Y-axis on a Scatter Plot by Editing the Series

1. Right-click anywhere on the chart. From the list of options that appear, click on Select Data.

2. Once the Select Data Source form appears, click on the Edit button under the Legend Entries (Series) section.

3. You should now see the Edit Series form (as shown below).

4. First, we are going to reset the contents of the x-axis.

Highlight the entire text in Series X values and press DEL.

5. Next, click on the upward arrow button next to the textbox.

This button will let you select the range that will serve as the basis for the values on the x-axis.

The blank Edit Series form should appear.

6. Once you see that, highlight the numeric values in your data that you want to move to the x-axis.

Note: Do not include the header.

Once selected, click on the downward arrow button. Doing so will lead you back to the Edit Series form.
 
7. Next, we are going to edit the range for the y-axis.
 
Repeat steps 4 to 6, only this time, do it on the Series Y values field.

• Highlight the entire text in Series Y Values and press DEL.
• Click on the upward arrow button next to it.
• Highlight the cells containing the numeric values that intend to move to the y-axis.
• Once done, click on the downward arrow button in the Edit Series form.

8. In the Edit Series form, you should now see your x and y axes ranges interchanged.

Once you are happy with the result, click OK.

9. And that’s it! You should now see the updated version of your chart with the x-axis numbers moved to the y-axis (and vice versa)

10. If you have added axis labels, you may need to edit them to match the new numbers.

In my example above, I must remove the “No. of Hours Worked” from the x-axis and move it to the y-axis.

It should now look like this:

Change the X-axis and Y-axis on a Scatter Plot by Reordering the Data in the Dataset

By theory, the leftmost column should be the independent variable, while the column that comes after is the dependent variable.

If you had these two variables mixed up and now you want to reorder your dataset, then all you must do is switch the columns within the dataset.

To do this:

1. Copy the current x-axis column from the dataset and temporarily paste it somewhere else (e.g., an empty area on your sheet or onto a new sheet)

2. Copy the current y-axis column and paste it onto the leftmost side of the data set.

3. Go back to the empty area where you have temporarily pasted the previous x-axis column.
 
Highlight those cells, copy them, and paste them onto the second column in the dataset.

IMPORTANT: You should do a copy-and-paste, and not a cut-and-paste. Otherwise, your chart will not work as expected as it will not recognize the new data.

4. And that is it! You should now see your chart’s x and y axes interchanged.

Delete the temporary column and, if needed, update your axis labels to display the correct text.

Conclusion

Remember that the scatter charts are the only charts in Excel with both X and Y axes. The solutions suggested above will only work on this kind of chart.

You may have read some articles saying that to switch the x and y axes, you need to go to the Chart Design menu and click on the Switch Row/Column. Please know that this is not correct. This method DOES NOT switch the numbers in the x and y axes. As its name suggests, it instead swaps the row and column of the chart. 

A tornado chart in Excel is a useful tool for sensitivity analysis and comparison. As it greatly helps an efficient comparison between two variables, it is commonly used by businesses and professionals for decision-making purposes.

The general outlook of a tornado chart includes horizontal bars placed opposite to each other with the base as the middle of the chart. As it resembles the shape of a butterfly, it is also known as a Butterfly Chart or a Funnel Chart.

Putting together a tornado chart in Excel often gets a little tricky. Here is all that you need to know about a Tornado Chart in Excel.

How to Create a Tornado Chart in Excel?

Unlike other simple charts, there exists no option to create a Tornado Chart in Excel. Users, therefore, need to create a default bar chart and customize it to a tornado chart.

Follow the steps below to learn how.

Step 1:

Below is a dataset that contains two variables i.e. sales of the same product in two different regions.

First, you need to arrange the first variable (i.e. California for the given dataset) in ascending order. Right-click on the first variable, and from the pop-up menu, choose Sort > Custom Sort as shown below.

From the dialogue box that then opens, choose to Expand the Selection.

As we need to sort the values based on the first variable i.e. California, we have selected the same as ‘Sort By’. The Order is to be kept from smallest to Largest as we need values to be arranged in ascending order.

Here is how excel has arranged the values under the Column Header ‘California’ in ascending order, and the remaining data is accordingly arranged.

Step 2:

Select the data to be plotted and insert a stacked bar chart into excel as follows.

Insert > Charts > Insert Column or Bar Chart > 2-D Bar

Step 3:

Once the chart is inserted, select the first variable that is plotted on the graph. California in our given example is presented by blue so we selected blue bars. Right-click to see the pop-up menu and choose ‘Format Axis’ or ‘Format Data Series’.

From the Plot Series options, check ‘Secondary Axis’ as shown below.

Excel would add a secondary axis to the top of the graph.

Step 4:

Right-click the secondary axis plotted on top and choose the option ‘Format Axis’ as shown below.

Within the secondary axis formatting options, two changes are to be made.

1. Change the minimum axis bounds to negative $90,000. Why that? The highest number of our dataset comes off to be $76,800 – as the axes are plotted at an interval of 20,000, it must fall somewhere after $70,000.  The highest value next to $70,000 at an interval of 20,000 is $90,000. Just pair that value with a negative sign and input it as the minimum bounds. The maximum bounds value would also be the same number but only with a positive sign.

2. Under Display Units, check the box for ‘Values in reverse order’.

Step 5:

This must-have loused up your graph with the orange and blue data plots intermingled, making little sense. This is because the primary and secondary axes are not in line.

To align both axes, select and right-click the primary axis to format the axis as follows.

Again the minimum and maximum bounds are set to -$90,000 and $90,000, respectively. However, for the primary axis, we will leave the box for ‘Values in reverse order’ unchecked.

Step 6:

The graph now somehow seems structured. However, there are yet a few steps more to follow. As we no longer need the secondary axis now, it’s time we say goodbye.

Right-click on the secondary axes to delete it as follows.

Step 7:

Next, we do not need the numbers on our chart to be negative. There’s nothing negative in our dataset, after all. To change these numbers to positive, select the primary axis, right-click and choose ‘Format axis’.

Under the Format Axis menu, open the Number drop-down and make the following changes:

1. Category : Choose Custom
2. Type : Choose ###0;###0
3. Format Code : Choose ###0;###0

Viola! The primary axis now presents a positive number line on both sides.

Step 8:

As of now, the product labels overlap with the bar chart and are barely visible. To make them prominent, select the chart (the blue and orange bars), right-click and click Format Axis.

Under Format Axis, open the dropdown menu for Labels. Set the label position as ‘Low’ to make the product labels move to the left side as shown below.

Feels a little dull? Select the product labels and opt for the Bold option from Home > Font. You may also give them different colors, styles, and fonts.

Step 9:

This step is all about making your chart more appealing and eye-catching. It may be hard for users to identify what figure does each bar represent.

You may add the value represented by each bar parallel to it simply by adding data labels to it. For instance, to add the sales represented by the blue bars, select either of them, right-click and from the pop-up menu, select ‘Add Data Labels’.

Here is how excel adds data labels to the chart.

You may reposition these labels from the center of each bar to the side or even against the end of each bar. Similarly, you may change their font color, size, and style to add prominence to it.

Step 10:

Time to do some final touches to our almost-ready Tornado Chart. Name your chart with a suitable title by clicking and typing in the header ‘Chart Title’.

If the chart seems a little too skinny and suppressed, pump it up a little by increasing the size of each bar. Right-click the chart to open the ‘Format Data Series’ menu.

Under the ‘Series Options’, reduce the Gap Width to your desired size. Must note how this works opposite i.e. decreasing the gap width increases the width of the bars and vice versa.

This is it! You have your Tornado Chart all set to go.

There are many more editing options that you can exercise to make your Tornado Chart in Excel more and more appealing. For instance, you may change the color of bars from solid fill to gradient fill, add borders to them, and much more.

Conclusion:

Creating tornado charts requires a great deal of effort, especially when compared to other in-built charts of Excel. However, the easy-going and efficient results make the effort worth it. It will take you a round or two of practice to master the art of pulling together a Tornado Chart in Excel.

Once you know the steps, surprise people with appealing tornado charts that are the best for two-item comparisons and sensitivity analysis.

Suggested Tutorial: Square Root in Excel