The post Make info-graphics with shape fill technique [Charting Tip] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>This is a quick, fun and elegant way to make info-graphics in Excel. Learn how to use shape fill technique for charts in MS Excel to create engaging output. You will learn how to make something like this:

The process for creating these charts is very simple. I made 8 minute video explaining the process for making info-graphic charts in Excel. Watch it below or read on for step-by-step tutorial.

Let’s say you have the data as shown above. To create the house styled chart, simply add 3 columns. We can keep a constant value for Base and Top series and calculate floors by =Value-Base-Top formula.

This is simple. Just select the three columns we just calculated and insert a stacked column chart. You will get this:

Now go ahead and import (or draw) shapes for top, floors and bottom part of the house.

Once you have three images / shapes, select each image, press CTRL+C to copy it and select the corresponding chart element and press CTRL+V to fill it up.

**You will get this:**

Whenever you apply shape fill to charts, Excel will automatically *stretch *the image. But we need to stack it. Select the chart series and go to format options (Ctrl+1).

Here, change the fill option from “Stretch” to “Stack and Scale with” and specify a number that suits your images. For the house example, we can use 100 (as each floor would be same height as the bottom or top floors).

That is it. Our chart is ready. Slap an interesting title and caption you are good to go.

Here are two more examples of this technique.

**Please click here to download sample workbook with info-graphic style charts in Excel**. You can examine all the steps, data, formulas and charts in the file. Feel free to customize or reuse the concept for your work.

If you like this tip, you will love these ideas too.

Do you like this technique? I love it, but in moderation. Such info-graphic style charts can engage your audience and present data creatively. That said, if you use wrong shapes or symbols, it can quickly become annoying.

**What about you? Do you like this idea? **Please share your feedback in comments.

The post Make info-graphics with shape fill technique [Charting Tip] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post How to predict cricket scores [Excel + Machine Learning] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Cricket world cup is on. Both my homes (India & New Zealand) have done well so far in the tournament and if things go OK in the last couple of matches, they should qualify for semi-finals. The games are happening in UK, which is 12 hours behind New Zealand. You know that means?

*Yes, lots of sleepless nights in Wellington.*

As I watch these games, I notice that every once in a while they show a “**score predictor**“. It will tell you what the final score could be based on the proceedings of the game so far.

So I thought, hmm, May be I should make one of those in Excel?

That is what I did. I created a machine learning model in Excel to predict cricket score. Sounds interesting? Read on.

*If you are thinking “the only cricket that keeps me up all night is the damned chirping one in my basement”, then don’t worry. *You need very little knowledge of cricket to understand the techniques. Once you know the ideas you can apply them to many other real life problems like predicting sales next year or student absences next term or electricity usage in the new plant.

Just a quick note if you are not familiar with cricket. In a typical one-day match, two sides compete. The game starts with one of teams batting first and scoring some runs in 50 overs. The next team then tries to beat that target set by first team in 50 overs.

Let’s start by defining the problem. We want to create a cricket score predictor that takes the inputs:

- Country playing – C
- Runs scored so far in the match – R
_{s} - Wickets remaining in the match – W
_{R} - Overs remaining – O
_{R}

Our predictor should tell us what could be the final score at end of 50 overs.

**For example,** we could ask, “Australia scored 52 runs in 10 overs losing 1 wicket. What would be the final score?” and our predictor can provide a guess – *say 305 runs*.

The final score of a team in a cricket match depends on many things, including:

- The playing team and their batsmen
- The opposition team and their bowlers, fielders
- The ground and pitch they play on
- The weather and historical weather
- Crowd attending the game and how much they are cheering
- What the umpires had for lunch
- etc.

If we try to incorporate every little thing that matters, we will never be able to construct our prediction model. So let’s ignore everything except those 4 parameters (C, Rs, Wr, Or) listed above.

Let’s say **Rp denotes our predicted Runs**. We can define Rp as

Rp = p(C, Rs, Wr, Or)

where

