Tag: Excel Charts

Show Negatives In A Chart In A Different Color Using The Invert If Negative Feature In Excel

How To Show Negatives In A Chart In A Different Color Using The Invert If Negative Feature In Excel

With this video we are going to use the “Invert If Negative” feature in the charts. This is useful to show the negative values in the chart with another color and you don’t have to manually change the color in each bar. You can learn more about Microsoft Excel charts and applications here: Excel Training Videos

Create a Marimekko Chart in Excel

How to Create a Marimekko Chart in Excel

Marimekko charts encode two variables: one along the height of the vertical axis and another using the width of the bars or columns. This one from The Economist, for example, has GDP per capita ($) on the vertical axis and share of global population (%) along the horizontal axis.

There are alternatives, of course; they could have used a scatterplot or maybe two column charts. But the Marimekko, I think, does a nice job extending the standard column chart in another dimension.


How to Create a Marimekko Chart in Excel


Learn How to construct it in the following link: Marimekko Chart in Excel

Create a Basic Chart in Microsoft Excel

Create a Basic Chart in Microsoft Excel

When you have organized data in a table is very useful to show it in a better way than the simple data table.

You can create a Microsoft Excel Chart and show the data in a graphical way. In this video you can learn step by step how to do it.

In this example you can see a kind of Chart in Excel, but you can create a lot of different types of Chart to show your informacion

How to Create Target Lines in Excel Charts

How to Create Target Lines in Excel Charts

In this video, you can Learn how to add a target line in bar chart. You can do that for make an evaluation of the results of a variable compared with a target.

The way to do that is adding the column of the target values to the chart. Then you have to select the new target serie from the chart. Finally change the chart type, from bars to line.

Waterfall Charts in Excel

Create a Waterfall Chart in Excel

 

 
A waterfall chart helps to visualize the contribution made by each of the parties to the total. This chart type is distinguished by having “floating” columns because they give the impression of being suspended in the air.
 

This is the waterfall chart that we are going to do:

Waterfall Chart with Microsoft Excel

In our example we are showing the sales of a company in 4 countries where they have presence: Brazil, Colombia, Argentina and Mexico. Each country brings a portion of the total sales of the company. TO begin with the construction of the chart, we have to take in count 2 elements: – Base Values: These values are the blank spaces on which “will float” the columns. – Connectors: A series of data will be needed to show the connectors between the floating columns. Now we are going to see how the Data Table is.

Waterfall Chart Data Table

Create the Waterfall Chart To create a waterfall chart select the data range to be graphed (A2: G6) and go to the Insert tab in the Charts Section and click the button to select Column and the Stacked Column option. As a result you have the following chart in Excel.

Initial Waterfall Chart

Now we swap the rows and columns. Right click on the graph and select the Select Data option, in the dialog box click on the Switch Row/Column button and accepts the changes. The graph will look as follows:

Switched Rows and Columns in a Waterfall Chart

The upper columns are the corresponding connectors and now will become lines. To make the change, you need to right click on the first of the series of connectors (in this example the green columns). In the pop-up menu you must select Change Series Chart Type and go to the Line section and choose the Lines chart type. Repeat this process for all columns of connectors and you finally you will have the chart below.

Change Chart Type for the Connectors in a Waterfall Chart

To format each connector you need to right click on each line and select the Format Data Series and you should consider making the following changes: In the Line Color section select Solid Line option and change the color to black In the Line Style section sets a width of 0.25 pt. and in Dash type choose Square Dot. Apply these changes for each connector. Optionally remove the gridlines of the chart and have the following result.

Change Connectors Format in a Waterfall Chart

It only remains to remove the fill color of the base values, for do this right-click on the corresponding data set (in this example the blue columns) and select the menu option Format Data Series and select in the Fill section No Fill option. When you close the dialog box will get the following result.

Format Base Columns in a Waterfall Chart

You need only to remove the legend to having a waterfall chart as shown the beginning of the article. (Source: Excel Total)
 

 

Chart Layout & styles

Excel Chart Layout and Styles




You can quickly format your chart by applying a different chart layout and a different chart style. The chart layout includes elements such as the titles, data labels, legend, gridlines, and data table.

