Common mistakes when setting up a Microsoft Excel workbook
Let’s imagine that you are able to create a Microsoft Excel workbook from scratch. You want to set it up for long-term, future success, right?
If the amount of data is going to grow over time, you need to think a few steps ahead to make sure that you are “future-proofing” how you will work with your workbook and the data. There are some easy steps you can take to ensure that the workbook is effective now and for the long term.
Use worksheets wisely
It’s important to think about the amount of data that one Excel workbook can hold. In Excel 2016, each workbook allows an unlimited number of worksheets. Your only limitation is the amount of system memory on your computer.
So, you have the ability to organize your data in countless ways. The general guideline I tell my students is to group your data to the smallest, meaningful subject that you can.
For example, if you are dealing with monthly financial data and setting this workbook up from scratch, then each individual worksheet represents one month of the year. You would have a minimum of 13 worksheets (one for every month and one for the summary of the year). Then, you can easily manage the data and not have to deal with annoying scrolling or other management issues.
Re-name every sheet use the “Summary” sheet to create summation functions across multiple worksheets easily. All the data stays live and any change on one worksheet would also reflect on others when used in a formula reference.
What to Avoid? When possible, don’t put all of your information on one worksheet in one workbook.
No blank rows. No blank columns
In an ideal workbook setup, you should avoid any blank rows or columns. The headers should go on the top of each column and the data flows from there.
Microsoft Excel has many built-in data analysis tools, Pivot Table reports, and much more. Many of these rely on the proper setup of the worksheet, without blank rows or columns. As one solid data set, Excel treats this data as one range and takes some solid assumptions about what you’re trying to do with it.
With blank rows or columns, you have to be much more careful using some of the tools and ensure that the program understands what range(s) you are trying to work with. Be sure to “scrub” your data (manually look for blank rows and columns) before you try and use any advanced tools as you may get some unexpected results.
If you are stuck using data that has blank rows and columns, you can try and separate into individual worksheets to keep the data better organized and manageable.
What to avoid? When possible, no blank rows or columns in your data set.
Naming Conventions. Label your data
Related to these first to Excel mistakes to avoid, you should also label your data in a concise, yet descriptive way. There is no real limit to the text entered, so use it to your advantage.
If you find that your label names are getting excessively long, you can always adopt a naming convention that works for you and your company. A naming convention is a short prefix and naming formula to follow.
For example, maybe you start a label with a 3 digit code of the department instead of a full name. Maybe you use “PROC” in place of “Procurement”. You get the idea. Keep it simple and decipherable for all.
What to avoid? Unlabeled data, long labels, or difficult naming conventions.
The last mistake to avoid in Microsoft Excel is doing repetitive tasks unnecessarily. If you find yourself using the same commands in the Ribbon, then you should add that command button to the Quick Access toolbar (e.g. Open, Print, Save, etc).
The Quick Access toolbar is available in most Microsoft Office products and provides easy customization for your personal copy of this program. Click the small arrow next to the toolbar to see popular commands you can turn on and off easily.
However, you can also choose “more commands” and see a menu with every command in Excel available to you to add to the toolbar. Once there, you can right-click to remove at any time as well. Instead of wasting your time searching for that hard-to-find command, use the toolbar to your advantage.
Taking it a step further, you can also take advantage of Macros. A macro is a saved series of tasks/commands. This is definitely an advanced topic and you should do your research before you get started with macros.
However, if you notice that you are doing the same commands repetitively, a Macro can be a huge time-saver. What can take you 10-15 clicks can be reduced down to one.
What to avoid? Avoid repetitive tasks by using the Quick Access toolbar and Macros effectively.
For more information on Microsoft Excel, click here for our class page or fill out the following form: