You know it’s true: Spreadsheets have errors like dogs have fleas. It is generally accepted that nine out of every ten spreadsheets suffer some error, and consequences can be severe:

  • A cut-and-paste error cost TransAlta $24 million when it underbid an electricity-supply contract.
  • A missing minus sign caused Fidelity’s Magellan Fund to overstate projected earnings by $2.6 billion (yes, billion) and miss a promised dividend.
  • Falsely-linked spreadsheets permitted fraud totalling $700 million at the Allied Irish Bank.
  • Voting officials reported spreadsheet irregularities in New Mexico and South Africa.
  • A new drug introduction was delayed several months by an untested macro, costing the pharmaceutical company profits and its patients misery.

9 Quick Ways to Idiot-Proof your Spreadsheets

1.Use Freeze Panes So Your Audience Always Understands What They’re Looking At

This one is a must have.

Honestly, it’s painful to open up “completed” spreadsheets which don’t have Freeze Panes enabled.

Freeze Panes allows you to always have headings fixed at the top or left of the Excel window, so headings are always visible when you scroll around the page.

freeze

Without Freeze Panes, your audience will have to scroll back up to the top of the page to remember what each column of data represents.  If you’re in a meeting or presentation, this can waste precious time and cause your audience to lose focus.

To freeze the top row, use the shortcut Alt-W-F-R.  To freeze the first column, use Alt-W-F-C.

2.Hide unnecessary sheets

If you have sheets in your workbook that your audience shouldn’t alter, then hide them.

Good examples of this are any sheets that contain reference data, lists, named ranges, or anything else that only exists to complete a formula or is an intermediate step in your work.

You can hide sheets by right-clicking on the sheet name in the bottom left corner of the screen, and selecting the Hide option:

hide

3.Hide rows/columns that aren’t in use or unnecessary

If you have some columns of data that don’t really need to be visible, then hide them!

Use Ctrl + 0 to hide the current column, or Ctrl + 9 to hide the current row.

4.Use consistent formatting to advise which fields should be changed (if any)

Some Excel workbooks are designed so that the user should edit a small number of values, to get the results they want.

They might want to select a department number, a specific date or period, or enter their own name.

And you have probably set up the file so that those values are included in formulas somewhere else in the sheet.

The potential issue here, is that your audience should only be playing around with the cells you specify.

Here’s a basic measurement calculator I created in Excel, without any formatting.

consistent

This file is helpful because you enter in your imperial measurements on the left, and the relevant value in the metric system is calculated in column E.

But there’s one issue.

It’s not obvious to see which cells should be changed, and which ones have formulas, right?

The file looks really bland and boring, too.

Now let’s look at the same file with some basic and consistent formatting, to aid the user in only modifying the relevant cells.

consistent

5.Use Data Validation messages to give hints to your audience

If your sheet is using drop down menus to select specific values, then you might want to give some hints relating to what values should be entered.

When you open the Data Validation options (shortcut: Alt-A-V-V) in Microsoft Excel, there are two tabs that can give hints to your users.

In the Input Message tab, you can cause a note to appear whenever the relevant cell is selected.

Here’s an example:

data

6.Go to the first sheet and/or top left cell before saving

Not too many people are aware of this, but Excel remembers which sheet and cell(s) are selected when you save your workbook.

And as a result, the next time you open the file, you’ll pick up exactly where you left off!

7.Remove any formulas references to other files

Referring to data in other Excel workbooks is sometimes necessary, but if you’re sending a file to your boss or your teacher, then those file references probably won’t work when they open it up.

It’s not very professional and could potentially lead to privacy issues if your potential customer sees a formula containing a filename reference like “G:\SecretManagementDrive\AnnoyingClients\CompanyABC\testfile.xlsx”.

The best option here is to override the data by copying the data and pasting it in the same location but using the Paste Special option and selecting “Values”.

copy

8.Name all sheets, tables and graphs

Nobody wants to see Sheet14.  Rename it to what it actually represents!

