Category: Advanced Excel Charts

Advanced Excel – Thermometer Chart ”; Previous Next Thermometer chart is a visualization of the actual value of well-defined measure, for example, task status as compared to a target value. This is a linear version of Gauge chart that you will learn in the next chapter. You can track your progress against the target over a period of time with a simple rising Thermometer chart. What is a Thermometer Chart? A Thermometer chart keeps track of a single task, for example, completion of work, representing the current status as compared to the target. It displays the percentage of the task completed, taking target as 100%. A Thermometer chart looks as shown below. Advantages of Thermometer Charts Thermometer chart can be used to track any actual value as compared to the target value as percentage completed. It works with a single value and is an appealing chart that can be included in dashboards for a quick visual impact on % achieved, % performance against the target sales target, % profit, % work completion, % budget utilized, etc. If you have multiple values to track the actuals against the targets, you can use Bullet chart that you will learn in a later chapter. Preparation of Data Prepare the data in the following way − Calculate the Actual as a percentage of the actual value as compared to the target value. Target should always be 100%. Place your data in a table as given below. Creating a Thermometer Chart Following are the steps to create a Thermometer chart − Step 1 − Select the data. Step 2 − Insert a Clustered Column chart. As you can see, the right Column is Target. Step 3 − Click on a Column in the chart. Step 4 − Click the DESIGN tab on the Ribbon. Step 5 − Click the Switch Row/ Column button. Step 6 − Right click on the Target Column. Step 7 − Select Format Data Series from the dropdown list. Step 8 − Click on Secondary Axis under SERIES OPTIONS in the Format Data Series pane. As you can see, the Primary Axis and the Secondary Axis have different ranges. Step 9 − Right click on the Primary Axis. Select Format Axis from the dropdown list. Step 10 − Type the following in Bounds under AXIS OPTIONS in the Format Axis pane − 0 for Minimum. 1 for Maximum. Repeat the steps given above for the Secondary Axis to change the Bounds to 0 and 1. Both the Primary Axis and Secondary Axis will be set to 0% – 100%. As you can observe, the Target Column hides the Actual Column. Step 11 − Right click on the visible Column, i.e. Target. Step 12 − Select Format Data Series from the dropdown list. In the Format Data Series pane, select the following − No fill under the FILL option. Solid line under the BORDER option. Blue under the Color option. Step 13 − In Chart Elements, deselect the following − Axis → Primary Horizontal. Axis → Secondary Vertical. Gridlines. Chart Title. Step 14 − Right click on the Primary Vertical Axis. Step 15 − Select Format Axis from the dropdown list. Step 16 − Click TICK MARKS under the AXIS OPTIONS in the Format Axis pane. Step 17 − Select the option Inside for Major type. Step 18 − Right click on the Chart Area. Step 19 − Select Format Plot Area from the dropdown list. Step 20 − Click Fill & Line in the Format Plot Area pane. Select the following − No fill under the FILL option. No line under the BORDER option. Step 21 − Resize the Chart Area to get the Thermometer shape for the chart. You got your Thermometer chart, with the Actual Value as against Target Value being shown. Step 22 − You can make this Thermometer chart more appealing with some formatting. Insert a Rectangle shape superimposing the blue rectangular part in the chart. In the Format Shape options, select the following − Gradient fill for FILL. Linear for Type. 1800 for Angle. Set the Gradient stops at 0%, 50% and 100%. For the Gradient stops at 0% and 100%, choose the color black. For the Gradient stop at 50%, choose the color white. Insert an oval shape at the bottom. Format the oval shape with the same options as of rectangle. The result will be as shown below − Your aesthetic Thermometer chart is ready. This will look good on a dashboard or as a part of a presentation. Print Page Previous Next Advertisements ”;