- C = Country
- Rs = Runs already scored
- Wr = Wickets Remaining
- Or = Overs Remaining
- p is a prediction function that does some magic to calculate Rp

**But we know that Rp = Rs + ****something**

This is because total runs at the end of 50 overs will be *something *more than Runs Scored at the time of prediction.

If we can find *something *our problem is solved. But how?

Run rate is ratio between runs scored and overs completed. So if India scores 342 runs in 50 overs, their run rate is **6.84**. As our prediction model is for 50 overs, if we know the Run Rate, we will know final score.

Let’s define few more variables.

- Os (Overs so far) = 50 – Or
- RRs (Run Rate so far) = Rs / (50-Or)
- RRr (Run Rate for remaining overs) –
*this should be predicted*

Given these variables, we can rewrite Rp (Runs predicted) as

- Rp (Runs Predicted) = Rs + Or *
**RRr**

So if we can build a model to predict RRr, we can calculate Predicted score.

We can argue that Run Rate in remaining overs will be a function of (country, run rate so far, overs remaining, wickets remaining)

RRr = f(C, RRs, Wr, Or)

We can further argue that each country has specific strengths and abilities when it comes to batting. So, if we define a set of functions, f1(), f2()…, fn() where fn is

RRr for country n = fn(RRs, Wr, Or)

We can then call the relevant function based on which country we are predicting the score for.

*Regression of course.* There are many sophisticated machine learning algorithms. But for something straight forward like Run Rate (remaining overs), we can create a simple multiple regression model.

Say RRr can be written as

RRr = m1*RRs + m2*Wr + m3*Or + *const*

Given a set of training data with RRs, Wr, Or and RRr, we can use LINEST() function in Excel to calculate {m1, m2, m3, const} that fits the sample data. Once we have the multipliers and constant value for each country, we can predict the score for any situation. Its that simple.

As with everything else in life, cricket matches too have significant variability. For that reason, rather than one regression model per country, why not create 10 of them per country and the average the prediction?

As management consultants say,

“When you are not sure what to say, just run a survey and tell them what they said.”

If this sounds like a bunch of bs, don’t worry. This is an actual machine learning technique known as **Ensemble Modeling**.

The idea of ensemble modeling is simple. We build multiple models from the training data and then combine the results of all models when making predictions.

See this picture to understand how a typical Ensemble Model works.

This way, we can create more variation in input scenarios and create a robust model.

For the sake of simplicity, let’s say we want to build 10 regression models for each country.

**How to aggregate the ensemble model results?**

We will end up with 10 predicted Run Rates (Remaining overs). We can simply average these 10 to come up with final prediction. You can also assign weights to the models and do a weighted average. Let’s stick with simple average.

Let’s get building then.

**Learn first, Predict next**.

That’s it. But if you want more text, here we go. Almost all machine learning models follow this pattern. They look at some data to construct the model. Once that model is ready, we then test it on a *different *data set to see how satisfactorily it performs. If the results are not up to scratch, we back to step one and fine tune the model.

The easiest of all these is a regression model. That is the same one we will be using too.

There are many fine websites for finding current score or recent match results. But in order to train our model, we need a collection of historical match data by each over. This is notoriously hard to get. Thankfully, there is cricksheet. They have historical one day match data at ball by ball level for 1,400 + matches in CSV format. (here is the downloads page)

** Note about data:** I noticed that cricsheet doesn’t have all matches data. For example I could not find any 2018 games in the data set I downloaded few days ago. It doesn’t really matter as we are using a large sample of data spanning several years.

As you can see, this data is at a too detailed level than what we need.

**So I used Power Query to combine 1,400 files**, reshape the data to over by over scores and then calculate total score, overs and wickets at every 5 over interval until end of the game. I then took only the recent 300 games as very old performances have little impact on current scoring patterns.

*Sorry for not explaining the Power Query or Excel formula steps. That would get too technical and this post will never end. *

Once reshaped, my data looks like this:

We can then derive additional columns,