Excel’s Quick Layouts feature enables you to apply these elements in different combinations with just a few mouse clicks. The chart style represents the colors used by the chart data markers and background.

Customizing the Excel Chart Elements Outline

Customizing the Excel Chart Elements Outline

 

 

You can make a chart element stand out by customizing the element’s outline, which refers to the border that appears around the element, as well as to single-line elements, such as gridlines and axes. You can customize the outline’s color, its weight, that is, its thickness, and whether the line is solid or consists of a series of dots or dashes.

Microsoft Excel Chart Elements and Types

Microsoft Excel Chart Elements and Types

 

 

A chart is a graphic representation of spreadsheet data that uses columns, points, pie wedges, and other forms to represent numbers from a select range. As the data in the spreadsheet changes, the chart also changes to reflect the new numbers. To get the most out of charts, you need to familiarize yourself with the basic chart elements.

Category Axis: The axis (usually the X axis) that contains the category groupings.

Chart Title: The title of the chart.

Data Marker: A symbol that represents a specific data value. The symbol used depends on the chart type.

Data Series: A collection of related data values. Normally, the marker for each value in a series has the same pattern.

Data Value: A single piece of data. Also called a data point.

Gridlines: Optional horizontal and vertical extensions of the axis tick marks. These make data values easier to read.

Legend: A guide that shows the colors, patterns, and symbols used by the markers for each data series.

Plot Area: The area bounded by the category and value axes. It contains the data points and gridlines.

Value Axis: The axis (usually the Y axis) that contains the data values.

Understanding Chart Types:

Excel offers 11 different types of charts, including column charts, bar charts, line charts, and pie charts. The chart type you use depends on the type of data and how you want to present that data visually. Although you must select a particular chart type when you first construct your chart, you can quickly and easily change to a different chart type later on if you need to.

Column: A chart that, like a bar chart, compares distinct items or shows single items at distinct intervals. However, a column chart is laid out with categories along the horizontal axis and values along the vertical axis.

Line: A chart that shows how a data series changes over time. The category (X) axis usually represents a progression of even increments (such as days or months), and the series points are plotted on the value (Y) axis. Pie: A chart that shows the proportion of the whole that is contributed by each value in a single data series. The whole is represented as a circle (the “pie”), and each value is displayed as a proportional “slice” of the circle.

Bar: A chart that compares distinct items or shows single items at distinct intervals. A bar chart is laid out with categories along the vertical axis and values along the horizontal axis.

Area: A chart that shows the relative contributions over time that each data series makes to the whole picture.

Scatter or X Y Chart: A chart that shows the relationship between numeric values in two different data series. It can also plot a series of data pairs in XY coordinates.

Stock: A chart that is designed to plot stock market prices, such as a stock’s daily high, low, and closing values.

Surface: A chart that analyzes two sets of data and determines the optimum combination of the two.

Doughnut: A chart that, like a pie chart, shows the proportion of the whole that is contributed by each value in a data series. The advantage of a doughnut chart is that you can plot multiple data series.

Bubble: A chart that is similar to an XY chart, except that there are three data series, and in the third series the individual plot points are displayed as bubbles (the larger the value, the larger the bubble).

Radar: A chart that makes comparisons within a data series and between data series relative to a center point. Each category is shown with a value axis extending from the center point.

Select a different Chart Type in Excel

Change the Type of a Chart in Excel

 

 

 

If you feel that the current chart type is not showing your data in the best way, you can change the chart type with just a few mouse clicks. For example, you might want to change a bar chart to a pie chart or a line chart to a stock chart.

You can also save yourself some work by configuring Excel with a new default chart type, and by saving the current chart type and chart formatting as a template that you can reuse later on.

 

 

Learn How to Create a Chart in Excel

How to Create a Chart in Excel

 

 

 

You can create a chart from your Excel worksheet data with just a few mouse clicks. As shown in “Understanding Chart Types”, Excel comes with 11 main chart types.

However, each of these types has several predefined varieties, so in all Excel offers more than 70 default chart configurations, which means there should always be a type that best visualizes your data. Regardless of the chart type you choose originally, you can change to a different chart type at any time.