Advanced Excel – Step Chart ”; Previous Next Step chart is useful if you have to display the data that changes at irregular intervals and remains constant between the changes. For example, Step chart can be used to show the price changes of commodities, changes in tax rates, changes in interest rates, etc. What is a Step Chart? A Step chart is a Line chart that does not use the shortest distance to connect two data points. Instead, it uses vertical and horizontal lines to connect the data points in a series forming a step-like progression. The vertical parts of a Step chart denote changes in the data and their magnitude. The horizontal parts of a Step chart denote the constancy of the data. Consider the following data − As you can observe, the data changes are occurring at irregular intervals. A Step chart looks as shown below. As you can see, the data changes are occurring at irregular intervals. When the data remains constant, it is depicted by a horizontal Line, till a change occurs. When a change occurs, its magnitude is depicted by a vertical Line. If you had displayed the same data with a Line chart, it would be like as shown below. Differences between Line Charts and Step Charts You can identify the following differences between a Line chart and a Step chart for the same data − The focus of the Line chart is on the trend of the data points and not the exact time of the change. A Step chart shows the exact time of the change in the data along with the trend. A Line chart cannot depict the magnitude of the change but a Step chart visually depicts the magnitude of the change. Line chart cannot show the duration for which there is no change in a data value. A Step chart can clearly show the duration for which there is no change in a data value. A Line chart can sometimes be deceptive in displaying the trend between two data values. For example, Line chart can show a change between two values, while it is not the case. On the other hand, a step chart can clearly display the steadiness when there are no changes. A Line chart can display a sudden increase/decrease, though the changes occur only on two occasions. A Step chart can display only the two occurred changes and when the changes actually happened. Advantages of Step Charts Step charts are useful to portray any type of data that has an innate nature of data changes at irregular intervals of time. Examples include the following − Interest rates vs. time. Tax rates vs. income. Electricity charges slabs based on the Units utilized. Preparation of Data Consider the following data − Step 1 − Select the data. Copy and paste the data below the last row of the data. Step 2 − Copy and paste the entire data on the right side of the data. The data looks as given below. Step 3 − Delete the cells highlighted in red that are depicted in the table of second set of data given below. Step 4 − Shift the cells up while deleting. The second set of data looks as given below. Step 5 − Copy the second set of data and paste it to the right side of it to get the third set of data. Step 6 − Select the third set of data. Sort it from the smallest to the largest values. You need to use this sorted third set of data to create the Step chart. Creating a Step Chart Follow the steps given below to create a step chart − Step 1 − Select the third set of data and insert a Line chart. Step 2 − Format the chart as follows − Click on the chart. Click the DESIGN tab on the Ribbon. Click Select Data in the Data group. The Select Data Source dialog box appears. Select Series1 under Legend Entries (Series). Click the Remove button. Click the Edit button under Horizontal (Category) Axis Labels. Click OK. The Axis Labels dialog box appears. Step 3 − Select the cells F2:F10 under the Axis labels range and click OK. Step 4 − Click OK in the Select Data Source dialog box. Your chart will look as shown below. Step 5 − As you can observe, some values (Years) in the Horizontal (Category) Axis are missing. To insert the values, follow the steps given below. Right click on the Horizontal Axis. Select Format Axis. Click AXIS OPTIONS in the Format Axis pane. Select Date Axis under Axis Type in AXIS OPTIONS. As you can see, the Horizontal (Category) Axis now contains even the missing Years in the Category values. Further, until a change occurs, the line is horizontal. When there is a change, its magnitude is depicted by the height of the vertical line. Step 6 − Deselect the Chart Title and Legend in Chart Elements. Your Step chart is ready. Print Page Previous Next Advertisements ”;

Discuss Advanced Excel Charts ”; Previous Next Excel charts are an efficient means to visualize the data to convey the results. In addition to the chart types that are available in Excel, some application charts are popular and widely used. In this tutorial, you will learn about these advanced charts and how you can create them in Excel. Print Page Previous Next Advertisements ”;