- Last over of the game: =MAXIFS([Over], [Source.Name],[@[Source.Name]], [Country], [@Country])
**RRs –**Run Rate so far: =[@[Cum Runs]]/[@Over]**Or –**Overs Remaining: =[@[Max Over?]]-[@Over]**Wr –**Wickets Remaining: =10-[@[Cum Wickets]]- Score at end: =SUMIFS formula
**RRr**– Run Rate (remaining overs)

=([@[Score at end]]-[@[Cum Runs]]) / [@[Overs Remaining]]

Using 2,3,4 & 6 we can create our regression models.

But before we go there, let’s split the data in to training & test data sets. For this example, I choose the latest 50 games as test data set and everything older as training data. Instead of creating two separate tables, I just added a column at the end to look at [Match ID] to tell me whether something is test or training.

**Also, we do not need to use last over data for training.** At the end of game there is nothing left to predict, so there is no point of using last over data when training the model.

I have added a data point ID as column to this table so I can uniquely identify all data points when sampling training data. It is [Data point number]

Don’t freak out. We are still on topic. Bagging is the *technical term * for the concept of randomly sampling data, building models and then aggregating (ie bagging) at the end.

Bootstrapping refers to random sampling of data.

Our bootstrap approach is rather simple and naive.

- For each country, we want 10 bags – 10 data sets
- For each data set, we want a random sample of 50 data points
- We then create a multiple regression model to fit

RRr = m1*RRs + m2*Wr + m3*Or +**const** - We average all 10 model results when predicting outcomes.

**A note on const: ** When I was building my cricket score prediction models, I realized that setting const=0 gave me a better R2 (ie the model fits well with training data). So I set the 3rd parameter of LINEST() to FALSE (ie no need for *const*). You may want to keep it on for other types of models.

In my training data from cricsheet, we have 16 countries. That means we need 16*10*50 = 8,000 data points to construct the models.

Using a bunch of RANDBETWEEN, INDEX+MATCH and COUNTIFS, I was able to construct this grid.

Once data grid is ready, we can create a bunch of LINEST() formulas to tell us the multipliers (m1, m2, m3) for each model. This can be done 160 times (each of the 16 countries need 10 models). But I am very lazy. So I used INDEX() formula to fetch arrays of 50 cells so that LINEST results can be tabulated nicely. This is how our multiple regression model looks:

As you can see, our model has very high *R ^{2}* values. This is promising.

*While high R ^{2} values are good, *you should not trust the model blindly. You should also check if the relationship between output (Run Rate in remaining overs) and inputs (RRs, Or, Wr) is chance. This can be done by looking at F statistic and F distribution probability. I have not bothered with this step for all of the data, but I did check for few samples to see if the F probability is low (low means relationship is not random).

Learn more about F statistic and how to interpret the results.

As you can see, each model predicts Run Rate (in remaining overs). But we need to predict the score. Given the inputs:

- C = Country
- Rs = Runs already scored
- Wr = Wickets lost so far
- Or = Overs Remaining

We can calculate predicted Runs (Rp) as

- RRr =
of all 10 country specific predictions (RRs, Wr, Or)*average* - Rp = Rs + RRr * Or

In simple words, our final prediction is Runs already scored + average of 10 predicted run rates *times *remaining overs.

Now that we have our shiny ensemble models, let’s go test them. I have extracted score data from last 50 games by innings. I then filtered away any games with less than 50 overs played (canceled due to rain, chased before the last ball etc.)

This is what we have.

For prediction, we also need to know what were the runs scored and how many wickets they had in hand at certain over. I started by creating a scrollbar to select the over (any multiple of 5 between 5 and 40). Then we fetch the relevant inputs from test data and run the model against them to calculate predicted score. I then compared this against actual score to see what kind of error and accuracy our model is getting.

This involved using some crazy, but fun MMULT and INDEX functions (ofcourse, TRANSPOSE too). It is 2:19 AM as I am typing this. That means, Unfortunately, it is too late in the game to explain the formula logic here, so I will leave it to your imagination.

