As a small business owner or employee, there’s a good chance you’re using Excel to do some part of your job. Once the purview of accountants, it’s become a respected tool for many uses: reporting, data management, key performance dashboards, and more.
Having been an Excel consultant for over 10 years, I can tell you that most people are not using Excel to their best advantage. In fact, I’d say that 90% of people who use Excel could do more with it. I see it time and time again. People do things the hard way in Excel, or they are only using a fraction of its capabilities.
It makes sense, since there is a LOT to Excel. You could spend many hours learning about Excel, and not run out of topics. Personally, I’ve spent years teaching Excel classes and programming Excel applications, and I’m still continually learning new things about it. It’s kinda the Grand Canyon of software – so vast, you’ll never see it all.
That doesn’t mean you shouldn’t try. I’m going to challenge you to learn something new about Excel every week. There are innumerable opportunities for learning – blogs, YouTube videos, podcasts, books – even the “Help” menu in Excel can provide lots of useful info. Consider
signing up for a tip-of-the-week email, and spend a few minutes reading it when it comes in. I know its tough to take the time to invest in learning, but the return on that investment may help you run your business more efficiently, freeing up your time and energy for things you’d rather be working on, whether that’s developing new products, improving customer service, or going kayaking, which, personally, would be my choice.
Let me whet your appetite by telling you about some of the useful and productive things you can do with Excel.
Create a Control Panel
If you’re managing multiple sheets in Excel, it can be cumbersome to find the sheet you’re looking for, or to rearrange them in a meaningful way. A control panel can help. This would be a pane that says on top of all of your worksheets, so that you can always see it, and it would list all of the worksheets in your document. You’d be able to click on any worksheet to jump to that sheet, and you’d be able to drag-and-drop to rearrange them.
Data Validation
If you’ve got people filling out forms in Excel, then you know that people will make mistakes on them, costing you time and energy in correcting them. Why not let Excel head that off? Excel’s got some validation features that can ensure a field is numeric, limit users to specific choices, and make sure that required fields are completed.
Save Selected Worksheets as a PDF
Do you need to send a collection of worksheets to a client? It’s possible to select a set of worksheets, and save them all as a single PDF document, which can then be sent to the client.
Implement Spell Check on Specific Ranges
When you spell-check a worksheet, Excel will spell check either the selected cells, or the entire worksheet. But what if you’d like to always spell-check specific ranges on your worksheet? Maybe you’d always like to spell check cells A1 through C12 plus D18 and E24, for example, but skip the spell check on all other cells. This sometimes happens because you’ve got industry-specific jargon in other cells, and you don’t need them spell checked. It’s possible to create your own spell check function that will only spell check specific ranges on your worksheet, or even on multiple worksheets.
Automate tasks with macros
Excel has a built-in macro recorder which allows you to record a set of tasks and play it back later. This comes in handy if you have tasks you perform over and over again. Let’s say that I create a sales report each month, along with an associated chart. I could create a macro to generate the chart automatically, so that I don’t have to do that work each time.
Increase flexibility
You can use named ranges to make your Excel applications more durable and flexible, especially when used by those without much Excel experience. By using named ranges, you can create an easy method of drawing attention to a set of data that you want viewers of your spreadsheet to look at. Let’s say you’ve got a workbook with several sheets, one for each of your regions. You can create a named range called “HighestAnnualRevenue”, referring to whichever region has the highest annual revenue. Next month, if that changes, all you need to do is change the “HighestAnnualRevenue” range to point to a different region. Now you just train your Excel users to look for that named range, and they no longer need to fish through all of the worksheets to find the best performer.
Create a customizable dashboard
If you’ve got a set of data that you distribute to several people, it’s possible that each of those people wants to see a customized view of the data. There’s no need for you to generate several customized copies of the data. Instead, give them a dashboard that lets them filter the data, change the sort order, apply subtotals, and format, all without asking them to learn these features in Excel. A dashboard can offer all of these features at the click of a button.
I could go on and on with this. Excel is often scoffed at by IT departments as an entry-level data management tool. But that’s an archaic attitude. Take it from someone who’s work in IT for over 30 years, and seen the inner workings of many different tools and languages. Excel has evolved into a powerful tool that can be leveraged by those without a background in computer programming. Learning to make better use of Excel may very well help your company to overcome obstacles that are limiting your success. So, what are you waiting for? Learn something new about Excel today, and you just might find that it helps your company to thrive.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of business experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2021. All rights reserved.