Advanced Excel – Funnel Chart ”; Previous Next Funnel chart is used to visualize the progressive reduction of data as it passes from one phase to another. Data in each of these phases is represented as different portions of 100% (the whole). Like the Pie chart, the Funnel chart does not use any axes either. For example, in a sales pipeline, there will be stages as shown below. Prospects → Qualified Prospects → Proposals → Negotiations → Final Sales. Typically, the values decrease gradually. Many prospects are identified, but a part of them are validated and even lesser qualify for Proposals. A still lesser number come for negotiations and in the end, there is only a handful of deals that are won. This will make the bars resemble a funnel. What is a Funnel Chart? The Funnel chart shows a process that starts at the initial state and ends with a final state, where it is noticeable in what stages the fall out happens and by what magnitude. If the chart is also combined with research data, meaning quantified measurements of just how many items are lost at each step of the sales or order fulfillment process, then the Funnel chart illustrates where the biggest bottlenecks are in the process. Unlike a real funnel, not everything that is poured in at the top flows through to the bottom. The name only refers to the shape of the chart, the purpose of which is illustrative. Another variant of Funnel chart is where the data in each of these phases is represented as different portions of 100% (the whole), to show at what rate the changes occur along the Funnel. Like the Pie chart, the Funnel chart does not use any axes either. Advantages of Funnel Charts Funnel chart can be used in various scenarios, including the following − To allow executives to see how effective the sales team is in turning a sales lead into a closed deal. A Funnel chart can be used to display Web site visitor trends. It can display visitor page hits to the home page at the top, and the other areas, for e.g. the web site downloads or the people interested in buying the product will be proportionally smaller. Order fulfillment funnel chart with the initiated orders on top and down to the bottom the orders delivered to satisfied customers. It shows how many there are still in the process and the percentage cancelled and returned. Another use of Funnel chart is to display sales by each salesperson. Funnel chart can also be used to evaluate Recruitment process. Funnel chart can also be used to analyze the order fulfillment process. Preparation of Data Place the data values in a table. Step 1 − Insert a column in the table as shown below. You will get the following data. You will use this table to create the Funnel chart. Creating a Funnel Chart Following are the steps to create the Funnel chart − Step 1 − Select the data and insert a Stacked Bar chart. Step 2 − Right click on the Shift Bars (blue color in the above chart) and change Fill color to No Fill. The chart looks as shown below. Step 3 − Design the chart as follows. Right click on the Vertical Axis. Select Format Axis from the dropdown list. Click AXIS OPTIONS in the Format Axis pane. Check the box – Categories in reverse order. Step 4 − Fine tune the chart as follows. Deselect Legend in Chart Elements. Change the Chart Title. Right click on the orange Bars. Select Format Data Series. Change the Gap Width to 0% in the SERIES OPTIONS. Step 5 − Select Data Labels in Chart Elements. Your Sales Pipeline Funnel chart is ready. Formatting a Funnel Chart Step 6 − Make the chart more appealing as follows Click on the Chart Elements. Deselect Horizontal Axis. Deselect Gridlines. Right click on the Bars. Select Format Data Series from the dropdown list. Click on Fill & Line under SERIES OPTIONS in the Format Data Series pane. Click on Gradient fill under the Fill option. Set Gradient stops. Step 7 − Click on Solid line under BORDER. Select Color as black. Your formatted Funnel chart is ready. Aesthetic Funnel Chart Now that you understood the fundamentals of Funnel chart, you can proceed to create an aesthetic Funnel chart that actually looks like a Funnel as follows − Step 1 − Start with the original table of data. Step 2 − Select the data and insert a 3-D Stacked Column chart. Step 3 − Design the chart as follows. Right click on the Columns. Select Format Data Series. Click on SERIES OPTIONS in the Format Data Series pane. Select Full Cone under Column shape. Step 4 − Fine tune the chart as follows. Click the Design tab on the Ribbon. Click Switch Row / Column. Right click on the Vertical Axis and select Format Axis from the dropdown list. Click AXIS OPTIONS in the Format Axis pane. Check the box – Values in reverse order. Step 5 − Deselect all the Chart Elements Step 6 − Right click on the Bars and select Format Data Series from the dropdown list. Step 7 − Click on SERIES OPTIONS in the Format Data Series pane and type 50% for Gap Depth under SERIES OPTIONS. Step 8 − Format your chart with details as follows. Select Data Labels in Chart Elements. Place them and format them. You can choose Call Out option for the Data Label of the bottom part. Give a Chart Title. Your Funnel chart is ready. Print Page Previous Next Advertisements ”;