Here is how our model compares with actual results at 15 overs.

And this is how it works after 25, 35 and 40 overs. As you can see, accuracy improves the later in game you ask for prediction.

Right now, as I am typing this, India is playing against Bangladesh. India have score 314 in 50 overs. I wanted to see how our model predicts the score at various points in game. As you can see, it gets a little optimistic (as India didn’t loose a wicket until 30th over) but the prediction gets closer since 35th over.

If you want to examine the calculations, predict your own scores or just want to see how its all done, **here is the file**.

In the download:

- You will find two models, not one. This is because I built two regression models to see which will give better prediction. The one presented in this article gives better results.
- You will find a simple score predictor too. Enter inputs (Country, runs so far, wickets so far and overs) and it will tell you what the predicted score is.
- All calculations and data.

Feel free to mash up the data to create your own prediction tool.

I made a short (oh well, 37 minutes long) video explaining the process, machine learning concepts and Excel implementation. Watch it below or see it on Chandoo.org youtube channel.

This is an interesting topic and I am sure you want to know more. See below references to understand the concepts better.

**Ensemble modeling:**- Ensemble modeling & learning
- Ensemble modeling basics, terminology and sample Python code
- Understanding bootstarpping technique
**LINEST() & Multiple Regression:**- LINEST Function – Microsoft Help
- LINEST example and explanation from MIT
- LINEST and other forecasting functions in Excel
**F statistic:**- Understanding F statistic
**Excel features:**- Power Query for combining files
- INDEX function
**Also see:**- Time series forecasting in Excel

I had so much fun creating this. I did have a few false starts and made models with wrong equations, but eventually came up with something that provides sensible prediction. I am happy with the way it turned out. Although I couldn’t explain every little thing about the model in this post, I hope you are able to fill those gaps in.

Do you like this prediction model in Excel? Are you surprised to see a complex machine learning algorithm implemented in good ol’ spreadsheet? *Share your thoughts in the comments.*

The post How to predict cricket scores [Excel + Machine Learning] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Should finance people learn Power BI? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>I recently went to Sydney to conduct some training programs on Advanced Excel and Power BI. While I was there, I met my good friend Danielle, who runs Plum Solutions, a financial modeling consultancy & training company. We got talking about various things and the topic eventually turned to “finance people and Power BI”. **We debated whether finance professionals (analysts, reporting people, financial modelers and controllers) should bother learning Power BI or stick to Excel.**

As this is an interesting topic, I am sharing my thoughts in this post along with a video of our debate at the end of it. Feel free to chip in and share your views to.

Let’s examine both sides of the argument. We start with why you need to learn Power BI and then look at why you don’t want to.

**Power BI is visual & easy:**You can quickly take a P&L or complex model outputs and turn them in to a bunch of interactive visuals with Power BI. We all know how much decision makers and customers love visuals. So this is a huge win.

**Power BI makes it easy to share stuff:**We all know about the 70Mb workbooks that calculate NPV of a project or compare few different scenarios. Unfortunately that hunk of a file can’t be emailed CFO as she is globe-trotting and can’t run array formulas on her iPhone. On the other-hand Power BI files can be shared easily. You just publish and send the link or notification to the people. They can check it out on phone / tablet / computer and interact too.**Consolidate data with Power Query:**Ok, this is not just Power BI thing. With Power Query, we can consolidate files, stitch together tables and merge different sets of data. This means, no more manual tasks or laborious lookups.

**Visual scenarios with what-if parameters:**You can use what-if parameters, slicers in Power BI to make interactive scenarios. This is so much better than using either scenario manager or changing cell values in Excel.

**Work on larger sets of data easily:**Excel has physical limit of 1mn rows per spreadsheet. So if you are forecasting operations of a large company, chances are you have more than a million rows and hit the limit in Excel. Power BI has no such limits, so you can happily work on larger data-sets.**Power BI is regularly improving:**Almost every month Microsoft releases a new version of Power BI. This is good for people who are building things as you have new capabilities to rely on and improve user experiences.

