Forecasting using Excel Functions
“Todays forecast will be Hot and Humid with a Chance of Snow?”
(Even the experts with big computers get it wrong)
In the previous post we looked at Manual Forecasting techniques and how Excel can be used to assist. In this post we will look at how we can use Excel built in functions to aid us in forecasting.
This post is going to delve slowly at first and then deeper into some of Excels Statistical Functions. Readers are encouraged to follow along at your own pace and use the examples in the Examples Workbook attached.
All charts, tables and diagrams in this post with the associated Excel formulas are included in the Example workbook.
In this post I will be using the following nomenclature
^ means raise to the power eg: 10^2 = Power(10,2) = 100
. means multiply eg 10.2.M.X = 10.2 * m * x
Why do we need to use Excel Functions?
In the first post we looked at some simple data with only a few points and a trend that was very fairly obvious or was it.
A number of other linear trends could have equally been used and all look about right.
However in real life data is rarely this simple.
Fortunately Excel has a Number of Functions and Tools that allow us to look for trends and use the data natively for forecasting purposes.
There are a number of standard types of trends which can be classified as:
Linear – Approximating a straight line
Polynomial – Approximating a Polynomial function to a power
Power – Approximating a power function
Logarithmic – Approximating a Logarithmic line
Exponential – Approximating an Exponential line
Excel supports the use of these trend types in a number of ways.
Excel Functions and Tools
Excel has a number of Worksheet functions specifically designed to assist us with analysing various trends.
They are categorised by type below
Excel Functions for Linear Trends
- Slope
- Intercept
- Linest
- Trend
- Forecast
Excel Functions for Exponential Trends
- Logest
- Growth
Other Excel Tools
- Excel Chart + Trendline
USING EXCELS WORKSHEET FUNCTIONS
Linear Estimates
In the first Post we looked at using a linear equation in the form Y=mX + c to express our estimated line of best fit which we manual estimated was linear.
Excel has 2 functions which we can use to calculate the actual slope (m) and intercept (c) for the above equation.
Slope
The Slope function returns the slope or gradient of the linear regression line through data points in Known_Y’s and Known_X’s.
eg: =SLOPE(Known Y values, Known X values)
Intercept
The Intercept function calculates the point at which a linear regression line will intersect the Y-axis by using existing X-values and Y-values.
eg: = INTERCEPT (Known Y values, Known X values)
Use
To use the above 2 equations we simply enter 2 equations in cells
m = SLOPE(C47:C51, B47:B51) = 1.298
c = INTERCEPT(C47:C51, B47:B51) = 0.140
We can now use our revised linear equation to plot a line of best fit
Y = m.X + c
Y = 1.298.X + 0.140
So for
X = 5, Y= 6.63 &
X = 30, Y = 39.07
Which we can plot as a new series on our chart
Linest
The Linest function can be used to calculate the Slope and Intercept parameters for a linear function
Linest is an array formula which must be entered as an array formula to return all the values that it can return.
Eg: = LINEST(Known Y Values, Known X Values,Const , Stats)
=LINEST(C47:C51,B47:B51,TRUE,FALSE) will return the Slope (m) component of the equation
Const = True b parameter is calculated
False b is set to 0 (zero)
Stats = True Return additional regression statistics
False Return the m co-efficient and const b
To return both components you must enter the same formula in adjacent cells in the same row
and the equation must be entered as an array formula
Eg: = LINEST(C47:C51, B47:B51, TRUE, FALSE) Ctrl Shift Enter
| Slope (m) | Intercept (c) | |
| Linest | 1.298 | 0.140 |
Alternatively the values can be retrieved from the Linest array function using the Index function
Gradient m =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),1)
Intercept c =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),2)
The use of the Index function negates the requirement to use an Array Entered formula.
Stats
Linest can also return a number of statistics when Stats parameter is set to True
Eg: =LINEST(C47:C51, B47:B51, TRUE,TRUE) Ctrl Shift Enter
This must be entered as an array formula of 2 columns by 5 rows
The formula can also be entered as a normal equation also using the Index function to extract the array values
Eg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), Row ,Column)
If you want to know the r2 value (discussed later) it is in the 3rd row, 1st column.
Eg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), 3 , 1)
The above table shows the statistic and the value for our example above using both array entered and Index formulas
The r2 parameter highlighted will be discussed later.
Trend
The Trend function is used to calculate a straight line best fit line based on a number of known X & Y values.
Values of Y can be calculated for values of X inside or outside the know range of X values and so Trend can be used to interpolate or extrapolate data.
eg: = INTERCEPT (known Y values, known X values, New X Value, Const)
Const = True; Calculate the Intercept value
= False; Set the Intercept value c = 0
If for example you are using this to model your power cost.
If you have a fixed monthly cost plus a cost per kW, you would set Const to True
If you have no fixed monthly cost and are only charged per kW set Const to false
eg: =TREND($C$101:$C$105,$B$101:$B$105,B106,TRUE)
Forecast
The Forecast function is used to calculate a straight line best fit line based on a number of known X & Y values.
Values of Y can be calculated for values of X inside or outside the know range of X values and so Trend can be used to interpolate or extrapolate data.
eg: = FORECAST (New X Value, Known Y values, Known X values)
= FORECAST(B129, $C$124:$C$128, $B$124:$B$128)
Non-Linear Estimates
So far our examination of trends has revolved around the use of linear estimates and the Excel functions that support that.
But as we saw above there are lots of cases where non-linear estimates are required.
This section will deal with the following estimate types.
- Polynomial – Approximating a Polynomial function, a.x^n + b.x^(n-1) + c.x^(n-2) + … + m = 0
- Power – Approximating a Power function, y = a.x^b
- Logarithmic – Approximating a Logarithmic line, y = b.ln(x) + a
- Exponential – Approximating an Exponential line, y = b.m^x
Luckily Excel has a number of function and some tools to assist us here as well.
Exponential Functions
Exponential functions are based around the formula y = b.m^x
Excel has one function specific to growth estimates and that is the Logest function.
As with Linest, Logest is an array function.
eg: =LOGEST(Known Y’s, Known X’s, Const, Stats)
=LOGEST(C6:C13, B6:B13, true, false) Ctrl Shift Enter
Const = True or omitted b parameter is calculated
False b is set to 1
Stats = True Return additional regression statistics in an array
False Return the m co-efficient and const b
Alternatively the values can be retrieved from the Logest array function using the Index function
B = INDEX( LOGEST( C6:C13, B6:B13, True, False), 1)
X = INDEX( LOGEST(C6:C13, B6:B13, True, False), 2)
The use of the Index function negates the requirement to use an Array Entered formula.1
However Logest, is a tricky function as it actually just passes values to the Linest function!
So we can actually use the Linest function for doing nearly all of our Exponential, Logarithmic and Power function trends.
But you ask “Doesn’t Linest give us the parameters for a straight line?”
Absolutely.
To use Linest to analyse an Exponential function we need to unwrap it so to speak and that is done by taking the Log of the Y values prior to putting them into the Linest equation, like this:
Form: = LINEST( LN(Known Y Values), Known X Values)
eg: = LINEST( LN(C32:C39), B32:B39) Which is an array formula
or = INDEX( LINEST( LN(C32:C39), B32:B39), 1) as a normal formula
Now the tricky part is that the m component or array parameter 2 must now be converted back to an exponential so we can use exp(m component) or =EXP( INDEX( LINEST( LN(C32:C39), B32:B39),2))
This is difficult to explain but is shown in a worked example on the Exponential Functions section of the Non-linear Functions page of the example workbook attached.
Growth
The Growth function can be used to calculate an exponential curve that best fits your data based on a number of known X & Y values.
Form: = LINEST(Known Y Values, Known X Values, New X Values)
eg: = GROWTH($C$32:$C$39, $B$32:$B$39, B40) as a normal formula
This is also shown in a worked example on the Exponential Functions section of the Non-linear Functions page of the example workbook attached.
Logarithmic Functions
Logarithmic functions are based around the formula y = b.LN(x)+a
Excel doesn’t have a specific function dealing with Logarithmic functions, however we can use the Linest function as previously described by first converting the data from a Logarithmic to Straight line and this is done by talking the LN of the X values.
Form: = LINEST( Known Y Values, LN(Known X Values))
eg: = LINEST( LN(C32:C39), B32:B39) as an array formula
or = INDEX( LINEST( LN(C32:C39), B32:B39), 1) as a normal formula
This is shown in a worked example on the Logarithmic Functions section of the Non-linear Functions page of the example workbook attached.
Power Functions
Power functions are based around the formula y = a.x^b
Excel doesn’t have a specific function dealing with Power functions, however we can again use the Linest function as previously described by first converting the data from a Power function to Straight line and this is done by talking the LN of the X and Y values.
Form: =LINEST( LN(Known Y Values), LN(Known X Values))
eg: =LINEST( LN(C58:C65), LN(B58:B65)) as an array formula
or =INDEX( LINEST( LN(C58:C65), LN(B58:B65)), 1) as a normal formula
The above equations return Parameter 1 as b and Parameter 2 as LN(a)
LN(a) must be converted back to Parameter a by taking the Exp(a)
This is shown in a worked example on the Power Functions section of the Non-linear Functions page of the example workbook attached.
Polynomial Functions
Polynomial functions are based around the formula y = a.x^n + b.x^(n-1) + c.x^(n-2) + … + m
Which typically looks like y = a.x^5 + b.x^4 + c.x^3 + d.x^2 + e.x +m
And if any of the parameters a to m are zero that part of the function will be zero and not shown.
Excel does have a specific function dealing with Polynomial functions, and you guessed it, it is the Linest function. The Linest function must be told that it is dealing with a polynomial function and this is done by adding another parameter to it’s input. The extra parameter is added by raising the know X values to the power of an array of number 1..n, where n is the power of the polynomial you want to use.
Form: = LINEST( Known Y Values, Known X Values^{1,2,3,..n})
eg: for a polynomial of power 3
= LINEST(C84:C94, B84:B94^{1,2,3}) as an array formula
or =INDEX( LINEST(C84:C94, B84:B94^{1,2,3}), 1) as a normal formula
The above equations return Parameter 1 as a, Parameter 2 as b, Parameter 3 as c if a power 3 polynomial is used.
This is shown in a worked example on the Power Functions section of the Non-linear Functions page of the example workbook attached.
Multiple Variable Linear Regressions
The Linest function is able to be used to determine the regressions of multiple input variables (X1, X2, … Xn) that may contribute to a single output variable (Y).
This is best demonstrated with a simple example:
Hui’s Fruit Shop
Say we have a Fruit Shop and we only sell Apples & Oranges and we know how many Staff and what our Overhead Costs were and how much Profit we have made each year for the past decade.
This could be tabulated below:
We can use Linest to work out a regression for this model. That is what is the relationship between the output and all the inputs.
The format of this will be
Form: = LINEST(Known Y values, Known X Values, TRUE, TRUE) as an Array Formula
eg: = LINEST(E122:E132, A122:D132, TRUE, TRUE)
Note that the Known X Values of this example is a 4 column wide area representing the 4 variables.
This must be array entered in an area Xn + 1 columns wide and 5 rows deep, in our case a 5 column x 5 row area.
Note that the equation for then profit is made up of the array values from the first row of the answer array in reverse order
Y = 18.84.X1 + 27.98.X2 + 3851.79.X3 -0.26.X4 -15406.84
And that the parameters are in highest X4 to lowest X1 order followed by b at the end
You can also see the other parameters of the array of which the most important is the r2 factor which in this example is 0.90 indicating that there is a good fit between the Inputs and the Profit. Hence we could be relatively comfortable using our profit equation for the estimate of future profits.
Measuring the accuracy of the Estimation.
In the linear Linest section at the start and in the previous example we briefly mentioned a measure called the r2 parameter and said that because it had a value of 0.90 we would be comfortable using our estimation parameters to estimate future profits.
r2 is a measure of the error between the data points and the estimated values.
Its values vary between 0 = no relationship and 1 = a perfect relationship.
For example here are 3 charts based on the equation of Y = 3 X + 5
The equations of the lines of best fit and the r2 values are shown on each chart.
You can see that the data of Chart Y1 has a very close fit to the equation both visually and through a very high r2 value of 0.9962, where as at Y3 there is a very loose relationship between the data and the estimate which is shown visually as well as a low r2 value of 0.2552.
The derivation and use of this is beyond this post and I would refer you to the Excel Help of the Linest function, where it is discussed or Wikipedia.
How Does All This Work ?
The Excel Linest, Logest and Growth Functions all use a technique called “Least Squares Approximation”.
This is an iterative process which minimises the sum of the square of the distance from the estimated line to the actual data for all known data points. Once this is minimised the parameters which define the estimated line are returned to the user.
The scope of how Least Squares works is beyond the scope of this post, but if you are interested have a read at Wikipedia.
There are a number of other estimation techniques available which excel doesn’t support.
One should never assume that “just because Excel gave me the answer – it is correct” and this applies to the use of statistics more than any other area in maths or Excel usage.
Limitations:
The above techniques need to be used with a degree of caution.
Often a trend will exactly mathematically fit the data but in reality you wouldn’t use the equations.
In the picture below (courtesy of Wikipedia) 10 data points are exactly matched by a Polynomial function , whereas the linear estimate misses every point.
Which estimate would you choose to use? The linear function I hope.
This is discussed in more detail at Wikipedia.
Disclaimer
It should be noted that just because Excel returns an estimated line of best fit to your data, it doesn’t mean that your data actually follows that trend, it just may be coincidental and that user discretion is advised in all cases, refer Limitations above.
There are a number of other estimation techniques available and users interested should discuss these if required with a person expert in their data distribution.
Excel Functions Referred to in this Post
Exp – Return the exponential value of the input
Forecast – Forecast intermediate or future values based on known X and Y values
Growth – Derive an exponential estimate for a known set of X & Y values
Index – Lookup a value at row/column intercept from a table or array of data
Intercept – Return the intercept of a linear estimate
Linest – Derive a linear estimate for a known set of X & Y values
LN – Return the Natural Log value of the input
Logest – Derive an exponential estimate for a known set of X & Y values
Power – Returns the value of a number raised to a power
Slope – Return the slope of a linear estimate
Trend – Forecast intermediate or future values based on known X and Y values
Further Readings
Excel has a number of extra Statistical functions hidden in the Data Analysis addin.
I have not discussed or used these tools here as not all users will have access to them and the post is getting longish already.
Functions you may want to have a look at include:
Correl & Pearson: Both functions allow the calculation of correlation coefficients between variables.
Exponential Smoothing: The Exponential Smoothing analysis tool predicts a value that is based on the forecast for the prior period, adjusted for the error in that prior forecast
Fourier Analysis: The Fourier Analysis tool solves problems in linear systems and analyzes periodic data by using the Fast Fourier Transform (FFT) method to transform data, great for analysing periodic and frequency based data.
I would direct readers who are interested in using these techniques to look at the following sources
Microsoft Excel Help – Statistical Functions
Newton Excel Bach, not (just) an Excel Blog
Further Readings
What’s Next ?
In the next post we will looks at some Tools that Excel has to assist us in quickly determining which estimate method we can use.
I will also give you a neat little UDF to assist in your interpolations/extrapolations of your data which was used to make the animated GIF at the top of the first post.
ps: Happy Australia Day Everyone 🙂 !
115 Responses to “73 Free Designer Quality Excel Chart Templates – Grab now and become a charting superman”
There's nothing worse than the default MS chart formats. Thank you for working to rid the world of these eyesores.
Man, you sure do have a fetish for Excel.
What about line charts? These are great but not feeling the love for the lines. -C
[...] 73 Free Designer Quality Excel Chart Templates - Grab now and become a charting superman Plantillas para gráficos de excel (tags: charts excel grafico) [...]
Hi...These templates aint working
Message Received: Drawing conversion failed.
@Monte Bel - thank you for visiting PHD and commenting 🙂 Hope you liked the templates
@ Kapil : thanks 🙂
@Cristobal: Thanks for visiting PHD, btw, the line charts are there, just load the template and convert the chart type from bar chart to line chart, the colors would adjust automatically (they should 😛 ), let me know if this doesn't work.
Really cool templates, many thanks, I downloaded them for future use. Bye
[...] Download 73 beautiful excel chart templates | Art of excel charting | Become a conditional formatting [...]
[...] ...http://www.newyorker.com/arts/critics/notebook/2008/03/24/080324gonb_GOAT_notebook_schjeldahl73 Free Designer Quality Excel Chart Templates - Grab now and become a charting superman add to del.icio.us add to Stumble How many times you created a chart in Microsoft excel and [...]
Excellent prepared
Vuélvete un Superman de las Gráficas en Excel (Inglés)...
Si eres un Ingeniero sin la menor idea de como combinar colores y hacer mas atractivas tus gráficas, aquí podrás encontrar unas plantillas para que puedas volver tus gráficas en Excel mas atractivas y dejes con el ojo cuadrado a tu Jefazo. NOTA: Los co...
Vuélvete un Superman de las Gráficas en Excel (Inglés)...
Vota este post en teknear.com...
Hi, your chart templates are great. I did n't understand how to create 1*1 pixel in paint brush with the colour of my choice.
@DRN ... thanks, you can create the color of your choice in mspaint by,
1. go to start > all programs > accessories > paint and click on it
2. double click on any of the color buttons at the bottom
3. select "Define custom colors >>"
4. enter RGB values for your color choice
5. click on "Add to custom colors"
6. click OK
7. select paint bucket tool (tool tip says "fill with color")
8. click on the image area
9. go to menu > image > image attributes (or press ctrl+E)
10. enter 1 as width and height
11. save as bmp or jpg or gif
cool, now you have a 1*1 pixel in color of your choice.
Let me know if this helps you... 🙂 welcome to PHD...
Thanks a lot...I got it 🙂
[...] read: Learn how to create project plans / gantt charts using conditional formatting Create art grade excel charts with these 73 designer templates Other uber cool excel tricks to make your colleagues zealous and your boss happy Tags: charting, [...]
Chandoo, You are a Freaking Genius, you saved me atleast 2 days of work.
Thanks Man for this, great to see some value been added to the Internet.
Please do post some more sample, this will be really helpful to alot of us.
@Dhondu ... welcome to PHD, thanks alot for your comments.. 🙂
thank you!!!
you made my day..too much helpful
[...] Read: Become a conditional formatting rock star - learn these 5 cool tips 73 free excellent excel chart templates, save time, look smart! Tags: excel, howto, ideas, learn, microsoft, technology, tips, tricks Yo! rock star, mind [...]
Dear Chandoo,
Really you are great yar! I learned most of the Conditional Formatting by your tips. If you have any other tips and tricks in excell please send me a link.
Thank You
With regards.
Is it possible to display charts simply by selecting the data, and is it possible to change the chart by selecting another data.
Please reply.
Dear Excel Master,
Hi Chandoo I have changed your name ...You are really great..I have download all cool templates for future use.
If you have any other tips and tricks in excel please send me a link.
Have nice Weekend Bue.
[...] Art with charts, 73 FREE Downloadable Excel Chart templates Tags: charting, cool, excel, howto, ideas, microsoft, technology, tricks, visualization [...]
Thank you so much sir, Really cool templates, I downloaded them for mu day to day use. KEEP IT UP!
@Sadiq Ali: Hi sorry, I didnt notice your question till now "Is it possible to display charts simply by selecting the data, and is it possible to change the chart by selecting another data."
well, its not possible to plot charts dynamically by selecting data unless you are willing to write some VBA. There are other techniques to make charts look dynamic, I will write about one such technique sometime soon.. keep watching this blog 🙂
@Nikhil - welcome to PHD blog, thanks for your comments. Sure, just subscribe to our mailing list or blog content feed to get fresh hot excel tips everyday.
@Shanker - Welcome to PHD blog. Thanks for your comments. Keep visiting this place to enjoy more tips 🙂
[...] on charts: 73 beautiful excel chart templates - download free Categories : Excel Tips | visualization Tagged with: bad charts | charting | Excel Tips [...]
Hi Chandoo!
I've been reading through a lot of the site for hours and I got a question man, I've encountered recently some excel documents a co-worker downloaded which is sort of a quiz, basically a game quiz, it has an image of a movie and you had to guess the name in a box then, a box below gave you a correct with green bgcolor or wrong in red bgcolor. My question is what kind of formula or way, can I get to use this so I can do some quiz test for some students? it's a very ingenius way to pop the brain a bit with excel. I appreciate the time you put into this site, keep up the work.
hi chandoo,
is my second time typing it seems my explorer sucks, I'll make it short this time, I received an email with an excel document that does a quiz where they showed you an image of a movie and you had to answer which movie is it from, almost case sensitive capitals don't matter, so i would like to know what is the formula or whatever it is so i can apply it on educative ways for classes. thanks for the time you put into this site.
@istreva ... thankfully your first comment made to my server before crashing your browser. Welcome to PHD and thanks for the comments 🙂
You can create a quiz in excel by using protected cells and conditional formatting. I will write a tutorial on this may be in the next 2 weeks. Basically the way to do this is to have the answers in a range that is protected so that no one can view it, and then use conditional formatting on cell(s) to change color to red / green based the answer entered by quiz taker.
I think that would be awsome, I've been trying to figure that out but I'm not an excel guru, I studied some computer programing and done some javascript, so i thought there would be some "if" "then" involved, I think If you can point me in one direction with one sample I can manage to replicate the process and maybe even add on some more to it! Thanks man, 6 hours a day in the office can be very insighfull reading your site 🙂
[...] 73 beautiful chart templates - free and easy to use Become a conditional formatting rock star, download these cool examples [...]
Hi
I just found this site and am happy to find other people interested in Excel as much as I am!. I have a problem because my IE can't find the link to download the charts. Are they still available??
Thanks
Joseph
@Joseph: Welcome to PHD and thanks for visiting us 🙂
You can download the files from http://cid-b663e096d6c08c74.skydrive.live.com/embedrow.aspx/Public/73%20Free%20Designer%20Quality%20Excel%20Chart%20Templates.zip it is a one zip file available for download from Live.com's skydrive. Let me know if you have access issues, I can upload the file to my own server and give you another link.
Thanks,
Hi Chandoo, this site is really useful for my report. Everyone might admire me as of my interesting presentation. nice day!
Thaopham: Awesome... I am happy you found this useful 🙂
I am still not able to download these templets.......ti shows access denied.....sorry for being too demanding but if you could mail to my id azmat1979@rediffmail.com it would be highly appreceiated
hi Chandoo... this is of great help and saves a lot of time...
Thanks
Fantastic! I don't use Excel much, but this is so cool, I had to download the templates 🙂
Thank you.
Great work!!! This is very useful!! Cheers - Deepak
[...] Earlier on pie charts: Why no one likes your pie & what to do about it? | In cell pie charts | 22 beautiful pie chart templates for excel [...]
perfect! thanks very much for your contribution.
Excellent charts!
Microsoft must buy your chart templates and use them in their next Office release.
May God bless you.
Hi Chandoo,
Thank you very much...I learned a lot conditional formatting &
Dashboard technique because of you.
If you can provide more dashborad examples clearly..then it will be
a great help.
Regards
Rajinikanth
thanks for your valuable chart templates. really they are very useful. keep up good job.
@Nick: Welcome to PHD and I am happy you liked these charts.
Hi Chandoo,
Its very good help for every one who wants to prepare the charts,
your templates are good and I utilised some of the templates with some modifications.
Great job!!!!!!thanks
@Rajinikanth: Welcome to PHD and thanks for your nice words. I am very happy you liked the templates and using them in your work.
good day, dear, This is very useful, thanks a lot,
keep is going,
[...] Free High Quality Chart Templates [...]
[...] Better excel chart templates - Create great looking charts with these templates [...]
I want to create a chart where the bars in the chart mirror the color in the cells used to create it. For example, if something is Red at 12% and Green at 85%, I want the columns in the chart to be Red at 12% and Green at 85%. Whenever I try to create the chart it colors each bar the same. Is what I want to do possible?
SHannon - one way you could do this is to add a different series to the chart for each colour that you will be using, then format those series the specific colours you want. The series would need to reference different columns, which would only contain the data specific to each colour.
For instance, if your original data was in a column starting from A2, then in the cell B2 (which will be where the green series of the graph points to) you would have a formula like =if(A2 > .80, A1, na()) which means that only data that meets your cut-off point for being green (80% in this example) gets copied there. Anything else gets entered as the error #N/A, which Excel doesn't plot.
THis should do the trick.
Thanks for your work. It's what I ever wanted in Excel, otherwise a good program.
Little question - the edges of the pie charts are uneven - is some anti-aliasing possible?
Dear Sir
Actually an looking for your help.
I have a folder which contain word and excel working on .However it me personal file but have to dispute in where a lots of person to get access with it .is it possible to put it with an easy way as read only just they can view it only.Thanking in advance for your help
@Martin... Try using Excel 2007, it makes the edges of charts look more smoother. More on the charting differences between 2007 and 2003 here: http://peltiertech.com/WordPress/why-i-dont-like-excel-2007-charts/
@Krishun... You can use the protect sheet options to protect ranges or whole worksheets from editing. Learn more here: http://chandoo.org/wp/2009/11/03/make-better-excel-sheets/
Excellent!! I've been looking for some nice looking charts!
You are an angel!!!! I have struggled with a pie chart, but not anymore!!
Thanks,
Wise Writers and Speakers
Hi Chandoo
When I try to download it does nt come as a normal zip folder and it asks which program to use? I tried excel and doesn't work! any ideas 🙁
@Catherine... sometimes live.com adds an underscore "_" at the beginning and end of the file name. just select the file, press "F2" and remove the "_" from end. This should make the file work with winzip (or expand all option).
its great..
thank you
Thats Gr8:-)
[...] 73 free designer quality Excel chart templates — endlich keine 08/15 Charts mehr! [...]
Hi Chandoo,
I found these templates, and like the idea of using them. However, I must be missing something because when I follow the directions, the result I get is not what I expected. When I choose the range $N$9:$S$13 and select the user defined chart, I get a really strange result.
I found that I had to select only the data values ($O$10:$S$13), then when the used designed chart appears, manually re-enter the X Axis series ($N$10:$N$13), and the each references for each of the years ($O$9 etc.).
Any idea what I am doing wrong?
Regards David
Thank you! This looks great. The defaults look horrible. You've done a great job with these templates!
Great Work;
It is very useful in a real life and time saving as well.
Thank you
Saved me a lot of time and grief and gave me a much better quality of output than I would ever have achieved on my own
Much appreciated
Hi Chandoo - Have read your interview in msn. You are simply great and can stand as one of the roll model to the youngsters especially who scream by comparing themselves with others promortion or hike.
Best part is that you have realised what is your passion.
HEARY CONGRATS.
Rgds,
Gopinath
[...] Free Excel Chart Templates [...]
Do you have a simple Excell Add-On that gives Dashboard gauge results? My physicians need something easy to ready and the Gauge formot seems to be easy. Thanks
Dear Chandoo,
I just came across your site and I must say thank you very much for all the helpful tips. Saves all of us days of working so many things out ourselves!! I have a problem. I download the chart templates file from the link you gave but when I tried to open the Zip file using WinRar, I get an error message "The archive is either in unknown format or damaged". I have used WinRar to open other zip files without any problems. Is there any way I can re-download and open in WinRar? Are the templates still available? Thanks very much
Hi Chandoo,
Pls ignore earlier request (not the comments though!) as I managed to download and extract. Thank you once again
UnniK
[...] 73 Free Designer Quality Excel Chart Templates – Grab now and become a charting superman | Poi... (tags: excel graphs charts templates) [...]
Hi Chandoo
I do not know how to put curved text in the doughnut shape which aligns well with the lines.
Any one any idea?
Vinod
Chandoo....you rock!!! Thanks a tonne, man!
Hi Chandoo,
Cant download the templates as the link is not available anymore. can you please provide an updated link?
Many thanks
@Ahmet
The download links take you to a Sky Drive page
Just select the file from there
There are several online tools available that can help you create comprehensively designed flowcharts and graphs within few mins. These could be flash animated charts that could go in to a ppt slide.. Read more here.. http://askwiki.blogspot.com/2011/09/best-of-online-graphs-and-charts.html
By learning Microsoft Excel Online it helps me to work more effectively and more precise with the help of examples files were we can download and learn in more advanced terms in MS Excel. I appreciate the work of this online training programme to higher extent because it help lot of people who is willing and interested to work in Excel like me. I Love MS Excel to the higher extent. And I request you to please upload more example files so that the people like me can be benifited from Excel training.
Thank you.
N.Vishwanath.
I appreciate for all your help to make us learn Microsoft Excel in a more advanced terms. I request you to please update more exercise files, so that the people like me who are interested can be benifitted from learning MS Excel. I Like MS Excel to the higher extent.
i cash teachung
This looks great! I would really like to take a look at these. Is there another, more direct link to download the templates? The Sky Drive link doesn't work for me.
Nice templates! Thanks!
Biju
http://www.lenvica.com
Wonderfully creative and comprehensive. Bubble charts play an increasingly important role in my work. Any idea why Pie Charts can have leader lines to show the Label, but I cannot do that [easily or at all?] for bubble charts. As you know, bubbles can overlap or completely cover each other, so use of leaders can make them understandable. Thanks for any recommendations
Hi, I have a question regarding templates. Basically I have a series of data, theyre like 10 numbers. I calculated the summary statistics for it already and now I want to create a bell curve and have that bell curve automated where if I put in any number, it will plot it on my bell curve and I can see if its an outlier or how many standard deviations its away from the mean. Thanks
PS: Im using Excel 2007 and already have the Data Analysis Took Pak added.
Chandoo,
I have tried few times but can't use these charts. When I use the 1st method - nothing happes when I change the data??
more clear
Chandoo,
I want to build a 3-D charts for Item 1, 2, 3 and their sales for 2011 and 2012. Do you have a template for that?
Simon
@Simon
Setup your data like:
2011 2012Item A 17 20
Item B 15 13
Item C 12 16
2011 is above column with no. 17
2012 is above column with no. 20
Select the whole area including Items, Years and data
Goto Insert, Chart, select the 3D chart type you want to use
I think I need to clarify the data set a bit.
2011-01 2011-02 2011-03 2012-01 2012-02 2012-03Item A 17 20 23 34 32 32Item B 15 13 34 32 33 65Item C 12 16 34 32 44 99
I want the Items to stack in 1 bar per month, and show it in one list of bar for each year
Simon
@Simon,
Can you email file to me?
(Click on Hui above and look at bottom of the page for email address)
Hi Chandoo,
I like the look of the pseudo incell chart, how did you achieve such an effect? I've downloaded the templates, but there is no fill for any data series.
Thanks!
JY
i got to know abt chandoo.org recently, n since two days completely diggin out the site 😛 i just love working with excel n powerpoint n graphs etc.. this site is really "awesome". 😀
Do you have any cool templates for line graphs?
HI CHANDOO,
PLEASE LET ME KNOW HOW TO CREATE THAT SMALL BOX AS U HAVE CREATED IN A New Year Resolutions Template that Kicks Ass , IN WHICH WE HAVE TO TICK OR UNTICK.........
Does ANYONE know if its possible to make a CLOCK graph to depict overlapping work schedules for a crew at any particular job location? if so, where can I get it and/or how do I make it. Thanks for ANY feedback David.
update please...
Really cool templates, many thanks, I downloaded them for future use. Bye
Seeing the stuff in your website I feel astonished that how a man can be so brainy doing so many things single handed. Kudos to you.
Hi Chandoo excellent charts for free, but i ma trying to find out how you created your pie of pie of pie chart re water use. how can i create the same thing using my data
I am very interested to see if someone has used these dashboards as shared workbooks to give multiple users (5-10) access to update the dashboards concurrently. Lots of our clients use spreadsheets very inefficiently with no concurrent update of data made possible due to their reliance on old school "you-add-your-data-and-I-will-add-mine when-you-are-done" approach with no account for version control etc. I don't want to use VBA because once you step out of the team they keep phoning you to make the slightest changes to the code. I want to keep it as simple as possible and was hoping their would be people out there who have used these dashboards in a smart collaborative manner.
The option of saving the chart templates is not available in Excel 2010 as i m currently using the Office 2010. Pls guide how to get the oprion of Chart Type > User Defined
I downloaded the excel template, but with I copy the chart to workbook, how do I copy the data put. its not working for me please advise.
I have the latest version of Excel. Method 1 and 2 do not work! I cannot edit any of the templates. Can someone Help me please???
Hi Chandoo,
How to convert pie chart into bar diagram. It is like converting earth to flat map but do know the process.
Help will be appreciable.
Will people that don't have these templates see them the same way when they open the spreadsheet on their computer?
@Julie
All versions of Excel after 2003 render colors and lines slightly differently to versions before 2007
But yes you will mostly see the same on your PC
Hi!
I am looking for a "Variance Matrix" Graph for differences in Quality scores. Basically, we score quality from 1 - 5 and then an auditor will score the same call 1 - 5. I want a graph that has 1 - 5 vertically and then 1 - 5 horizontal at the top, then put counts in the middle for how many calls match up in each box EG, If I scored it a 1 and then they scores it a 3 it would show up at 1 vertically(my score) and 3 along vertically (Auditors score) is this possible on excel?
Hi Sir,
I want to create a Graph any type. I have data in below format:
Company Name Emp Name Number
ABC Aditya 555
ABC Mukesh 777
ABC JHA 999
XYZ Aditya 666
XYZ Mukesh 444
XYZ JHA 888
Please guide me to plot this graph on above data but I want Company Name, Emp Name, Max of No, Min of No and Avg of No.
Waiting for your response...
@Aditya
Can you please pose the question at the Chandoo.org Forums
http://forum.chandoo.org
Add a sample file to simplify the task
Thumbs up for the templates...you rock man!
Thanks so much.
[…] Free High Quality Chart Templates […]
this is one what m i looking for