If you’re building large models, then you may want to use volatile functions – including OFFSET(), INDIRECT(), and TODAY() – with caution, because unless you know what you are doing, they *might* slow Excel down to the point that data entry is sluggish, if not downright tedious.
In fact, you *might* want to consider getting out of the habit of using these functions at all if there are alternatives, and you might want to replace volatile functions in your existing models with non-volatile alternatives…I have reduced recalculation time in large models from minutes to milliseconds by doing just that!
So what the heck does volatile actually mean? And why should you care? Let’s find out, shall we?
How does Excel update all those cells?
Let’s take a look at how Excel ensures that each cell has the right number in it when you make a change somewhere. But first, a disclaimer: Note that this is an introductory article, and so is necessarily simplistic. If you want to know more about the specifics of this complicated subject, check out the links to Excel MVP Charles Williams’ excellent site at the bottom of this article. Okay, disclaimer ends…
A large Excel model might have several hundred thousand cells with formulas in it. Maybe even several million. Most of these formulas will reference other cells, and many of those cells will have formulas in them that reference other cells in turn, and so on. If a formula in a Cell A2 refers directly to Cell A1, then A2 said to be directly dependent on A1. Obviously if A1 changes, we need those changes to flow through to A2. And when recalculating the entire workbook, we need A2 to be recalculated AFTER A1 has been recalculated. That’s called a dependency chain.
Large models can have a number of very long dependency chains comprising of hundreds of thousands of cells that run across worksheets or even between workbooks. To keep track of how all these cells interrelate – and to ensure that a change in any specific cell’s value correctly flows through to any other cells that may depend on it – Excel builds and maintains what is known as a ‘dependency tree’. Think of this as a big flow-chart or circuit diagram showing how all the cells in one of these giant formulas interconnect. Excel maintains this dependency tree every time you make a change to a formula in a cell, by looking at the argument list of each separate function within that formula. And this dependency tree is saved along with the file itself.
Smart Recalculation
Thanks to this dependency tree, when you change the value in one cell, Excel can work out what other cells might be affected. And so Excel can smartly recalculate just those particular cells. Meaning it doesn’t have to blindly recalculate the whole workbook just because one fairly insignificant part of it might have changed.
So let’s say you change the value of a cell somewhere that has only one other cell pointing at it (and no further cells depend on that other cell). Thanks to smart recalculation, Excel only recalculates the value of the cell you just changed, and the value of that ONE dependent cell. It doesn’t have to recalculate the entire workbook.
Likewise, if you change the value of a cell somewhere that has many, many cells downstream, then Excel of course has to recalculate all of the cells further down that particular chain. But it can safely ignore any cells further up that particular dependency chain. And it can ignore any cells elsewhere that aren’t in this particular dependency chain.
If a long-enough part of a dependency chain gets recalculated, then you might well see the word ‘calculating’ in the status bar while Excel works its way through all the relevant cells in that chain. But usually, this recalculation happens so fast that the word ‘calculating’ flicks on and off so quickly that you don’t notice it.
Not-so-smart recalculation thanks to volatility
Now here’s the important bit: a particular class of formulas called volatile formulas get automatically recalculated any time you enter data anywhere in any open workbook – even if the thing you just changed had nothing to do with those volatile functions. And then this triggers Excel to then recalculate all directly dependent cells downstream from those volatile formulas too. Yikes!
This mean that if you’ve opened a very large spreadsheet model with volatile functions in it – and if those volatile functions have a large number of formulas downstream (or a smaller amount of resource intensive formulas) – then if you are say trying to add items to a shopping list that you’ve started in another workbook it could take minutes for you to add each item to that shopping list, because every time you add an item, it triggers an avalanche of unnecessary and pointless recalculation in the large spreadsheet model.
The fact that each and every cell ‘downstream’ of any volatile formulas get recalculated is an important point to get your head around. Many people think that slow calculation times due to volatility is due to the time it takes to recalculate large amounts of volatile functions in a model. But often most of that delay is in fact due to the recalculation of all the cells ‘downstream’ from those volatile functions. In other words, even just one volatile formula with a very long calculation chain hanging off it could cause you grief. And if that calculation chain gets more and more complex, so does the effect of that one volatile formula.
Here’s how that looks visually:
In fact, it’s not just entering data that will trigger a volatile function to recalculate, but also these things (among others):
- Deleting or inserting a row or column.
- Performing certain Autofilter actions.
- Double-clicking a row or column divider (in Automatic calculation mode).
- Adding, editing, or deleting a defined name.
- Renaming a worksheet.
- Changing the position of a worksheet in relation to other worksheets.
- Hiding or unhiding rows (but not columns)
So almost anything can set off that domino effect. Which reminds me of this:
(And what the heck…if you enjoyed that, then click this link too. But hurry back…this post is getting cold).
So which functions are Volatile?
These ones:
- NOW()
- TODAY()
- RAND() and RANDBETWEEN()
- OFFSET()
- INDIRECT()
- INFO() (depending on its arguments)
- CELL() (depending on its arguments)
If you’re an intermediate Excel user, then chances are that you already use some of these regularly. For instance:
- OFFSET() is usually the function of choice to anyone who wants to create dynamic ranges
- Many large models make use of the INDIRECT() function to construct cell or range references “on the fly” in response to some choice that a user makes
- Many large models make use of the TODAY() function to check if a date entered by a user occurs in the past, present, or future.
When does this matter?
Most of the spreadsheets you use these functions in are so small that you probably don’t even notice any extra volatility-related recalculation. So no harm done. However, if you’ve ever had that a large spreadsheet that seems particularly sluggish when you’re trying to enter new data – or that seems to impact the performance of other open workbooks – then chances are you know exactly what I mean.
I’ve seen frustrated-looking users waiting for as long as one to two minutes for particularly large models to recalculate after each and every change they make to it, even if those changes are relatively insignificant, such as changing the spelling of a column header.
Often spreadsheets like this get so sluggish that users switch Excel’s calculation setting to Manual, just so they can make changes in a timely fashion, and then switch it on again when they’re done in order to have the model calculate the correct answer. This is dangerous…I’d never set calculation to manual if I could help it. There’s just too much chance that someone someday will use output of such a model without remembering to set calculation to Auto. What’s worse, when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened. I have seen many cases in my career where analysts have done just that…opened a workbook with calc set to manual, opened a whole bunch of others where calc was set to auto, and then done an entire day’s work without realizing that calculation was subsequently turned off for all of them. Doh!
Here’s a slide from my Excel Efficiency presentation that warns users not to do this:
Previously you might have thought that you had no choice but to switch calculation to Manual, because you might have thought that this sluggishness is an unavoidable consequence of the size and complexity of your spreadsheet. But now you know that it *might* be caused by use of volatile functions, and that volatile functions might not be suitable for some occasions…particularly if you’re building large models that utilize these functions at key points within your model. Replace those Volatile functions with some non-Volatile alternatives, and you’ll likely find that your model stops being a slow dog, and starts being a much faster greyhound. To the point that you can switch calculation back to Automatic again.
What are the alternatives to Volatile functions?
While volatile functions like OFFSET() and INDIRECT() are incredibly useful, you can usually achieve the same thing by using other non-volatile formulas such as INDEX or CHOOSE, as well as through leveraging off the dynamic references that Excel Tables allow.
And instead of the TODAY() function, you can use VBA to populate today’s date as a hard-coded value in big models, as you’ll see in the download file below. Check out the Alternative Functions tab of that file to see some examples of common use of volatile functions, as well as some non-volatile alternatives.
If you’re struggling to find a non-volatile replacement for an existing volatile formula, then you can always post a question on the Chandoo Forum asking for some advice on non-volatile alternatives.
Am I being over-zealous here?
As we’ve seen, too much reliance on volatile functions *might* trigger large parts of a model to be recalculated needlessly. But it’s worth remembering that this is only going to be noticeable in particularly big spreadsheets. So perhaps I’m being a little overzealous here. So if you know what you’re doing, then maybe you don’t want to dismiss volatile functions outright. After all, you can always assess your options on a case by case basis: try them out, test, test, test, test again, and then make a balanced decision.
However, if you know of an alternative formula combination that does exactly the same thing as a volatile formula, then I’d suggest that you get into the habit of using that instead whenever you can. That way you won’t inadvertently have issues when it really matters. And I’d suggest that if you don’t have much experience of functions and performance, then perhaps it’s safest to simply err on the side of caution and steer clear of volatile functions altogether.
So not only do I see little down side to avoiding volatile formulas, but I see a significant upside: I’ve seen plenty of large models built by the likes of the big 4 accounting/consulting firms that make heavy use of volatile functions, and that consequently have recalculation times so long that they are effectively unusable. Stripping out the volatile formulas from these models has resulted in delays from data entry falling from upwards of two minutes to well under a second. Not to mention that users can now work on other files while these models are open, without fear of triggering an avalanche of unnecessary and pointless recalculation. Had these model builders known to avoid volatile functions, they would have saved users a lot of grief.
Excel MVP and Recalculation Expert Charles Williams says:
The better use you make of smart recalculation in Excel, the less processing has to be done every time that Excel recalculates, so avoid volatile functions like INDIRECT and OFFSET where you can, unless they are significantly more efficient than the alternatives. (Well-designed use of OFFSET is often fast.)
In fact, on Charles’ website he goes so far as to say avoid volatile functions wherever possible.
With all that in mind, I’ve made a personal choice to steer clear of volatile functions where I can. Your mileage may differ. Regardless, the subject of volatility is definitely something that intermediate users should be made aware of. What they do with that awareness is up to them. But forewarned is forearmed.
Fancy a demonstration?
Sometimes it’s most helpful to see something with your own eyes. So download this file, open it, and enable macros: Volatility-demo-using-TODAY-20140230
You’ll see it has a dropdown in it, where you can choose to either populate a cell with the volatile TODAY function or with a hard-coded date:
Downstream of that drop-down output cell are 20,000 formulas spread across two columns:
If you choose the Use Volatile TODAY() Function option from the dropdown, then whenever you enter data in that 3rd ‘Completely independent cells’ column then you should notice a significant delay. Change that dropdown to ‘Use Hard-Coded Date’ and you should experience significantly less delay, if any.
You’ll also see a blue button you can click, that will time how long the delay is under each option:
On my system, there’s about a 1 second delay when using the TODAY() option, and almost no delay when using the hard-coded date. (Note that you have to click the blue button twice after you change that dropdown to get the ‘proper’ reading. The first reading will be artificially high.)
Why are some functions volatile?
The reason for some of these functions being volatile this is fairly obvious. For example:
- NOW() should always return the time as at the last calculation, so needs to be refreshed any time new data is put into the workbook, in case one of your formulas does something specific based on the time of day.
- TODAY() similarly must be refreshed to ensure than the day hasn’t changed since the last time something was entered into the workbook (which will be the case, if someone works past midnight, or if they come in in the morning and make a change to a file that they had left open the previous night.)
But the reasons for others being volatile – such as OFFSET and INDIRECT, which are often used by modellers to create dynamic named ranges – are less clear. First, let’s look at what OFFSET and INDIRECT actually do:
- Offset Returns a reference to a cell or a multi-cell range that is a given number of rows and columns from a given reference. So OFFSET($A$1,1,2,5,3) says “Go one cell down from $A$1 (which takes us to $A$2), then two cells across (which takes us to $C$2) and then return a block of cells 5 down from $C$2 and 3 across from $C$2 (which gives us the range $C$2:$D$6)
- Indirect Returns the reference specified by a text string. References are immediately evaluated to display their contents. So Indirect(“$A1”) tells Excel “Go look in cell $A$1, and tell me what’s in it”.
So why would that mean they need to be volatile? Because Excel constructs dependency trees based on cell references.
- INDIRECT() has an argument that is constructed out of text – e.g. INDIRECT( “$A1”). This might look like a cell reference, but it is not. In fact, the argument of an INDIRECT function might equally look something like this: INDIRECT(“$B”&$C$9-2).
- OFFSET() takes numerical arguments, which point to a cell reference, but are still just numbers.
- In order for these to form part of Excel’s dependency tree, the Excel dependency tree algorithm would have to first evaluate text like INDIRECT( “$A1”) or the numerical arguments like OFFSET($A$1,1,2,5,3) in order to determine what the associated cell reference actually is, before adding it to the dependency tree. Maybe the Excel obviously made the call that rather than introduce this extra step where these two functions are concerned, they may as well just make both functions fully volatile.
But given that you can set up INDEX() do much the same thing as OFFSET(), why doesn’t INDEX need to be volatile too? I imagine it’s because INDEX uses range arguments, whereas OFFSET uses numerical arguments. So Excel can extract these range arguments directly from an INDEX() function when building/amending the calculation dependency tree.
Note that INDEX() is what’s called semi-volatile, meaning it gets recalculated when the workbook opens.
And also note that any formulas used in conditional formatting effectively become what Charles Williams calls super-volatile: they are evaluated each time the cell that contains them is repainted on the screen (which happens say if you use the scroll bar to move the ‘view’ up/down or left/right), even in Manual calculation mode. But because no other formulas are ‘downstream’ from conditional formats, then only the conditional format formulas themselves get recalculated. So if you’ve got simple conditional formatting rules, you won’t notice any delay.
More info:
I’ll talk about alternatives to using volatile functions in a series of upcoming posts. But meanwhile…if you’re not feeling too sluggish…then check out these great links from Excel MVP Charles Williams.
- Excel 2010 Performance: Improving Calculation Performance
- Smart Recalculation
- Volatile Excel Functions
- Excel Dependencies
- Evaluation Circumstances
- Writing efficient VBA UDFs Part 10 – Volatile Functions and Function Arguments
Pretty much everything I’ve covered in this post came from Charles’ writings, so I’d like to acknowledge the work he has done in explaining this complex subject to countless Excel users over the years. Charles also sells a great add-in called FastExcel for profiling Excel calculation performance and memory useage – so be sure to check that out if you want to get serious about diagnosing volatility issues with your own Excel models.
You may also be interested in Jan Karel Pieterse’s RefTreeAnalyser utility, which among other things allows for easy Auditing of formula dependents and precedents, helps you trace errors, and will let you time your workbook calculation for each worksheet to find bottlenecks as well as check columns for formula inconsistencies. Jan Karel has a free demo version with limited functionality, if you’d like to take it for a spin.
Let me know your thoughts in the comments
This has been a particularly taxing post to write. So if you found this article helpful, please let me know below in the comments. If you’re not following something I said, or can think of a better way to say it, then let me know that too.
About the Author.
Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:
=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))
That’s right, pure #VALUE!
Find out more at http://www.heavydutydecisions.co.nz
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!