So you’ve made it to part 3 of Are You Trendy, well done.
In todays final post of Are You Trendy? we will take a break from the maths and discuss techniques and tools that Excel provides to assist us with Trend Analysis.
We will look at 2 techniques, one built-in to Excel called Charts and another which is a User Defined Function (UDF) I developed.
Both techniques have uses in analysis of trends.
Once again all examples below are found on the Examples Workbook.
Excel Charts
Excel has a number of charting styles some of which have the ability to calculate and display Trend Lines.
The following tables shows which charts Do and Don’t support built-in trendlines.
| Supports Trendlines | Doesn’t Support Trendlines |
| Scatter (X Y) | Pie |
| Line | Donut |
| Area | Radar |
| Column | Stock |
| Bar | Surface |
| Bubble |
For those charts that don’t support trendlines, this doesn’t stop us, as Excel users, from adding other series or using other techniques to add trendlines to the charts, it just that we have to do the hard work ourselves and not use Excels built-in skills.
For this exercise we will be using a Scatter (X Y) Chart but the techniques apply equally to all charts that support trendlines.
So lets go: First we need some data.
Next add a scatter chart , Select the data and Insert Chart, X Y (Scatter Chart)
Adding a Trend Line to the chart is as simple as selecting the chart and right click on the Series you want to add the Trend Line to, see below.
You can change the trendlines properties to make it stand out as per the above.
Once you add a Trendline a Format Trendline dialog will appear.
This is the main window for setting Trendline properties and format options.
In this post I am not going to be dealing with the Tab Options of Line Color, Line Style, Shadow and Glow and Soft Edge options, as I am sure you can work out what they do and play with them at your leisure.
Trendline Properties
Once you have added a Trendline you can access the Trendlines properties at any time by:
Selecting the chart
Select the Trendline
Right Click and Format Trendline.
How Many Trendlines Can I Have ?
You can add a large number of Trendlines to each Chart Series in Excel by simply selecting the Right Clicking on the Chart Series and select Add Trendline.
Some of the regression types may not be available on second and subsequent regressions.
Trend/Regression Type
The main section of the Format Trendline dialog is the Trend/Regression Type:
This section lists 5 trend types we are familiar with from the Are You Trendy? (Part 2).
For more details on the individual Tren Types refer to Are You Trendy (Part 2).
The default trend type is Linear ( Y = mX + c ).
You can select each type in turn and see the estimated trend.
Exponential
Linear
Logarithmic
Polynomial
This shows the Trendline for a Polynomial Estimate of Power = 2. Excel supports up to Power = 6.
Power
Moving Average
Moving average isn’t a forecasting tool, but is more of a data manipulation tool, which is commonly used for elimination of short term trends or smoothing of the data source. Some industries use Moving Averages to look at buying and selling opportunities by comparing the current price against longer term trends.
It is possible to use moving average data for trend analysis, but this must be first done manually in Excel and then it can be used with the techniques discussed here.
As such we won’t be discussing its use here.
If you are interested in reading more about the use of moving averages have a look at Moving Average.
Trendline Name
The Trendline Name section by default shows you the Selected Trend Estimation type and your Data Series Name eg: Linear (My Data)
However you can type whatever Name you want by using the custom field:
eg: Huis Fruit Shop Sales Trend
Forecast
The Excel Chart Trendline function allows us the option to project our Trendline a number of periods or X Values into the future or past.
The above will forecast our trend line 50 X axis values past the limits of our data and 10 X axis values prior to the limits of our data and is shown below.
This is a great option to use for 2 reasons.
Firstly you can use this to see where the Trendline will cross the Y axis (X = 0) and is an important check to validate your selected Trendline.
You can see in the example above that the Trendline crosses the axis at -10. If this isn’t correct we can either try another estimation technique or Excel has a tool, which is discussed below, to deal with that.
Secondly, you can use it to look into the future by projecting the Trendline past your data limits.
Misc Parameters
The final 3 parameters
Set Intercept
By default Excel Chart Trendline has used a Const value of True and hence calculates the natural intercept value for the data. If you want to over-ride this with a 0 intercept value Tick the box and select a value
Set Intercept : Not selected
Set Intercept : Selected Value of 10.0
I would always suggest using the Forecast backwards option first to see what the natural intercept is before using this option (Both charts above show the Trendline projected back 10 periods) but the second chart has a Y intercept set manually to 10.
Display Equation on Chart
The Display Equation on Chart options use is self explanatory, it Display the equation to the current Trendline on the current Chart:
The equation is based on the select Trend type.
The equation is also live, in that if you change and selected another Trend Type or the data changes the equation will update, similarly if you select to intercept the Y axis at 0 or another value.
By selecting the Text Box which has the equation the equations Font properties can be changed eg: Font Style, Size, Italic, Bold etc as well as background color.
Display R2 on Chart
This display the R2 value on the chart in the equation Text Box as shown above.
The R2 value is based on the select Trend type.
The R2 value is also live, in that if you change and selected another Trend Type or the data changes the R2 will update, similarly if you select to intercept the Y axis at 0 or another value.
By selecting the Text Box which has the equation the R2 the Font properties can be changed eg: Font Style, Size, Italic, Bold etc as well as background color.
How Can We Look at Values Along a Trend Line?
In Are You Trendy? (Part 2) and in the section above we looked at a number of ways of establishing a Trendline.
So how can I predict values along a Trendline?
There are a few ways and we will discuss 2 of them.
1. Write an equation in excel
2. Use my Trendy User Defined function.
Write an Equation in Excel
In the previous section we had a chart shown below:
The equation for the line of best fit is Y=7.8984.e^(0.0256.X) which in Excel cell formula talk is =7.8984*EXP(0.0256*X Cell)
So we can setup a cell or range of cells in excel to show us either intermediate values or future values of X and the corresponding Y values.
The great thing about having an equation is that we can now use this to animate our chart.
I’m not talking about Avatar style animation, but simply adding a tool for the user to interactively select X values and the equation and chart will show us the new Y value. This even allows us to show future values past the end of our data because we are using the equation to our data.
How do we do this?
Refer to Sample Workbook.
Firstly lets add a cell which will contain the X value
E52 = 10
Secondly add a cell which has our equation
E53: =7.8984*EXP(0.0256*X Cell)
Third add a Scroll Bar Control from the Active X controls on the Developer toolbar and set a few properties
Minimum = 0
Maximum = 300
Linked Cell = E52
Large Change = 10
Fourth add a new series to our chart which will just have a single X and Y value
Series Name = “Our Trend”
X Series = E52
Y Series = E53
You can now interactively move the srcoll bar back and forth and the Chart will show you the new forecast value for our sales in Huis Fruit Shop.
The limitations with the above approach is that it is fixed to the equation you use for your Y value, which is based on your selected choice of Trendline type.
Can we link this technique to the equation from the Trendline ?
With Excel natively, No 🙁 .
TrendY – A User Defined Function for the Analysis of Past, Intermediate & Future Trendline Values
In a post at Chandoo.org user Trevian3969 asked the question “How can I view intermediate values along a Charts trend line?”
The answer was the development of the UDF, TrendY (Trendy).
Trendy was developed to do exactly that, take the equation from a Charts Trendline and evaluate it to determine intermediate, past or future values based on a given X input.
Because the UDF is taking the equation for the trend line it can be used to return a Y value for any X values, prior to, later than or within the supplied or known X Range of the chart.
An Example of the use of Trendy is shown in the attached Workbook
Form: = TrendY(X Value, [Chart No], [Series No], [Trendline No])
X Value: Is the X Value that you want to know the Y Value of the Trendline
Chart No: Chart No is optional and is the Chart No on the current sheet, Default or omitted = 1
Series No: Series No is optional and is the Series No on the chart, Default or omitted = 1. The series No is the number shown at the end of the Formula Bar when a series is selected
eg: =SERIES(“My Data”,Sheet1!$B$2:$B$41,Sheet1!$C$2:$C$41,1)
Trendline No: Trendline No is optional and is the Trendline No of the Trendline you wish to track. Default or omitted = 1.
[Option Parameters, Default = 1]
Eg: = TrendY(20) will place the value of X=20 into the Trendline Equation of the Trendline of Chart 1 Series 1 Trendline 1
= TrendY(40,1,3,2) will place the value of X=20 into the Trendline Equation of the Trendline of Chart 1 Series 3 Trendline 2
=TrendY(B43,,3) will place the value of cell B43 into the Trendline Equation of the Trendline of Chart 1 Series 3 Trendline 1. Chart and Trendline both default to 1 as they are omitted.
The function works with all the Trendline types and variants of those, except the Moving Average.
To use the Trendy UDF copy the following code to a VBA Module or alternatively import the Txt File as a new Module
The code is also available in the Example Workbook with a fully working m0del.
If you don’t have the Trendlines equation shown it will do that for you and it ignores the R^2 function if shown.
The Trendlines equation box is actually where the function gets the equation from in the first place.
Then on a worksheet simply use then =Trendy(X Value) as described above.
Trendy has only been tested on Excel 2007 and 2010, use on other versions at your own risk.
Limitations
The Trendy UDF is limited to use the available precision of the parameters, for each equation, as displayed in the Equation Text Box on the Chart.
In some cases especially when multiplying by powers of large numbers and especially in the Polynomial equations, there may be a large discrepancy between the calculated value by Trendy and the displayed value by the Trendline on the Chart. In these cases the Trendline is correct. This will show on the chart as the Plot Y value of Trendy not being on the Excel calculated Trendline.
Trendy has 2 constants at the top of the UDF that are used for the calculation of Intermediate Equations. These are equations that the user doesn’t see but are done at a much higher level of precision to avoid these errors. These constants are:
Const DataLabelNoFormat = “#,##0.0000”
Const PolynomialNoFormat = “#,##0.000000000000”
If you have rounding errors the easiest way to fix them is to increase the number of decimals by increasing the number of zeroes after the decimal in the appropriate line, remembering that Excel only carries 15 decimal places anyway and Trendy is already using 12 for Polynomial Trends already.
Trendy returns the displayed equations to 4 decimals after the intermediate calculations in any case.
Further Readings
All Examples from the Are You Trendy? Series in one Workbook
Final
I hope you have enjoyed this 3 part series on Trend Analysis and Forecasting using Excel.
It has been put together to Introduce you to, and whet your appetite to, some of the functions, tools and techniques that you may not have had a lot of exposure to in the area of Trend Analysis and Forecasting using Excel.
I’d like to thank Chandoo for the opportunity to look after Chandoo.org for the past week and the opportunity to put the 4 posts up.
I will now hand Chandoo back the keys to the blog, hopefully without any dents.
Let me know what you thought of the series in the comments below:
Keep us informed on How you go with real data once you start applying some of these techniques.
Hui…
ps: I still don’t know if Trevian3969 was happy with my response to his post as he never responded ?
70 Responses to “10 Tips to Make Better and Boss-proof Excel Spreadsheets”
Proper print settings on each sheet helps your boss to print the reports quickly without hastling you after printing irrelevant stuff.
It is highly relevant that you print your reports once before circulating it to your boss or other people.
Knowing that what your boss actully look at in the entire report can be very usefull. You can build a good summary of what your boss wants and put that as separate tab in the form of dashbord report, so that your boss does not peep into rest of your work and start pocking you with irrelevant stuff.
You can also put that Dashboard into the email summary and not trouble your boss to open your workbook. This is ultimate boss proof tip and I have been using this for long time now.
Thank you Chandoo. Great checklist to follow before delivering an excel spreadsheet to someone else. Some points you mention are seemingly so simple that we might overlook them - like selecting cell#A1, but they make a difference to the impression the spreadsheet creates at the recipient's end.
Dear Chandoo,
Great tricks.
One trick I use (more and more) is to hide the sheet tabs and to hide the formulabar via the 'tools' 'options' and the 'view'-tab.
Another trick is to limiting the scrolling area to hide all columms (or rows) until the end of the sheet. Select the column, press CTRL+SHIFT+RIGHT, right-click on the column and hide (also possible via VBA).
I was wondering though if 'boss-proof' is related to 'excel-stupid-proof'?
Cheerio
Tom
Just wondering if the hiding formula bar really works when a recipient opens it whose "view-show-Formula Bar" is still checked...
It's saved to the sheet I believe.
Absolutely agree with this post !!!
on the past months, after reading this blog, PTS's and Debra's Contextures, one of the things I've beggining to do as a best practice is to create all my spreadsheets with 3 tabs: data, summary and control, and this last one generally xlveryhidden, and sometimes the data one hidden as well.
And this restrictions are also being applied as best practice, and with a lot of benefits as you well mentioned. Furthermore, if combined with dynamic named ranges, formulae is more readable to users, and the WOW effect is often achieved when the question "How did you do that?" arises.....
Keep on the good posts !!!
Rgds,
Martin
Would you mind sharing an example of this technique?
Is there a way to keep the data in a seperate file rather than the same excel. This way you could keep presentation and data separate. But not sure how you would link up the two excel files
Yes, there is a way but it is not prefered.
I used this a coulple of times, (You need to code).
mail me if you need assistance with some sort
It entirely is possible. The problem comes though, when you share the spreadsheet.
If the recipient doesn't have both files, or access to both, things break when the values try to refresh.
ey, why is the boss a she??
haha - welcome to the future. About time.
Chandoo, one more trick that we could use with the help of VBA, RT click on the View code of the particular sheet, in the properties table set the Visible status to 2-xlveryhidden, this ensures the sheet name does not show up even when the BOSS tries to unhide the sheet from the sheet >> unhide option. Dont forget to password protect the VBA (available under tools >> VBAProject properties.
Very good tips, although I have to say Chandoo, that your cats probably need to be spayed or neutered if they behave like that. =)
Good to see all these tips on a single "sheet", and giving the name *boss proof*, and Dilbert was a great welcome 😀
The best way to "Boss Proof" (and "Self Proof"!!) a spreadsheet is to keep back ups. I use a macro that saves the last 3 significant versions of the spreadsheet all with a date stamp included in the file name.
To quickly select cell A1 on all sheet, use CTRL-Page UP or CTRL-Page down to navigate between sheets and CTRL-Home to select cell A1 (if you have frozen pane, it will select the top left cell of the section below).
Great list. And I follow every single item... I also use a consistent background color for input cells in every report/dashboard. And I use a little VBA to identify the user and change the report accordingly (selecting the right market, for example).
Chandoo, Nice post. I like to use the hidden Paste Picture Link option. Keep the original report you want displayed on a hidden sheet and only show the boss the report picture. Also great to watch the confusion when boss trying to select cells is worth the effort!
I usually save as PDF if there's no interactivity in the report. That way nothing can go wrong 🙂
PDFs work a dream for me too and saves the boss's EA from telling me all the time that she can't print my work!!
@All.. thanks a ton for sharing your ideas. I am thinking of writing a part 2 of this post explaining some of your ideas in detail.
@Bazlina ... I will make sure the boss is a HE in the next post 🙂
"10 Tips to Make Better and Boss-proof Excel Spreadsheets"...
Unless of course your Boss reads PHD !
Great article with one glaring error.
If (like me) the majority of your spreadsheet errors are *caused* by cats, adding more cats is just going to increase the problem.
@Hui you always have a boss, even if you are boss. If you dont have a boss, then may be a cat or even a dog.
@Debra: hmm... Are you sure the cats are not after the mouse? Go learn some keyboard shortcuts.. now 😛
Great Web Site. I've done almost all the above in trying to build my application and it's taken me hours and hours reading my "dummies " book. Thank you for all this information.
Is there a formula I can use that will automatically return to "A1" cell should an associate use the 10 page spreadsheet I have?
Is there a way to set an expiration date on my workbook so that beynd that date no one will get beyond the cover page?
Paul, in all my "user facing" workbooks (those that I distribute) I create a named range called "Home" on the worksheet(s) that are most likely to be used. Then I write a little VBA that selects the Home range whenever that worksheet is activated or on other triggers depending on the context of the sheet. This is more appropriate for the dashboard tabs or summary tabs my job requires.
But I usually set this functionality up early on in the design process so I can take advantage of it as well. I will sometimes assign a keystroke to the GoHome macro.
I'm in the marketing department (aka the picture department) and have to say that the macros/Excel sheets from our controlling department are the worst! They come to me to sort out the mess!!
@Peter: You can try creating a table of contents and then place it on each and every sheet so that user can jump to anywhere from anywhere. Here is a tutorial to help you get started.
Also, You can prevent users from accessing the workbook after a certain date using macros. But users can certainly by pass it by disallowing macros on that workbook.
@Jimmy: Wow... (just kidding) Welcome 🙂
I was recently given a spreadsheet to improve upon.
One of the "boss-proof" actions that the previous author had used was to use data validation instead of protecting the sheet to ward off people changing formulas.
After entering a formula or value into a cell, use data validation to only allow, in this spreadsheet, whole numbers between 9999999 to 99999999.
It's a bit of a pain to actually correct stuff instead of just unprotecting a sheet, but for those that know how to unprotect a sheet, it's a definite way to keep them from fooling with formulas.
Puchu,
We would love to see "Print" in your links section.
It helps us taking prints as neat as your posts 🙂
Chandoo,
I've emailed you a couple of times looking for avenues I need to try to put my workbook on the Internet.
I notice you use PremiumThemes for your Web Site...You must feel good about their service. Do you think PremiumThemes might be an option for me?
Paul
Instead of :
Now Right click and select “Hide” option.
Shortcut can be used : Ctrl+0 (to hide)..
sir i wanted to know,how to hide cells or tab without hiding rows and columns? PLZ TELL ME
Hi Chandoo!
Great tips! Im researching on an excel project now that you can create to "lighten" the size without sacrificing the data inside..
We usually encounter problems with the data, excel file is shared, in a network folder.. and there are 11 people that enters their own productivity in each tab.. however, there comes a time (uncertain) where some of the data they enter either gets deleted or changes value.. could this be a file size problem? are there other ways to create this file that will decrease data inconsistencies?
thanks!
[...] Hide un-necessary rows to create clean looking workbooks (and 9 more tips) [...]
[...] Presentation format: all spreadsheets, should be designed so that it is easy to follow the process flow and result. Almost every spreadsheet should be presentable and understandable to senior management without additional formatting or explanation. (tips: how to design boss-proof excel sheets) [...]
[...] on Excel formatting here: How to make better excel sheets, Formatting [...]
[...] on Excel formatting here: How to make better excel sheets, Formatting [...]
[...] 10 Tips to make better & boss-proof spreadsheets in Excel [...]
You will find another quick and easy technique here:
http://www.onsitetrainingcourses.com.au/main/page_blog_hiding_most_excel_rows_and_columns.html
[...] tips: Learn how to make better Excel sheets Spread some love,It makes you awesome! [...]
Save what you want the boss to see as a PDF. Absolutely foolproof and no cats hurt in the process.
I really enjoyed allot of the tips on here, especially the one on comments on cells. That will come in handy on allot of our projects. I would also like to share on on my little tricks. I am constantly working on several different reports with several different systems and in doing so I am constantly running in problems and my way out of them is simply calling <a href"http://www.reportingguru.com/"> Reporting Guru </a> and telling exactly what I'm going through and they can tell me exactly how to get out.
One of the things I've found to boss proof my worksheets are a few simple VBA scripts to automatically protect the workbook/worksheets, and direct them to the "Quick Look" dashboard page, I hide all of the raw data sheets before saving. The script looks like this:
Private Sub Workbook_Open()
Sheets("Summary").Protect Password:="password"
Sheets("Labor Cost by Site").Protect Password:="password", AllowUsingPivotTables: =true
Sheets("Labor Cost by month").Protect Password:="password"
Sheets("Quick Look").Protect Password:="password"
Sheets("Quick look").Activate
ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False
End Sub
I also have a pivot that contains labor cost data which cannot be refreshed while the worksheet is locked.
Private Sub Worksheet_Activate()
Sheets("labor cost by site").Unprotect Password = "password"
Set pvttable = Worksheets("labor cost by site").Range("a1").PivotTable
pvttable.RefreshTable
Sheets("labor cost by site").Protect Password = "password", AllowUsingPivotTables:=True
End Sub
OPPAN GANGAM STYLE!
good
Your post are always with something creative , thanks for sharing this information , your post are worth reading and implementing 🙂 great job
Hi,
I will try to learn every point slowly !
Shokran Chandoo.
Best boss Proofing of sheets is useing indirect(address 😛 this prevents most smartass bossess from doing any actual changes cus the formula will be long and hard to understand for any bystanders..
Also putting the actual calculations on a different sheet can make a sheet bulletproof from bosses.. especialy if you put them in the Very hidden so when the boss learns how to unhide sheets he wont simply find them.
One thing iv also learned is that most bosses is scared of macros that gives "virus" warnings before beeing run 😛 That include the default warning from Excel...
Long formulas or work arounds is best way to go.
What's the best way to amalgamate two existing excel spreadsheets into one?
Two teams use the same format spreadsheets with individual data split into calendar months and I want to make them one without manually entering the data.
Alt + D + D + N
Write a query and viola, Two sheets into one.
Changing the properties of the file to read-only . (While the file is closed, right click on the file and check the read-only box.)
This allows my boss(es) to access the file -- even change it -- without being able to save their changes. If a boss likes his 'new' version, he can save it with a different file name.
But now -- how to prevent the boss from deleting the file altogether? Or deleting the whole network?
Hey man.
Think you can go as easy as to make a shortcut that links to your read only document. Then the boss wont know of the root document. He can figure it out but lets face it. He is a boss and 70% if them wont know squat
Instead of "Hiding" rows & columns, I find "Grouping" works best as its very easy to quickly see if a worksheet has hidden rows/columns. Sometimes hiding a random row/column is not easily noticed and can create issues.
I have one xl sheet with different dates in many columns and one raw's. I want to send this data to another xl sheets for each date. if somebody can help me will be great.
Dear Samantha,
Check out the website of Ron de Bruin. He has a great set of macro's and free add-in that can help you with this issue.
http://rondebruin.nl/win/s3/win006.htm
Tom
Hello, I have just found out that I made a mistake in my spreadsheet: I had a column of negative numbers, but one of them was positive (while it should have been negative). Is there a formula/system to avoid this?
Thanks.
Mariateresa
Yes, data validation. Values you denote would be between -1 and -999,999,999.
Hi,
Hiding any worksheet can be unhidden and messed around easily. I change the visibility in visual basic from -xlSheetVisible to -xlSheetVeryHidden. By this, even if you right click on sheets, you will be unable to find the hidden sheets.
Cool? I think so...
Very informative, Thanks
Is there a way to lock cells in an already protected worksheet.
(Thus the entire worksheet is protected, then the entire office can open it as read only but only a few users have the password to edit the file)
I would like an additional password or prompt box so these few users don't accidentally change formulas.
Itss such as you learn my thoughts! You appear too understand
a lot abnout this, like you wrote thee e-book in it
or something. I fel that you just could do with some percent to presseure the message house a little bit,
but insatead off that, this iis wondeerful blog.
An excellent read. I'll definitely be back.
It is in reality a nice and helpful piece of info.
I am happy that you just shared this useful info with
us. Please keep us up to date like this. Thank
you for sharing.
I laughed out loud reading the 2nd solution about moving to marketing department and making ppts.
I've been using "technical" sheets for a long time already and depending on the audience it is hidden or not. I'm currently in my NO VBA mindset, so the very hidden option is no longer. Using sheets names like: TechnicalCodes; ExplicitVariables;SetUp; HeavyCalc seem to work to my experience as they send along a message "Don' t you mess-up here, you fool!". A "Read This" section or sheet however does not work!
Reading stuff on this site has helped me develop a good habit of using colors and themes to assist the end user in being well-behaved. In my book the best advise here, because it is about the user experience and not only about protection your own work.
For dashboards I get rid of tabs and scroll bars. Besides 2 exceptions, I need to come across a manager who can turn them on again without my help.
Seems that I forgot about protecting cells, sheets and workbooks altogether. Damn!
Thanks for the informative article Chandoo, I've been struggling with Excel lately. It's a powerful tool, but hard to learn for me.
Thanks Chandoo for sharing these excel sheet tips it helps me a lot to understand excel more.
Nice roundup, Chandoo! Here's one more I thought would be relevant:
For Excel 2013+, you can hide the ribbon, as shown in this animated gif: https://gridmaster.io/tips/hide-ribbon-excel-space
This will simplify the interface, making it less likely for people to accidentally make changes. 🙂
THANK YOU SIR
I'm better at Power BI thanks to you!