Advanced Excel – Pareto Chart ”; Previous Next Pareto chart is widely used in Statistical Analysis for decision-making. It represents the Pareto principle, also called the 80/20 Rule. Pareto Principle (80/20 Rule) Pareto principle, also called the 80/20 Rule means that 80% of the results are due to 20% of the causes. For example, 80% of the defects can be attributed to the key 20% of the causes. It is also termed as vital few and trivial many. Vilfredo Pareto conducted surveys and observed that 80% of income in most of the countries went to 20% of the population. Examples of Pareto Principle (80/20 Rule) The Pareto principle or the 80/20 Rule can be applied to various scenarios − 80% of customer complaints arise from 20% of your supplies. 80% of schedule delays result from 20% of the key causes. 80% of a company profit can be attributed to 20% of its products. 80% of a company revenues are produced by 20% of the employees. 80% of the system problems are caused by 20% of causes of defects. What is a Pareto Chart? A Pareto chart is a combination of a Column chart and a Line chart. The Pareto chart shows the Columns in descending order of the Frequencies and the Line depicts the cumulative totals of Categories. A Pareto chart will be as shown below − Advantages of Pareto Charts You can use a Pareto chart for the following − To analyze data about the frequency of problems in a process. To identify the significant causes for problems in a process. To identify the significant areas of defects in a product. To understand the significant bottlenecks in a process pipeline. To identify the largest issues being faced by a team or an organization. To know the top few reasons for employee attrition. To identify the topmost products that result in high profit. To decide on the significant improvements that increase the value of a company. Preparation of Data Consider the following data, where the defect causes and the respective counts are given. Step 1 − Sort the table by the column – Defect Count in descending order (Largest to Smallest). Step 2 − Create a column Cumulative Count as given below − This would result in the following table − Step 3 − Sum the column Defect Count. Step 4 − Create a column Cumulative % as given below. Step 5 − Format the column Cumulative % as Percentage. You will use this table to create a Pareto chart. Creating a Pareto Chart By creating a Pareto chart, you can conclude what are the key causes for the defects. In Excel, you can create a Pareto chart as a combo chart of Column chart and Line chart. Following are the steps to create Pareto chart − Step 1 − Select the columns Defect Causes and Defect Count in the table. Step 2 − Insert a Clustered Column chart. Step 3 − As you can see, the columns representing causes are in descending order. Format the chart as follows. Right click on the Columns and click on Format Data Series. Click SERIES OPTIONS in the Format Data Series pane. Change the Gap Width to 0 under SERIES OPTIONS. Right click on the Columns and select Outline. Select a dark color and a Weight to make the border conspicuous. Your chart will be as shown below. Step 4 − Design the chart as follows. Click on the chart. Click the DESIGN tab on the Ribbon. Click Select Data in the Data group. The Select Data Source dialog box appears. Click the Add button. The Edit Series dialog box appears. Step 5 − Click on the cell – Cumulative % for Series name. Step 6 − Select the data in Cumulative % column for Series values. Click OK. Step 7 − Click OK in the Select Data Source dialog box. Your chart will be as shown below. Step 8 − Click the DESIGN tab on the Ribbon. Step 9 − Click Change Chart Type in the Type group. Step 10 − Change Chart Type dialog box appears. Click the All Charts tab. Click the Combo button. Select Clustered Column for Defect Count and Line for Cumulative %. Check the box – Secondary Axis for Line chart. Click OK. As you can observe, 80% of the defects are due to two causes. Print Page Previous Next Advertisements ”;

