4 Ways You’re Misusing Excel

iloveexcel-300x124

I love Excel. I mean I REALLY love Excel.  If it was possible to have a love affair with a program, MS Office’s powerhouse would be my soul mate.  Throughout the course of an average day, I have no less than 5 different spreadsheets open, each performing a different function.

Perhaps it’s because I love Excel so much that I am so incredibly frustrated when I see people misusing it.  Hey, I get it. Excel can be intimidating.  There are so many formulas and functions within the program that it can be scary.  But Excel is really simple once you know the basics, and the versatility of it is unparalleled.  But really, so few people use it in any semblance of the way it is capable of being used.

 

Here is my list of the

 

Top  4 Ways You are Misusing (or Not Using) Excel.

 

#4 Using it as glorified graph paper.

How many times have you opened up Excel, dumped a bunch of numbers into it, and then used your CALCULATOR to perform the calculations before enteringthose very same calculations into Excel? Tsk tsk tsk. Yes, I know, formulas CAN be scary.  But I promise, performing basic calculations in Excel is a piece of cake.  Even if you’re not a math whiz, as long as you know your Order of Operations, you can create a formula.  The only difference between grade 6 math class and an Excel formula is the location of the = sign; in Excel it PRECEEDS the equation, kind of like an episode of Jeopardy. (“What is 4 times 42, Excel?”)  So if you want Excel to tell you what 1+2 equals, you must enter =1+2 in a cell.

Learning how to make Excel do your calculations for you WILL save you a bunch of time.

basic-formula-clip

Yes, there are more intricate formulas, but you probably don’t need them.  You DO need to know how to make Excel perform the basics though. I could write for days on using formulas, but someone has done it for me; About.com has a wonderful tutorial on using the basic formulas here. (Want a lesson using your own data? Call me.)

 

#3 Using text boxes instead of entering text in cells.

I shudder every time I see this. If I could have a one-on-one with the person responsible for adding this feature to Excel, I would probably beat him/her over the head with a rubber mallet. Yes, I know that this enables you to enter text exactly as you want it, without being confined to the limitations of cell size, but it eliminates one key feature of using Excel: IMPORT & EXPORT.  Data entered into text boxes becomes non-existent when you import into another program.

Learn to manipulate the row and column size and use the “Merge & Center” and “Wrap Text” functions to create cells big enough to hold your text.

merge-centre-wrap-clip

Here’s another juicy tip for entering text in cells:

linebreaks-in-cell-clip

#2 Not protecting your work.

If I had a dollar for every time someone sent me an unprotected worksheet…well, I wouldn’t be writing this post.  Protecting (or locking) your spreadsheet does the obvious – PROTECTS your work.  Why would you want to do this? To prevent accidental changes.  You just spent countless hours perfecting your formulas, entering your data and making your spreadsheet “pretty”.  Do you REALLY want that to disappear with an accidental key press or click of the mouse? Didn’t think so.   Excel makes it REALLY easy for you to prevent accidental changes. It has already set all your cells to automatically lock, all you have to do is press that little “Protect Sheet” button on your “Review” tab.  You don’t even need to enter a password!

protection-clip

Want to be a little more specific about what you lock and what you don’t? Select a cell, or a range of cells, right click, then click on “format cells…”.

protection-clip2

In the Format Cells Dialogue box, click on the “Protection” tab. Check, or uncheck beside “Locked”, then click OK. Go back to your “Review” tab and hit that ‘Protect Sheet’ button!

protection-clip3

Seriously people, this is a time- (and sanity-) saving habit to get into, even if you never EVER use a password for your protection needs.

 

 And the #1 way you are misusing Excel is…

Not using it for your list-building.

If I am certain of one thing, it’s that everybody – business or personal – has a contact list.  If you are running a business, you may even be using a CRM to manage it. But do you have those contacts in an Excel database? Probably not, but you should. Most people tend to use their email client’s contact list, (such as MS Outlook, Gmail, etc.). But what if you wanted to import those contacts into another client?  If you have ever tried to export contacts from Outlook, you’ll know that doing this isn’t always an easy task.  Not only that, sometimes the data becomes corrupt in the process, or, worse yet, is only exportable in a format that you can’t use in the new software. (This is by design, I’m sure. Sneaky software developers making it difficult to switch to another developer’s software, thus ensuring they continue to receive your licensing fees.)

By building and maintaining your main contact list in an Excel spreadsheet, you eliminate these frustrations. Excel databases are easily importable into EVERY CLIENT MANAGEMENT PROGRAM.  Don’t believe me? Click on “Import” in whatever CRM you are currently using.  Try several different ones.  They will ALL have the same option: “import from Excel” (though sometimes it will use the extension name in place of “Excel”, eg.: .xml, .csv, .xmls, etc.)

Can Outlook or Gmail say the same thing?  Even if you THINK you will never need to export or import your contacts into any other program, I can almost guarantee that at some point in the future, you will. Software has a shelf-life; eventually you will find something better to use.  If you build your client database in Excel the “right” way, it will have a portability that is comparable to none.

So what is the right way? It’s using one row per contact and one column per entry category (i.e. First Name & Last Name are in different columns, as is City & Province), with all the data on ONE sheet. Why one sheet? Because most programs can only import from one sheet, not the whole workbook.  And finally, no matter how much you like “pretty”, Excel is a workhorse not a show pony. If you keep your formatting simple and readable, importing to another format will be seamless.

 

Not sure how to set up your Contact list in Excel the “right” way? Leave me a comment outlining what categories you track and perhaps you’ll find a complimentary custom-designed template in your inbox.

 

Interested in learning how to make Excel work more efficiently for you or your business? I offer training from basic to advanced, tailored to YOUR data needs.Contact me to find out more.

 

**DISCLAIMER** The tips in this blog post refer to Microsoft Excel 2010. If you are using a different version, the buttons and locations of things may be different, but the results will remain consistent no matter which version (or platform) they are performed in.

 

© Trina Waller 2016. All rights reserved.  Unauthorized use and/or duplication of this article, and the material contained within, without express written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given the post author, TW Intuitive Office Solutions, and TWIOS.com, with appropriate and specific direction to the original content.

Leave a Reply

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