Don’t worry, any formula references will automatically update to refer to the new name.

9.Keep the formulas so your calculations can be understood

As long as you have the original data in your Excel workbook, it’s always a good idea to retain the formulas so people can understand your work.

The main reason for this is that people will be able to retrace your work and understand your file much easier.  And if someone wants to make a small adjustment, all your subsequent calculations will still apply.

To join the conference or see a replay, order a DVD or transcript, or read more
Click Here
 


45 Comments

zqmvan · June 15, 2018 at 2:16 pm

buy cialis canada pharmacy
best price cialis online http://canadian-pharmasy.com

Teddy Scheerer · June 21, 2018 at 11:06 pm

I additionally believe that mesothelioma is a exceptional form of many forms of cancer that is normally found in these previously exposed to asbestos. Cancerous tissues form while in the mesothelium, which is a safety lining that covers almost all of the body’s organs. These cells normally form in the lining of the lungs, tummy, or the sac which actually encircles one’s heart. Thanks for giving your ideas.

Reinaldo Nickolich · June 27, 2018 at 1:18 am

With thanks! Valuable information!

levitra vs viagra vs cialis · June 28, 2018 at 2:25 pm

Everyone loves what you guys are usually up too.

This sort of clever work and coverage! Keep up the awesome works guys I’ve included you guys
to my personal blogroll.

is viagra safe · June 28, 2018 at 3:29 pm

I have observed that in the world the present moment, video games are the latest phenomenon with children of all ages. Many times it may be extremely hard to drag your son or daughter away from the activities. If you want the very best of both worlds, there are several educational video games for kids. Great post.

costco cialis · June 28, 2018 at 4:33 pm

Hello would you mind letting me know which hosting company you’re working with?
I’ve loaded your blog in 3 completely different internet browsers and
I must say this blog loads a lot quicker then most.
Can you recommend a good hosting provider at a fair price?

Kudos, I appreciate it!

when did viagra come out · June 28, 2018 at 5:40 pm

Ahaa, its good dialogue concerning this piece of writing here at this web site, I have read all that, so at this time me also commenting
here.

cialis price per pill · June 28, 2018 at 6:48 pm

You made some really good points there. I checked on the internet for more info about the issue and found most people will go
along with your views on this web site.

taking viagra without ed · June 28, 2018 at 7:51 pm

Hello, i feel that i saw you visited my weblog so i got here
to go back the prefer?.I am attempting to to find issues
to enhance my website!I assume its adequate to use a few of your concepts!!

what works better viagra or cialis · June 28, 2018 at 8:54 pm

I have been browsing on-line more than three hours as of late, yet
I never discovered any attention-grabbing article like yours.
It is beautiful worth sufficient for me. In my opinion, if all web owners and bloggers made just right content as you probably did, the net shall
be much more helpful than ever before.

over the counter viagra walmart · June 29, 2018 at 7:15 am

I just couldn’t go away your web site prior to
suggesting that I extremely enjoyed the usual information an individual
supply to your visitors? Is going to be again ceaselessly to check out new posts

when will generic cialis be available? · June 29, 2018 at 8:24 am

I am sure this piece of writing has touched all the internet
visitors, its really really fastidious paragraph on building up
new webpage.

Ruth · June 29, 2018 at 10:04 am

Nice post. I was checking continually your blog and I am impressed! Really useful information specifically the last part? I maintain such info much. I had been looking for this certain information for a very long time. Thank you and best of fortune.

Zenaida C · June 29, 2018 at 12:13 pm

There’s definately a great deal to find out about this
subject. I love all the points you’ve made.

Candace Galimi · June 29, 2018 at 2:44 pm

With thanks! Valuable information!

does medicare pay for cialis · June 30, 2018 at 1:10 pm

Way cool! Some extremely valid points! I appreciate
you penning this write-up plus the rest of the site is really
good.

does viagra make your penis bigger · June 30, 2018 at 2:13 pm