Advanced Excel Charts – Useful Resources ”; Previous Next The following resources contain additional information on Advanced Excel Charts. Please use them to get more in-depth knowledge on this. Useful Video Courses Excel – A Step by Step Complete Course 56 Lectures 6 hours TELCOMA Global More Detail Excel Makro ve VBA Eğitimi-1 37 Lectures 6.5 hours Volkan Yurtseven More Detail Excel Pivot Tables – Crash Course Most Popular 18 Lectures 47 mins EdSolver Learning More Detail Learn MS Excel, Data Analysis, and Financial Analysis 50 Lectures 3 hours Tayana Grubisic More Detail Excel Analytics – Data Analysis with Pivot-Tables and Charts 47 Lectures 2.5 hours Blink Data More Detail Learn microsoft excel course for data analysis Zero to hero 50 Lectures 2 hours ADITYA More Detail Print Page Previous Next Advertisements ”;

Advanced Excel Charts Tutorial PDF Version Quick Guide Resources Job Search Discussion Excel charts are an efficient means to visualize the data to convey the results. In addition to the chart types that are available in Excel, some application charts are popular and widely used. In this tutorial, you will learn about these advanced charts and how you can create them in Excel. Audience This guide targets people who want to use graphs or charts in presentations and help people understand data quickly. Whether you want to make a comparison, show a relationship, or highlight a trend, these charts help your audience “see” what you are talking about. Among its many features, Microsoft Excel enables you to incorporate charts, providing a way to add visual appeal to your business reports. Prerequisites Before you proceed with this tutorial, we are assuming that you are already aware of the basics of Microsoft Excel charts. If you are not well aware of these concepts, then we will suggest you to go through our short tutorial on Excel charts. Print Page Previous Next Advertisements ”;

Advanced Excel – Waterfall Chart ”; Previous Next Waterfall chart is one of the most popular visualization tools used in small and large businesses, especially in Finance. Waterfall charts are ideal for showing how you have arrived at a net value such as net income, by breaking down the cumulative effect of positive and negative contributions. What is a Waterfall Chart? A Waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. A typical Waterfall chart is used to show how an initial value is increased and decreased by a series of intermediate values, leading to a final value. In a Waterfall chart, the columns are color coded so that you can quickly tell positive from negative numbers. The initial and the final value columns start on the horizontal axis, while the intermediate values are floating columns. Because of this look, Waterfall charts are also called Bridge charts, Flying Bricks charts or Cascade charts. Advantages of Waterfall Charts A Waterfall chart has the following advantages − Analytical purposes − Used especially for understanding or explaining, the gradual transition in the quantitative value of an entity, which is subjected to increment or decrement. Quantitative analysis − Used in quantitative analysis ranging from inventory analysis to performance analysis. Tracking contracts − Starting with the number of contracts at hand at the beginning of the year, taking into account − The new contracts that are added The contracts that got cancelled The contracts that are finished, and Finally ending with the number of contracts at hand at the end of the year. Tracking performance of company over a given number of years. In general, if you have an initial value, and changes (positive and negative) occur to that value over a period of time, then Waterfall chart can be used to depict the initial value, positive and negative changes in their order of occurrence and the final value. Preparation of Data You need to prepare the data from the given input data, so that it can be portrayed as a Waterfall chart. Consider the following data − Prepare the data for the Waterfall chart as follows − Ensure the column Net Cash Flow is to the left of the Months Column (This is because you will not include Net Cash Flow column while creating the chart). Add two columns − Increase and Decrease for positive and negative cash flows respectively. Add a column Start − the first column in the chart with the start value in the Net Cash Flow. Add a column End − the last column in the chart with the end value in the Net Cash Flow. Add a column Float − that supports the intermediate columns. Insert formulas to compute the values in these columns as given in the table below. In the Float column, insert a row in the beginning and at the end. Place an arbitrary value 50000. This is just to have some space to the left and right sides of the chart. The data will look as given in the following table − The data is ready to create a Waterfall chart. Creating a Waterfall Chart You can create a Waterfall chart customizing Stacked Column chart as follows − Step 1 − Select the cells C2:H18 (i.e. excluding the Net Cash Flow column). Step 2 − Insert Stacked Column chart. Step 3 − Right click on the Float series. Step 4 − Click Format Data Series in the dropdown list. Step 5 − Select No fill for FILL in the SERIES OPTIONS in the Format Data Series pane. Step 6 − Right click on the Negative series. Step 7 − Select Fill color as red. Step 8 − Right click on the Positive series. Step 9 − Select Fill color as green. Step 10 − Right click on the Start series. Step 11 − Select Fill color as gray. Step 12 − Right click on the End series. Step 13 − Select Fill color as gray. Step 14 − Right click on any of the series. Step 15 − Select Gap Width as 10% under SERIES OPTIONS in the Format Data Series pane. Step 16 − Give a name to the chart. Your Waterfall chart is ready. Print Page Previous Next Advertisements ”;

