Got Tricky Data? Try Excel Magic Tricks!

Got data? Then you’ve got needs – the need to make your data make sense. Microsoft Excel has amazing built-in magic tricks, and all it takes is just a few clicks!

Information is important. Information is critical. Information is what keeps the world moving. Life can depend on having the right information – especially the life of a business. Information is the lifeline for the professional world.

If you think we’re overestimating the value of information, try not being able to access what you need when you need it. There is a reason data is such big business today. Data storage and protection of stored data have become a $30 billion industry, with several large players in the game, including Microsoft and Amazon. Growth is expected to explode in the next five years, to nearly $100 billion by 2022. Brands have embraced the flexibility of cloud data storage with its scalability and efficiency.

The growth of cloud storage is two-fold: brands are recognizing the ease of storing data off-site, and the cost efficiency of increasing storage needs over time due to their own growth. An invisible bonus is the decreased need for in-house redundancy, with data back-ups taking more space on servers and requiring staff to oversee these processes. What do we mean by “in-house redundancy”? We’re talking about how critical data is to the success of organizations, small and large. Think about your data – your information. If your data suddenly disappeared, how long would you be able to continue normal operations? Yes, you could revert to the most recent data back-up with minimal impact – hopefully – but there is also the question of how the data loss occurred. But these are considerations better left for your cybersecurity team.

Back to your data – your information. You need your data. Your data gives you great insights into your brand performance, your customer base, your revenue, and your operational details. But how do you make sense of your raw data? If your information is stored in a Customer Relationship Management (CRM) software solution, you likely already have custom reports established to give you the details you need. These CRM solutions are often expensive and require further customization to get the details you need in a way that is meaningful to you and your organization. Not very helpful, is it?

• A Customer Relationship Management (CRM) software solution allows for the continual data analysis to improve customer relationships and provide the insights needed for customer retention and revenue growth.
• A CRM often pulls data from multiple sources to compile reports, like a company’s website or site analytics account for traffic metrics, various marketing efforts like direct mail or email campaigns, or even inbound marketing campaigns to measure response rates, and even social media profiles. All of this may be incredibly helpful, or it may be presented in a way that doesn’t have the right information and meaning that you are looking for to gain particular insights.

Don’t overlook the basics!

Microsoft Excel is a fantastic tool for data references and building tables to find deeper meaning from your data. Microsoft Excel helps you take raw data and organize your information in a way that holds meaning for you. You want to manipulate your data so that you can get helpful insights from the information within. For example, you’d like to identify the group of customers that purchased or canceled with your business within a certain date range. You may be able to customize a report somehow from another platform, like a CRM, but if you either do not have one of these costly software solutions, or just need this information quickly and want to avoid the time it may take to set up a custom report for these details, Microsoft Excel is the perfect solution for this purpose.

Data presentation is rarely perfect for your needs – without that costly and time-consuming customized reporting solution we mentioned earlier. A couple of quick keystrokes can help you get what you need in Microsoft Excel. For example, if you need to quickly ascertain the number of customers who purchased or canceled service within a set month and year, you have multiple options in Microsoft Excel.

First, your raw data probably isn’t formatted how you need it to be. What can you do?

• Text to columns feature:
  • In Microsoft Excel, users have the opportunity to import data in a comma separated values file, a CSV, and then use this feature to separate the information into clean column format.
  • If your data is somewhat clean and consistent, this is one option. Otherwise, this is going to be manual and laborious.
• How are your VBA coding sills? Can you write a macro? This seems like overkill, but it’s one way to get your raw data into an organized format. If you’re going to put this kind of time into your data, you may as well consider the customized reporting features we discussed earlier.
• Sort and count manually
  • This is the least desirable option, as it’s the most manual and will take you the longest – isn’t the goal of technology to improve our lives and increase efficiency and productivity?

So, let’s look at our realistic option:

The super handy formula bar in Microsoft Excel is your best friend here. Follow these steps to get the magic number you need using this “magic trick” in Microsoft Excel.

Step 1: Ensure the date column is formatted properly. Excel defaults to a standard presentation for dates: MM/DD/YY. If your dates vary in presentation, this is an easy step that will save you a headache in later steps!

Step 2: Sort, or don’t sort. This is the magic of Microsoft Excel!

Step 3: A wonderful tool called the COUNTIFS formula is our best friend here. Using this specific example, if we’re looking at a specified data column in B, rows B1 through B50, and the date range is the month of April 2018, we can use this formula:

=COUNTIFS(B1:B50, “>04/01/2018″,B1:B50,”<04/30/2018”)

What this formula does is tell the spreadsheet to return the total number – not the specific rows or cells, mind you – of rows that include customers that are within the date range at you are looking.

Users will either need to update the cell range for the total count (“B1:B50”) or the date range (“04/01/2018” and “04/30/2018”) to update the total count.

• If users will need to reflect on this data more often than just once, we recommend users set up a separate tab or sheet within the file for each month, even if the full data is the same, to simplify the process. This will help users compare months at a quick glance.

There you have it.

Quick note: adding more rows, etc., and this count will update accordingly and automatically. Also, sorting won’t impact the formula, just the display.

Microsoft Excel has a huge number of neat little magic tricks designed to help users – and it just takes a bit of manipulation to make your data “talk” to you and tell you what you need to know.

Written by Carl Fransen

posted on May 2, 2018

Carl Fransen

My passion is to make my mark on the world in a positive and lasting way. I want to set an example for my son that his father can compete with integrity in today’s world, be very successful, and leave the world a better place for him.

Combining my technical/business-based education with a long career steadily progressing up the corporate ladder, I decided to build a company that held true to my values. So, I founded and designed the next generation of IT support firm: CTECH Consulting Group Inc. We are a completely automated, cloud-based IT company designed to compete against any other IT firm without the overhead. We promote a lifestyle to all our staff where they can work anywhere, at any time, access any information on any device that is relevant to their job, and collaborate with anyone they want to.