I’m extremely impressed along with your writing abilities as neatly as with the structure for your blog.
Is this a paid theme or did you customize it yourself?
Anyway stay up the nice high quality writing, it is uncommon to peer a
nice blog like this one these days..

    Nia · July 4, 2018 at 4:53 pm

    Its not a paid.

Ophelia Cowie · June 30, 2018 at 11:47 pm

With thanks! Valuable information!

when viagra doesnt work · July 1, 2018 at 1:39 am

I’ve been exploring for a bit for any high quality articles or blog posts on this sort of area . Exploring in Yahoo I finally stumbled upon this website. Reading this info So i’m glad to exhibit that I’ve an incredibly good uncanny feeling I found out just what I needed. I such a lot undoubtedly will make sure to don’t overlook this website and give it a look regularly.

cialis free · July 1, 2018 at 2:47 am

I have been browsing on-line greater than three hours lately, yet I by no means discovered any attention-grabbing article like
yours. It is lovely price enough for me. In my opinion,
if all site owners and bloggers made excellent content material as you did, the web will probably
be much more useful than ever before.

viagra manufacturer · July 1, 2018 at 11:25 am

Wow, this article is pleasant, my sister is analyzing
these kinds of things, so I am going to tell her.

side effects from cialis · July 1, 2018 at 1:05 pm

There’s certainly a lot to learn about this topic.
I love all the points you made.

how long before viagra works · July 2, 2018 at 11:11 am

I seriously love your blog.. Great colors & theme.
Did you build this web site yourself? Please reply back as I’m planning to create
my own personal blog and want to know where you got this from or just what
the theme is called. Kudos!

    Nia · July 4, 2018 at 5:25 pm

    ya i build this site by myself

cialis super active · July 2, 2018 at 12:51 pm

I’ve been browsing online more than three hours today, yet I never found any interesting article like yours.
It’s pretty worth enough for me. Personally, if
all site owners and bloggers made good content as you did,
the internet will be much more useful than ever before.

viagra pills · July 2, 2018 at 2:35 pm

Hello! I’ve been reading your blog for some time now and finally got the
bravery to go ahead and give you a shout out from New Caney Tx!
Just wanted to tell you keep up the fantastic work!

바카라사이트 · July 2, 2018 at 4:16 pm

With thanks! Valuable information!

바카라 · July 2, 2018 at 7:28 pm

With thanks! Valuable information!

viagra funny · July 2, 2018 at 7:31 pm

Hey there! I’ve been following your site for a while now and finally got the bravery to go ahead and give you a shout
out from Porter Texas! Just wanted to tell you keep up the good work!

is there a generic cialis · July 2, 2018 at 9:33 pm

Have you ever considered writing an e-book or guest authoring on other blogs?
I have a blog centered on the same information you discuss and
would love to have you share some stories/information. I
know my visitors would appreciate your work. If you’re even remotely interested, feel free to
send me an email.

herbal viagra green box · July 2, 2018 at 11:21 pm

Way cool! Some extremely valid points! I appreciate you writing this write-up
and also the rest of the website is very good.

cialis discount coupon · July 3, 2018 at 1:14 am

It is appropriate time to make some plans for the future and it’s
time to be happy. I have read this post and if I could I want to suggest you
few interesting things or suggestions. Maybe you can write next articles referring to
this article. I desire to read even more things about it!

cialis daily side effects · July 3, 2018 at 4:50 am

It’s appropriate time to make a few plans for the long run and it’s time
to be happy. I have learn this publish and if I may I want to counsel you few fascinating things or suggestions.
Maybe you could write subsequent articles referring to
this article. I desire to read even more things approximately it!

cheap viagra overnight · July 3, 2018 at 6:58 am

Wow, this post is nice, my younger sister is analyzing these things, therefore
I am going to tell her.

바카라 · July 4, 2018 at 11:07 am

With thanks! Valuable information!

바카라 · July 4, 2018 at 12:16 pm