**Power BI is not Excel:**This is the big one. You can’t create everything you do in Excel with Power BI. Many things that finance people do in Excel like making statutory statements (balance sheets, P&L etc.) or creating scenarios or running monte-carlo simulations on forecasts are ridiculously hard to reproduce in Power BI.*Power BI good for visuals, but all the heavy lifting must be done in Excel.*

**Power BI doesn’t yet have many basic finance functions:**Simple things like calculating NPV or Future value of a cash flow become a laborious mathematical exercise in Power BI. This is because the calculation engine of Power BI – Power Pivot, yet doesn’t have many finance functions.**Power BI is very technical:**Most finance people would learn how to work on finance & accounts in Excel. So Excel becomes a second language for them. But Power BI is not an extension of Excel. It is a deeply technical and complex software. So to get most of out it, finance people need to pick up technical skills like DAX, M and data modeling.

**Power BI is regularly changing:**Let’s admit it. Nobody like change. As Power BI rolls out new version every month, you will be forced to stay on top to feel at home. New features come, screens change, buttons move around and options add every month. Knowing all this and keeping up can be hard.

I think Power BI is a valuable skill to acquire nevertheless. The reasons:

**Reuse the skills in Excel:**Core components of Power BI – Power Query & Power Pivot can be used in Excel too.**Learning new things is fun:**Just as squats make your legs fit, learning new things keeps your brain fit.**Power BI can open new doors for you:**With each new skill you acquire, you will find new opportunities. It can help you find that exciting job or start a business.**Power BI is free:**Power BI Desktop software is free to download and install. There is heaps of free tutorials, examples and videos online (including on chandoo.org) to help you learn it. Even Excel is not free. So why not give it a try.**Data skills are hot:**Skills like how databases work, how to analyze data and how to tell stories thru visuals are very hot now. Power BI (and Excel too) makes it very easy to practice these skills.

I recorded a video with Danielle where we debated pros & cons of Power BI for finance people. Watch it below or see it on my YouTube channel.

Do you work in finance? What are your thoughts about Power BI? Share them below using comments. I want to hear what you think.

If the jargon of Power BI world is spinning your head, start with this simple explanation of what is Power BI.

For more examples and tutorials, check out the Power BI category on Chandoo.org or see this epic introduction video.

The post Should finance people learn Power BI? appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>To make matters interesting each sheet has a different format.

What now?

Of course Power Query to the rescue.

The post Combine multiple Excel files using Power Query [Full example + download] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>To make matters interesting each sheet has a different format.

What now?

Of course Power Query to the rescue.

This is an advanced example of Power Query. If you are a beginner, start with these pages.

Imagine you work in Finance. Your job involves paying employees for their business travel expenses. Every time someone goes on a business trip, they submit a trip expense report. **This is an Excel template with two tabs.**

- Travel details tab: for gather personal and travel details
- Expense details tab: for itemized expense details

As you have a lot of employees, you don’t want to manually scan the files and combine the data. *Here is a sample of how these files look.*

**You want to combine all the expense files** in to one big, consolidated & refreshable travel expense workbook.

Some of you may already know **Power Query’s “Get data from Folder” feature**. This helps us easily get & combine multiple excel files in a folder. Unfortunately, this alone **will not be helpful** for us as our file has two different tabs and we need to combine them separately

Here is the process we need to follow.

Start by placing all the expense reports in to one folder. This can be a folder on your computer or on a network / shared drive.

Now go to “Get Data > From File > Folder”

Point to the folder path and Power Query will show all the files in that folder.

Once satisfied with the list of files (don’t worry if you need to exclude some files, you can do that while editing the query by applying filters), click on “Combine & Edit”.

Now you will get another screen asking you choose which tabs / tables you want to bring. As we have two sets of consolidations, let’s start with the first one – travel details tab. Select that and proceed.

At this point, Power Query will create a folder called “Transform sample” and places a few things in it. PQ will also create a query for all the merged data. This is how your Power Query window could look.