Advanced Excel – Gantt Chart ”; Previous Next Gantt charts are widely in use for project planning and tracking. A Gantt chart provides a graphical illustration of a schedule that helps to plan, coordinate, and track specific tasks in a project. There are software applications that provide Gantt chart as a means of planning work and tracking the same such as Microsoft Project. However, you can create a Gantt chart easily in Excel also. What is a Gantt Chart? A Gantt chart is a chart in which a series of horizontal lines shows the amount of work done in certain periods of time with relation to the amount of work planned for those periods. The horizontal lines depict tasks, task duration and task hierarchy. Henry L. Gantt, an American engineer and social scientist, developed gantt chart as a production control tool in 1917. In Excel, you can create a Gantt chart by customizing a Stacked Bar chart type with the Bars representing tasks. An Excel Gantt chart typically uses days as the unit of time along the horizontal axis. Advantages of Gantt Charts Gantt chart is frequently used in project management to manage project schedule. It provides visual timeline for starting and finishing specific tasks. It accommodates multiple tasks and timelines into a single chart. It is an easy way to understand visualization that shows the amount of work done, the remaining work, and schedule slippages, if any at any point of time. If the Gantt chart is shared at a common place, it limits the number of status meetings. Gantt chart promotes on-time deliveries, as the timeline is visible to everyone who is involved in the work. It promotes collaboration and team spirit with project completion on-time as a common goal. It provides a realistic view of the project progress and eliminates project end surprises. Preparation of Data Arrange your data in a table in the following way − Create three columns – Task, Start and Duration. In the Task column, give the names of the Tasks in the project. In the Start column, for each Task, place the number of days from the Start Date of the project. In the Duration column, for each Task, place the duration of the Task in days. Note − When the Tasks are in a hierarchy, Start of any Task – Taskg is Start of previous Task + it’s Duration. That is, Start of a Task Taskh is the End of the previous Task, Taskg if they are in a hierarchy, meaning that Taskh is dependent on Taskg. This is referred to as Task Dependency. Following is the data − Creating a Gantt Chart Step 1 − Select the data. Step 2 − Insert a Stacked Bar chart. Step 3 − Right click on a bar representing Start series. Step 4 − Click the Fill icon. Select No Fill from the dropdown list. Step 5 − Right click on the Vertical Axis (Categories Axis). Step 6 − Select Format Axis from the dropdown list. Step 7 − On the AXIS OPTIONS tab, in the Format Axis pane, check the box – Categories in reverse order. You will see that the Vertical Axis values are reversed. Moreover, the Horizontal Axis shifts to the top of the chart. Step 8 − Make the chart appealing with some formatting. In Chart Elements, deselect the following − Legend. Gridlines. Format the Horizontal Axis as follows − Adjust the range. Major Tick Marks at 5 day intervals. Minor Tick Marks at 1 day intervals. Format Data Series to make the Bars look impressive. Give a Chart Title. Your Gantt chart is ready. Print Page Previous Next Advertisements ”;