Excel Data Analysis
It seems like in today's world, big data is all around us. Many people I work with have a lot of reports they have to create based on the data they receive each week or month.
Microsoft Excel provides several ways to analyze and interpret data. The data can be from various sources. The data can be converted and formatted in several ways. It can be analyzed with the relevant Excel commands, functions, and tools.
Excel allows you to analyze data using Conditional Formatting, Ranges, Tables, Text functions, Date functions, Time functions, Financial functions, Subtotals, Quick Analysis, Formula Auditing, What-if Analysis, and so many more.
Data Analysis with Excel:
Data Analysis is a process of collecting, transforming, cleaning, and modeling data with the goal of discovering the required information. The results are then communicated, suggesting conclusions, and supporting decision-making.
But, how do you know what type of function to use for your data?
Well, it really depends, but if you learn a few ways to format your data, you will start to determine the best way to showcase the data in a way that tells a story. And, that's what data is all about - telling a story so that someone in the organization can make appropriate decisions.
When collecting data, you must first look at the format. Is your data in a comma-delimited format? an excel file? a word file or a text file?
Once you know what format your data is coming to you, you can best determine how to import that data into or open with Excel.
Either way will usually work, but importing can give you more options - allowing you to tell Excel what to do with the data instead of Excel telling you what to do.
To begin, Open Excel.
Depending on what version of Excel you have, you may not see exactly what I'll show you here. I'm using Excel 2019, but many of the newer Excel versions follow these steps.
Click on the Data Tab and you'll see this Ribbon has many options.
Selecting "Get Data" gives you quite a few options, but you'll also see that within this drop-down menu, some of these options are buttons on the Ribbon directly to the function in the sub-menu - such as "From Text/CSV" file.
For this example, we're going to work with an external data source, but I will create future tutorials showing how to get data from other sources as shown here.
For our example, we'll say we have a CSV file called "SampleDataFile."
We'll choose "From Text/CSV" from the sub-menu and then locate our file.
Then click on "Import" at the bottom right of the screen.
The data file will open in a table format, giving you some options.
In the File Origin drop-down, you may select and change the file type. For the most part, I would accept Excel's default, but there are so many options here, I'm going to give you a quick overview. The File Origins drop-down will change the data you see in order to match the File Origin type. Many of these, as you will see, will change language and the data appearing on this screen.
The 2nd drop-down "Delimiter" allows you to tell Excel the piece of the data that separates the data into fields or columns. Again, I would go with what Excel determines is best, but if you need, you may click the drop-down and change the delimiter here.
And the 3rd drop-down "Data Type Detection" allows you to select the amount of data to have Excel base its decisions on. Again, we'll go with the default.
If the data looks correct, click the "Transform Data" button in the lower right corner of this window.
This option opens the Power Query editor.
In our example, you'll see that Excel understood that we had Headers for our Data Columns.
You can see how it changed, and correct if it didn't change correctly, in the Applied Steps area on the right side of the screen. For ours, when we change that to "Source," it takes our headers back into the data, which we did not want. So, we'll keep the "Changed Type" option that Excel already completed for us.
If you believe your data is already in a simple format, like ours, then you could select the "Load" option from the original import screen, shown above.
We will do a future tutorial on Power Query, but I wanted to show you this window as there are so many things you can do with the data using Power Query, depending on how your original data is setup and what you might want or need to do with it.
Once we select "Load," the data is brought into an Excel file and ours automatically shows the Filters applied to the Header row.
In this format, we really don't see anything we can "analyze" about the data, so let's go through a couple of things you can do to start making your data tell a story.
Since this data is basic information on clients (name, group, phone number, email, etc.), there isn't a lot of analyzing we might want to do. We can, however, sort and/or filter this data using the "Data" tab.
We've don't tutorials on filtering and sorting, so I'm going to bring up a different data source to show you some other functions to help you analyze your data.
Let's look at this new data source: ChartSampleData.
It contains Sales by Region for a sample company.
Think about what this data might mean to the company. Is there a particular region that is struggling? Is there a typical time of the year that all regions may do better? Thinking through what the data could tell you, will help you determine how to best tell your story.
Since this is sales, let's look at this in a chart format.
We'll select "Insert" and then "Recommended Charts" and select one that Excel recommends.
We'll select the first Clustered Column chart because this appears to show me the sales by region and then by month in a format I've seen before.
To me, this is a very busy chart and I don't see a story in all of the colors.
If there is a particular region that is standing out with the highest amount of sales, you could highlight that. It all depends on what you see, but it's your job as a data analyst to determine the story and how to best visualize it.
I do notice that one of the regions appears to be decreasing in sales. Even though some of the regions decreased a bit, the SE region appears to have continued to drop much more than the others (it is the Gray bar, in case it's hard to see on this chart).
So, instead of showing this colorful chart, let's show that region as one color and all others as a separate color.
Using the Design Tab, I selected "Colors" and changed the entire chart to Gray Scale.
I then clicked on one of the SE region columns so that the SE data set is highlighted.
I then clicked on "Format" and changed the "Shape Fill" to a bright orange.
The chart now looks like this:
Can you see how it brings the viewer's eyes to the SE region so they can see what might be going on? This is now telling a story - you're showcasing that the SE region is trending downward or differently than the other regions.
That's just one example of analyzing data.
Now let's look at Conditional Formatting.
Use conditional formatting to help you visually explore and analyze data, detect critical issues, and identify patterns and trends.
Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets that correspond to specific variations in the data.
A conditional format changes the appearance of cells on the basis of conditions that you specify.
To add conditional formatting, click the "Conditional Formatting" item on the Home Tab.
You'll notice many options to select from. If we're trying to highlight the downward sales trend of the SE region, in our example above, then we may want to select the "Highlight Cells Rules" and then "Less Than..." option.
Depending on your data, Excel could recognize a trend. But, you can change the settings that you'd like to call out.
Notice in our example, Excel thought that we might like to highlight data less than 22,500 in sales.
Leaving that option, you may also notice that there is another option on this window - in the drop-down you will see options for coloring or "conditionally formatting" your data. You can even select a custom format, if you'd like.
Again, data analysis is all about telling a story so that people can know what they might want or should do with the data.
These are just some basic ideas on how to analyze data and how to visualize data in Excel. I'll continue posting more advanced ways to analyze your data in Excel.
The main thing I want you to remember is that it's all about "telling a story" - we aren't just making things look colorful because Excel defaults and puts a lot of color on our data.
If you're interested in an entire course on Excel Pivot Tables, Analyzing Data, Macros, and more, CLICK HERE for a 50% discount.