As you can see, the default combined query data can be useless for our situation. So let’s proceed by editing “Transform sample file from reports” query.

In this sample query, you can make any changes and PQ will apply them to all the files in the folder before combining them to one gain data set.

Our travel details sample needs to become one row table so that we can effectively merge multiple files. To do so, follow these steps:

- Remove blank / heading rows on the top.
- Remove any nulls or unnecessary rows from column 1
- Transpose the table
- Promote first row to headers

This is how the output would look after the process.

Now that we have edited transform sample, time to go back to the “reports” query to see the output. If you are happy with it, rename the query and load it in to Excel (or Power BI).

**Combined travel details**

The process is same for expense details consolidation. Start by creating a fresh “from folder” query. As expense details are in a table, there is no need to do any additional changes to the transform sample. Simply combine everything from “expenses” tables and you are done.

**Combined expense details**

Power Query can be tricky to explain with blog posts alone. That is why I made few sample files and consolidated workbook.** Click here to download everything**.

Try to merge the files in “reports” folder using your own logic / transformation steps. Share your story / tips in the comments.

There are many reasons why Power Query may show an error when connecting to a folder. Here is a check list to help you.

**Make sure the folder path is valid and accessible**. If you created the query on one computer and try to refresh it from another, chances are it won’t work. Use shared network drives or change path in Power Query steps before refreshing.**Files are loaded, but merged query errors.**This can happen if you edited the transform sample. Usually Power Query adds “Changed type” steps automatically after you do something. These changed type steps refer to column names in the query and change data types. If you edit the transform sample and alter the column structure of table, then the query will fail. The solution? Simple, delete all the*automatically*added changed type steps.**Some files should not be loaded, but they load and mess up the results**. Before making any transformations, set up filters based on file type or names. This way you can prevent loading unnecessary files.

I do this all the time. My recent win was to merge 24 PDF credit card statements (2 types of cards over last 12 months) to one big table of data so that I can see trends and find out where I spend most.

What is your experience with combine multiple Excel files / folder query feature? What are some of your favorite tricks with this? **Please post them in the comments section.**

*This article is inspired from a comment by Sourav.*

The post Combine multiple Excel files using Power Query [Full example + download] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>You can use trace precedents in Excel to do this. Read this tip to learn how it works.

The post How to trace precedents in Excel formulas? [tip+music from Prague] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>That’s good morning in Czech. Hello from Prague. I am here as part of our Euro trip. Next stop is Munich before we go home to Wellington. So far, this trip has been beautiful and fun.

**I want to share a very useful and almost ****secret ****Excel tip with you all**. Imagine you are looking at a big, complex workbook with lots of calculations. You want to understand where everything is pointing to and how the workbook is set up.

Don’t worry, there is a feature in Excel to do this. It’s tracing precedents / dependents. If you select a cell and click on the trace precedents (or trace dependents) button in Formula ribbon, then Excel will draw arrows to all connected cells. This helps you visualize how everything is linked up.

Now comes the secret part. You can **double click on the lines** to go to the linked cells / ranges / tables!!!

I explain all of this in a short video. It has live music from Prague streets too. Czech it out

See complete overview of Excel’s auditing features or learn how to use Go to special to your advantage. Select & press F9 to calculate portions of your formulas to see and debug results.

That is all for now.

The post How to trace precedents in Excel formulas? [tip+music from Prague] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>The post Circular Arc – Doughnut Charts appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>This post will examine how to make it

**Alert:** It isn’t as straight forward as you may first think!

A couple of users responded with a Doughnut Chart

Which at first glance looks quite similar.

But the original author wanted round ends on the ends of the Doughnut segment. He also wanted a smooth chart.

A quick scan through the properties of a Doughnut Chart reveals there is no optionality to control the ends of the Doughnuts Segments. An alternative approach was required.

Before starting, if you want to you can follow along using a sample file with the worked examples shown below: Download Here

The solution I posed was to use an X-Y Scatter chart for the line segments and apply a thick Line style.