With thanks! Valuable information!

where to buy female viagra · July 5, 2018 at 10:03 pm

I have been browsing online more than three hours today, yet I never found any interesting article
like yours. It is pretty worth enough for me. Personally, if all web
owners and bloggers made good content as you did, the net will be a lot more useful
than ever before.

which works better viagra or cialis · July 6, 2018 at 12:45 pm

Hi there! This blog post could not be written any better!

Going through this article reminds me of my previous roommate!

He continually kept talking about this. I am going to forward
this information to him. Pretty sure he’ll have a good read.
Thanks for sharing!

acquisto cialis online · July 6, 2018 at 2:28 pm

I am sure this post has touched all the internet people, its really really good post on building up new blog.

how long does cialis last · July 9, 2018 at 2:50 pm

cialis patent expirationcialis canadacost of cialiscialis dailycialis without a doctor prescriptioncialis from canadaside effects of cialisgeneric cialis onlinecialis for daily usebuy generic cialis onlinefree cialisliquid cialiscialis for bphcialis generic namecialis over the counterhow much does cialis costonline cialiscialis pillslevitra vs cialiscialis online canadaviagra or cialiscialis generic best pricegeneric cialis canadacialis commercialhow long does it take for cialis to workhow to take cialiscialis pillwhen does cialis go genericbuy cialis online safelygeneric cialis reviewscanadian cialisotc cialiscialis for womencialis generic datecialis samplescialis dosecialis and alcoholwhen will generic cialis be availablecialis or viagrageneric cialis indiacialis 20 mgcialis otcwhen to take cialiscialis coupon cvscialis pricescialis 10mgcheap cialis onlinewhat does cialis doviagra vs cialis vs levitradifference between viagra and cialiscialis free trial once per yearcialis online pharmacyover the counter cialiscialis 5 mgcialis 5mg pricecialis no prescriptionhow much is cialisgoodrx cialisviagra cialisdoes cialis workhow long for cialis to workdaily cialisorder cialis onlineis there a generic for cialiscialis alternativehow long does cialis take to workcialis from indiacialis 20 mg pricecialis discountcialis canadian pharmacygeneric for cialiscialis coupon walmarthow to use cialiscialis over the counter at walmartbuy generic cialiscialis dosagescialis manufacturer coupondiscount cialiscialis price walmartwhere to buy cialiscialis over the counter 2017when will cialis be genericwhen will cialis be over the countercanadian pharmacy cialis 20mgcialis daily usecialis prescriptiongeneric cialis 2017how long does it take cialis to workcialis indiafree cialis couponcialis without prescriptionwhen will cialis go genericcialis professionalcialis blackwhen to take cialis for best resultsis there a generic cialiscialis 20mg pricecialis versus viagracialis patentcialis vs levitraprice of cialiscialis patent expiration 2017buying cialis onlinewhich is better viagra or cialisbuy cialis canadadifference between cialis and viagracialis bphdoes cialis make you last longercialis savings cardcialis drugcialis patent expiration datecialis coupon free trialactive ingredient in cialischeapest cialishow to take cialis for best results5mg cialiscialis online usacialis coupon walgreenscialis half lifecialis dosage 40 mgcialis super activedoes cialis lower blood pressurenatural cialiscoupon for cialiscialis vs viagra redditbest place to buy cialis online reviewswhat is cialis used forcialis genericoviagra vs cialis forumcialis wikiprices of cialiscialis and viagra togethercialis cheapcialis 20how to get cialiscanada cialiscialis 20 mg price walmartcialis discount cardfree cialis sample packcialis bathtubwho makes cialiscialis free samplehow much does cialis cost without insurancecialis 5mg best pricecialis vs. viagracoupons for cialiscialis cost per pillfemale cialiscialis for sale onlinewhat is the difference between viagra and cialiscialis 30 day free trialbuying cialis cheapcialis dosingcialis alcoholcialis daily costcialis trialbuy cialis cheapcialis blood pressurebuying cialis online safelydoes cialis lowers blood pressureis cialis covered by insurancebest time to take cialisgeneric cialis tadalafil best buyscialis overdosehow much does cialis cost at walmartcialis vs viagra vs levitracialis 5mg reviewcialis tadalafilcialis medicationcialis ingredientscialis shelf lifewhere to buy cialis onlinecanada pharmacy cialiscialis headachefree cialis samplescialis in canadabuy real cialis onlinecialis viagracialis not workingcialis voucher programcialis on linecialis pricingcialis dosescialis 20mg how long does it take to workcialis daily dosagegeneric cialis for salebuy cialis online cheapcialis websiteviagra versus cialiscialis 5mg price walmartcost of cialis at walmartwhen is cialis going genericwhen does cialis patent expireviagra and cialisviagra vs. cialiscialis without a prescriptiongeneric name for cialislong term side effects of cialiscialis directionsnon prescription cialiscialis price cvscialis pastillacialis instructionswhy is cialis so expensivecialis price per pillhow fast does cialis workbuying cialis online usawhat doe cialis look likewere can i buy cialiscialis price walgreensbest place to buy cialis online forumcialis online cheapprintable cialis couponhow much cialis to take20mg cialiscialis side effecthow cialis workscialis before and after photoscialis 40 mgcialis reviewcialis cost walmartcialis benefitscialis active ingredientviagra vs cialis pricecialis 5mg dailywhere to buy cialis over the countercheap cialis generic onlinecialis and blood pressurecialis drug interactionscialis for daily use cost20 mg cialiscialis free samplescost of cialis without insurancewhere can i buy cialisbuying cialis online safebph cialiscialis price canadacialis logocialis walmarthow long does cialis take to kick infree cialis trialcialis buy onlineis cialis better than viagrais there a generic cialis available?cialis and bphcialis daily dosecialis price comparisoncialis price costcohow to get cialis prescription onlinecialis alternative over the countercialis online prescriptioncan women take cialiscialis for prostatecialis manufacturercialis free trial couponcialis effectshow much does cialis cost at cvscialis generic timelinedo you need a prescription for cialishow long does cialis stay in your systemwhat is cialis forhow long does cialis workcialis adcialis viagra onlinehow long does 5mg cialis lastbuy cialis online overnight shippingcialis generic costbest place to buy cialiscialis 20mg reviewhow long does it take for cialis 5mg to workhow to take cialis 20mgcan i take 40mg of cialiscialis 2.5 mgcialis copay cardcialis best pricecialis mgdoes cialis expirebuying generic cialiscialis online india10mg cialischeap generic cialisis cialis over the countercialis usesdoes insurance cover cialisdo you need a prescription for cialis in canadano prescription cialissildenafil vs cialisviagra cialis levitrafree trial cialiscialis vs viagra costcialis generic 2017cialis dosage for bphover the counter cialis walgreenscialis 5mg couponcialis commercial bathtubcialis prescription onlinedoes medicare cover cialiscialis free trial offercialis erectionorder cialiscialis substitutecialis 20 milligramhow to buy cialiscialis redditwhat does cialis look likecialis bathtubssanofi cialisviagra vs cialis reviewsside effects of cialis vs viagrawhat are the side effects of cialiscialis from india online pharmacyviagra vs cialis hardnessherbal cialisgeneric cialis from indiau.s. pharmacy prices for cialiscialis definitionliquid cialis for salecialis usehow much does cialis cost per pillhow much cialis should i takecialis 10 mgcanadian online pharmacy cialismail order cialisliquid cialis dosagebuy cialis canadianwhat are the side effect of cialisstendra vs cialisfree samples of cialisgeneric cialis online canadacheap cialis canadadoes cialis make you biggercialis 100mgcialis generic onlinehow many mg cialis should i taketadalafil vs cialiswhen will cialis become genericlowest price cialiscialis side effects a wife’s perspectiveviagra vs cialis redditdoes daily cialis lower blood pressurecialis without a doctor prescription canadabuy cialis online usawhich is better cialis or viagrahow long does cialis last 20 mgcialis informationwhat cialiscialis side effects with alcoholhow to get cialis without doctorhow long for cialis to peakcialis and viagracialis preciocialis vs viagra reviewscialis 5mg price comparisoncialis after prostate surgerycost of cialis 20mgbuy cialis online redditcialis without a doctor prescription usais cialis genericwhat is cialis taken fornormal dose of cialiscost of cialis 5mgprescription free cialisfree sample cialiscialis vouchercanadian pharmacy online cialiscialis tabletgeneric cialis available in canadahow to take cialis 5mgcialis discount couponscialis and high blood pressureis cialis a controlled substancefree trial of cialispurchase cialis on linecialis 20mg side effectspurchasing cialis onlinebuying cialis without prescriptioncialis free trial phone numberadcirca vs cialisis tadalafil and cialis the same thing?what is the difference between cialis and viagracialis 10mg priceotc cialis 2017cialis com couponspurchase cialis onlineis there generic cialisbuy cialis without prescriptionbuy cialis genericcialis online no prescriptioncialis buycialis lillyis cialis covered by medicarewhat does a cialis pill look likeviagra and cialis togethercialis 5mg price cvsis cialis safecialis discountsonline pharmacy cialischeapest cialis 20 mghow to get a prescription for cialiscialis online without prescriptioncialis prostatecialis for sale online in canadacialis back painpastilla cialisside effects cialiscialis recommended dosagecialis high blood pressurebest way to take cialiswhat is better viagra or cialiscialis contraindicationstaking cialiscialis free 30 day trialcialis dosage for edcheap cialis 20mgbuy cialis from canadacialis 5mg daily how long before it worksis 20mg cialis equal to 100mg viagracialis for mencialis daily reviewcialis for daily use side effectscialis walgreenscialis dosage 20mgbuying cialis genericgeneric cialis tadalafil 20 mg from indiacialis 30 day trialcialis effective timecan i take two 5mg cialis at oncecialis 5mg costcialis patent expiration 2016what happens if a woman takes cialiscialis 5cialis online reviewsbuy cialis in canadabrand cialishow often can you take cialisbest price for cialiswhen does the cialis patent expirewhats cialisalternatives to cialiscialis doesnt work for mebest price on cialis 20mglilly cialiscialis cvscialis 5mg side effectsno presciption cialiscialis amazoncialis softcialis discount couponpurchase cialispurchasing cialisis generic cialis availablewhen will generic cialis be available in the uscanadian cialis 5mgcialis expirationreddit cialisgeneric cialis 20mgcialis v viagracialis commercial 2016cialis otc switchcialis not working anymorecialis resultsbuying cialis from canadajon jones cialiscialis free couponcialis 20 mg price walgreenscialis samplecialis canada pharmacymax dosage of cialiscan you buy cialis over the countermixing cialis and viagracialis daily side effectswhats better viagra or cialiscialis cheapest pricecialis and lisinoprilwhen should i take cialiscialiscialis couponcialis onlinecialis genericgeneric cialiscialis dosagecialis vs viagracialis side effectscialis priceviagra vs cialisbuy cialiscanadian pharmacy cialisbuy cialis onlinecialis costcialis couponscialis free trialcialis for salecheap cialiscialis 20mgcialis reviewscialis 5mghow does cialis workwhat is cialisbest place to buy cialis onlinehow long does cialis last

Monroe Sins · July 11, 2018 at 3:23 pm

I like the helpful info you provide in your articles. I will bookmark your weblog and check again here regularly.

Tiffani Claridge · July 12, 2018 at 11:41 am

With thanks! Valuable information!

Augustine Abrantes · July 12, 2018 at 2:42 pm

It?s hard to come by knowledgeable people on this subject, however, you

Leave a Reply

Your email address will not be published. Required fields are marked *