Imagine you’ve been given a shiny new laptop at work with a fresh version of Excel installed. What settings are best for financial modelling? Here we list out seven settings that you might want to change in Excel, bringing more happiness to your modelling. So today it’s “7 steps to greater Excel happiness”!
Selecting the best settings for Excel financial modelling
The first thing to do of course is find your Excel options. In your menu go “File” and then pick “Options” from the list.
If you know a bit about financial modelling you’ll probably know your way around Excel’s menu items. You might be getting into Excel keyboard shortcuts and you might know that you can drive all your menus starting with the awesome “Alt” key. So you can also find your Excel options by using your keyboard shortcuts and pressing “Alt” “F” “T” one at a time.
1. Disable Excel quick analysis toolbar
Having options pop up while you work (when you know where to find them anyway) is probably going to irritate you. You don’t want to be plagued by annoying pop ups asking you whether there’s something you want to do.
Excel we’ll tell you when we want to do something thanks. Stop nagging us. And when we want to do something we’re probably going to be using our keyboard shortcuts starting by pressing the mighty “Alt” key. So, if you’re someone who’s getting serious about their financial modelling, get into your Excel options and under “General” please turn the first two off.
2. Remove background error checking
You’re a confident modeller aren’t you? You know some of the basic best-practice guidelines for financial modelling. You know that you should make formulas the same across your Excel spreadsheet if you can (if only so you can use the “Ctrl R” shortcut to fill right all day). You don’t need Excel reminding you of that. You’re just going to find those little green error messages distracting and patronising.
Go ahead, live dangerously. In Excel options under “Formulas” turn error checking off. Annoying little green notes that remind you of the basics will hastily disappear.
3. After enter move down – or not?
OK now we’re into Excel’s advanced settings and you might take a little convincing on this one. But we’d recommend persisting with it for a week to see what you think. There is some logic to what we’re saying here.
In Excel we’re so used to inputting data, pressing enter and then having Excel go down a cell that it’s hard to want to change that setting. The default setting helps speed up data entry but in financial modelling what we’re more likely to be doing is working with formulas. So once we enter a formula Excel moves down and then we (perhaps using our keyboard arrows) move back up and check that we got the formula right. So it’s up and down all day entering and re-checking. Why not try turning the setting off? It will take a bit of getting used to.
In fact if you’re in a hurry with data entry on most keyboards you can achieve what you want there (moving down into the next cell) by pressing the down arrow instead of the enter key. So what we’re really trying to do is give you the best of both worlds (how cool is that?). If you turn this setting off you can press enter for your financial modelling, giving you the chance to review the formula you just built. For data entry, after inputting each piece of data, use the down arrow. Brilliant.
4. Turn fill options off
You’re an Excel maestro. You know how to copy and paste (“Ctrl C”, “Ctrl V”) and you even know how to paste special (“Ctrl Alt V”). You know how to fill (“Ctrl R”) and you’ve even got a few advanced mouse moves up your sleeve.
You really don’t need those annoying little fill options popping up all the time waiting to get tripped over or clicked on by mistake. Get rid of them. They’re history.
5. Disable hardware graphics acceleration
While you’re messing around with your Excel settings, disabling hardware graphics acceleration would probably be a good idea. You might be operating with a shiny new laptop at the moment but once time drifts on and after you’re thrown a huge model this one will help speed up your machine a good fraction. So disable hardware graphics acceleration. Go on, do it. Having changed this setting, if you shut down Excel and re-open it you might find Excel suddenly opens in a flash. Disabling hardware graphics acceleration did that for you. How very satisfying.
6. Open the developer tab
Do you think you might want to include one of those cool drop down boxes every once in a while, if only to show your boss that you know a thing or two about financial modelling? You’ll need to open Excel’s developer tab then.
From the developer tab you’ll get access to those groovy little forms (don’t use very many of them, they’ll just end up confusing people, save them for special occasions) and even macros. OOOhhh. Ahhhh.
Here’s a short video showing you how to open the Excel developer tab:
7. Turn animation off in Windows
Have you noticed how, in the laterhttps://www.financialtrainingassociates.com/wp-admin/post.php?post=29624&action=edit# versions of Excel, if you make a change in a model all the numbers jump around like you’re playing a slot machine? Someone somewhere must have come up with that idea and thought it was absolutely brilliant. But having numbers bounce around like that on screen is going to be sucking processing power. If you really want to see what’s changing in your model set calculation to manual (“File” > “Options” > “Formulas”) and then after modifying your model press “F9” to force manual calculation and see the numbers change. That’s how the pros do it (did you know that “F9” will send/ receive your Outlook mail for you as well?).
Turning animation off is not an Excel option. This one’s a system wide Windows option. So find your control panel and within that search for “Advanced”. You’re looking for “View advanced system settings”
After clicking on “View advanced system settings” you need the “Advanced” tab. Under “Performance” click on “Settings”.
Make sure the first box on the list is unticked. That will stop the numbers from bouncing up and down in front of your eyes and speed up your computer another enjoyable fraction. You might just be grateful for that if one day you end up crunching your way through an enormous great big financial model.
Here’s a short video showing you how to turn animation off:
Changing your world with advanced Excel settings
Of course we’re not pretending that we’re changing the world by fiddling with your Excel settings. But if you’re taking financial modelling seriously it really is worth exploring what you can find in your advanced Excel options and perhaps tweaking a few settings to suit. If you’re spending hours on your modelling making Excel’s options suit you is going to make your work just a little more satisfying. Playing with your Excel settings will help you feel happier on the inside. That’s got to be a good thing.