The part of this approach that makes it work is that Line Styles have a property for the Lines End including an option for a round end.

The solution chart above consists of 2 lines

The first is the Background (Grey) line, which is a complete circle

The second line is the green line, which is a segment of the circle equal to in this case 45% of a circle or 162 Degrees (0.45 x 360). It is in front of the Grey line.

To apply this technique I used a number of Named Formula, and based the chart on these named formula:

**First for the Background Grey chart segment**

To define the Grey segment I applied 3 Named Formula:

c1_Rad
| =RADIANS(-(ROW(OFFSET(Sheet1!$A$1,,,360+1,1))-91)) |

_x1
| =COS(c1_Rad) |

_y1
| =SIN(c1_Rad) |

The Grey circle is defined by an Array of Radians of each degree between 0 and 360 of a circle.

C1_Rad =RADIANS(-(ROW(OFFSET(Sheet1!$A$1,,,360+1,1))-91))

This works by using the Excel Row() and Offset() function to generate an array of Degrees from 0 to 360

The formula **ROW(OFFSET(Sheet1!$A$1,,,360+1,1))**

Will return **={1;2;3;4;5;6; …. ;358;359;360;361}**

Note that we have taken the array 1 degree past 360 because the Row’s lowest value is Row 1, not row 0.

We then subtract 91 degrees from this to allow the Chart to start at the top of the circle.

The adjusted formula **ROW(OFFSET(Sheet1!$A$1,,,360+1,1))-91**

Returns: **={-90;-89;-88;-87; … ;268;269;270}**

Finally the **–** in front of the array changes the direction of the circle from Anticlockwise to clockwise.

Returns: **={90;89;88;87; … ;-268;-269;-270}**

The Radians() function is used to convert the array of Degrees into an array of Radians

Returns: **={1.57;1.55;1.53; … ;-1.22;-1.23;-1.25} **

The Radians above were rounded to 2 decimals places for display on this post, but Excel internally is using the full 15 decimal place precision.

We can now use this array of Radians to draw the background circle

To do this setup 2 new Named Formula

**_x1**: =COS(c1_Rad)

**_y1**: =SIN(c1_Rad)

Each of these will return an array of the X and Y values corresponding to each of the Radians from the previous** c1_Rad **array. The X and Y values will vary between -1 and 1. You may need these for Chart Scaling later.

If you want a circle of different radius simply multiply the x and y formulas like **_x1**: =COS(c1_Rad)*5 for a radius of 5 and the same for the **_y1** named formula

To plot these we add a X-Y Scatter Chart.

Select a single cell. Then goto the **Insert**, **Chart**, **Scatter Chart** menu and select a **Scatter Chart with Smooth lines**. This will give you a blank chart.

With the Chart Selected, Right click on the chart area and choose **Select Data… **

Add a Series using the **Add **button. Use the Worksheet Name **Sheet1** and Named Formula **_x1 **& **_y1** for the X and Y values

You can leave the Series Name blank or enter a value like “Background Circle”.

Note that you must enter the Sheet Name including the **!** preceding the Named Formula name. Once you have accepted the inputs, if you return to the Edit Series dialog, notice that Excel now displays the Workbooks name instead of the Worksheets name. That’s quite ok.

You will now have a chart which looks like:

Finally Right click on the first series and select **Format Data Series.**

Set the Line Color to a Light Grey and set the Line Width to 12 . Check that Markers are set to None

**Next the Foreground Green chart segment**

To draw the front arc of the circle we add a few more Named Formula

_pct
| =Sheet1!$C$6 |

c2_Rad | =RADIANS(-(ROW(OFFSET(Sheet1!$A$1,,,_pct*360+1,1))-91)) |

_x2 | =COS(c2_Rad) |

_y2 | =SIN(c2_Rad) |

**_pct** stores the value of the percentage of the circle directly from the reference cell on the worksheet eg: 45%

To draw an arc we only need to factor the 360 Degrees for a full circle back to the percentage required for the arc: ie: from 0 to 45% x 360 degrees = 162 Degrees. Hence drawing an Arc from 0 degrees to 162 Degrees.

To do this we use the same formula as before except that we set the range to the 45% of 360 degrees using the Named Formula:

**C2_Rad**: =RADIANS(-(ROW(OFFSET(Sheet1!$A$1,,,**_pct***360+1,1))-91))

Add another series to the chart using.

With the Chart Selected, Right click on the chart area and choose **Select Data…**

**X values**: =Sheet1!_x2

**Y values**: =Sheet1!_y2

Next select the chart and ensure that the 45% circle is in front of the full circle

Select the Chart’s 2nd series and change the line width and line color to suit the impact you want.

Finally select the 45% line

Goto the Lines properties and set the **Cap type **to **Round**

**Add the Measurement**

With the Chart selected, goto the **Insert**, **Text Box** dialog and select a text box style and insert it.

With the text box selected, goto the **Formula Bar **and enter the Formula **=_pct **and press **Enter **or click the **Tick **icon to accept.

Finally with the text box selected, Change the Font Size to suit eg: 64 and Format the Text using an appropriate style from the **Drawing Tools**, **Format ** Menu

Ensure the Text box is wide enough to display up to 100% include the percentage sign

The Final Chart

and with another value…

**Other line type endings **

Experiment with other Line Ends and see what you can make?

**and Line Styles and Thicknesses?**

By careful use of chart series you can add multiple measurements to the same chart and use a combination of display properties to enhance your chart

In conclusion I have demonstrated a successful solution to Jhouz’s original post and then extended it a bit further.

The Author acknowledges that there is limited use for doughnut charts and only recommends them in limited circumstances.

I hope these enhancements allow you to better use and emphasise your data in your situation as well as add another Excel technique to your arsenal.

The post Circular Arc – Doughnut Charts appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>Tip: Same rules apply for bar charts too.

The post 5 simple rules for making awesome column charts appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>*Tip: Same rules apply for bar charts too.*

The first rule is simple. Always start your column charts at zero. When looking at column (or bar) charts, our mind measures height of each column and compares. So, if a column starts at some arbitrary point instead of zero, it can mess with our perception of how each column compares with other. Don’t believe me. See yourself.

Related: What is the most embarrassing charting mistake you made?

Sort your columns in a meaningful order. For example, sort them by descending order (of column heights), alphabetical order or chronological order. This will make reading the chart easy.

Give your chart a meaningful, clear title. Few examples of good and bad titles shown below.

Rock star tip: Using smart titles & legends in your charts

For most charts you can use data labels instead of axis & grid-lines. This will keep the chart clean.

If you choose to go with Axis and gridlines, then make sure they follow below guidelines.

- Axis label text should be relatively small & dull.
- Grid lines & axis line should be dull too.
- Do not display too many or too little major units on axis. You can change major unit size by selecting axis and pressing CTRL+1 (or axis options pane in Excel 2013).

Make sure the formatting (colors, fonts, special effects, backgrounds etc.) of your chart are really subtle and meaningful. If you use too many colors, you end up with a pig. People will then focus on all these colors, fonts instead of actual data.

**Few ways to add wow factor to your chart without messing it up:**

- Highlight a particular column (for example max value, min value etc.) using different series technique.
- Use descriptive titles, clever data labels to show more information.
- Use drawing symbols or shapes to enhance the message of chart.
- Make your chart interactive to give users control.
- Add Emojis even

So there you go. Follow these rules and your column charts will stand tall.

While above rules capture the gist of making good looking column charts, there is more to learn and follow. So go ahead and share your rules and tips using comments. Teach us how you make stunning column charts (or bar charts). **Post your comments below.**

If your job involves analyzing & charting data, then check out below tips to learn more.

- 5 more rules to make better charts
- How to select right type of chart for any situation?
- 14 skills you need as a chart maker
- Never use simple numbers in your dashboards
- More charting principles & examples.

The post 5 simple rules for making awesome column charts appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

]]>