https://thesis-masters.store/wp-admin/post-new.php
missing data or how to deal with dirty data
Data analysis in Excel is not only about pivot tables, charts, and VLOOKUPs. It also covers subtler skills. For example, you need to know how to handle:
- Missing data
- Dirty data
However, these are hard to learn without someone showing you. That is why I will help you here. In addition, you will need these skills in Excel.
data analysts
First, let’s talk about excel pivot tables. They are pivotal to your career. In fact, they are among the most common tools analysts use in Excel. They do two things well:
- They group things together easily.
- They display information clearly, especially for people who are not data analysts.
Bike Store Sales in Europe
Personally, I use pivot tables a lot. After all, managers and higher-ups often don’t want SQL, Python, or Tableau. Instead, they just want Excel.
To demonstrate, we will use the Bike Store Sales in Europe dataset.
date information
Here is what it looks like on my screen. First, let’s take a quick glance. Our date information includes:
- Date
- Day
- Month
- Year
demographic information
Next, we have customer age. Since this is a bike sales dataset, it also shows what people bought. Then we have demographic information:
- Age group
- Gender
- Country
- State
Besides that, we have the product details:
- Product category
- Subcategory
- The actual product purchased
profit, cost, and revenue
Furthermore, we have the quantity and price columns:
- Order quantity
- Unit cost
- Unit price
Finally, we have the financial columns:
- Profit
- Cost
- Revenue
Almost everything here can go into a excel pivot table. However, I won’t cover every variation. Instead, I will focus mostly on revenue. That is because money makes the value of a pivot table easy to see.
Recommended Pivot Tables
Where to Start
To get started, follow these steps:
- Go to Insert.
- Then click PivotTable.
Using Recommended PivotTables
Meanwhile, there is also a Recommended PivotTables option. If you click it, Excel suggests layouts based on your data. As a result, you get ideas, and Excel generates them for you.
A new worksheet
Select Your Data
Now, we will create our own PivotTable. First, click PivotTable. Excel then selects the data automatically. However, if it doesn’t, do this:
- Press Ctrl+Shift+Right Arrow.
- After that, press Down Arrow.
This selects all the data.
Choose Where to Place It
Next, choose where the PivotTable goes:
- A new worksheet
- An existing worksheet
Then look at the right side. There you will find the PivotTable Fields list. These are all the columns we just looked at. Now you can select them and drag and drop them.
Tableau tutorial series
If you took my Tableau series last week, this will feel familiar. In fact, you will start to see some patterns. The four areas are:
- Filters
- Columns
- Rows
- Values
Today, I will show you how to use all of them. In addition, I will show you some extras.
Country
To begin, we will look at profit, cost, and revenue. We will view them:
- Per country
- Per country and state
- With some drill-downs
First, take Country and drop it in. It fills in right away. As you can see, it is very simple.
State
Now, add State. Drag it below Country. You could place it above. However, below makes the most sense. As a result, the table expands. Fortunately, you can collapse it easily.
Expand/Collapse
To collapse the table, follow these steps:
- First, right-click.
- Next, go down to Expand/Collapse.
- Finally, collapse the entire field.
Now we see all the countries again. Each one has a plus sign on the left. Simply click it to see the states.
GROUP BY
How Grouping Works
Basically, this works like a roll-up. It is a grouping. It is also similar to other tools you may know:
- In SQL, it is like GROUP BY.
- In Tableau, it is like a drill-down.
As a result, you can drill into the information.
Adding Revenue as a Value
Next, let’s add some values. They give context to what we are grouping by. To start, let’s add revenue. This is the sum of bike sales revenue per country.
Australia
Now, open the dropdown for Australia. Here are two examples:
- New South Wales had 9,203,495.
- Queensland had 5 million.
Similarly, the rest follow the same way.
Canada
Of course, we don’t have to stop at Australia. Instead, we can drill down to the state within each country. That makes it very useful. For instance, we can look at:
- Canada
- France
- Every other country and its states
Currency
At this point, the table is not very pretty. It just says Sum of Revenue and shows plain numbers. So let’s fix it:
- First, highlight the numbers.
- Then go to Home.
- Next, type Currency.
- Finally, remove the two extra .00s with one click.
Revenue per Country
Change the Currency
Already, this looks much better. It reads well in dollars. Also, you can change to other currencies.
Rename the Field
Moreover, we don’t have to use the Sum of Revenue. We can do many things. For example, go to Value Field Settings. There you can customize the name. Let’s call it Revenue per Country.
United States
Show Percentage of Grand Total
Now, let’s try a quick demo. Choose Percentage of Grand Total. As a result, here is what we see:
- The United States has 32% of the revenue.
- Australia has the next largest share.
Sort Largest to Smallest
However, it is hard to see the highest at a glance. Therefore, we sort:
- Click the dropdown.
- Then go to Sort.
- Finally, choose Largest to Smallest.
Now the United States is on top.
California
Note that the states are not sorted this way. Instead, you have to click Sort again. Then choose Largest to Smallest. Now we can see that California is the biggest. In fact, it brings in 20% of that 32%.
Next, press Ctrl+Z a few times to go back. After that, I want to show you a few other things.
revenue
Now, let’s pull in profit and cost:
- First, pull in cost.
- Then pull in profit.
- Finally, change the currency on both.
For now, I won’t rename them. However, you absolutely can.
To begin, revenue is how much is actually being sold. For the United States, it was 27 million.
profit
Meanwhile, cost is what it took to manufacture, store, or distribute the products. That was 16 million.
Finally, profit is what is made at the end of the day. It comes after all the costs. That includes employee costs and everything else. Even so, the United States still makes 11 million dollars.
To sum up the United States numbers:
- Revenue: 27 million
- Cost: 16 million
- Profit: 11 million
calculated field
At this point, you might say, “I can glance at these two numbers and see the profit is right.” However, we can also use a calculated field. You may remember it from Tableau. In fact, it is basically the same thing.
Here, we can create an extra column. It adds and subtracts the values. Thus, it checks that our numbers add up correctly.
PivotTable Analyze
Let me do that quickly. To begin, follow these steps:
- First, go to PivotTable Analyze.
- Next, go to Fields, Items & Sets.
- Then choose Calculated Field.
- After that, name it anything you like.
For this demo, I will call it Calculated Field Demo. Of course, yours will be different. Afterward, you can enter the formula.
Formula
Although we haven’t covered formulas yet, this is our first tutorial. Still, it is basically the same as writing one in a cell. The only difference is that you get an open text box. Therefore, you can do what you want with it.
Build the formula like this:
- First, insert Revenue. That is the very large number.
- Next, type a minus sign.
- Then insert Cost.
- Finally, click OK.
Calculated Field Demo
Here is the column we just created. As you can see, it matches our Sum of Profit column exactly. That is what we want. In other words, we are checking that Revenue and Cost give the correct profit. Sometimes they are off. For this reason, it is good to check. Overall, it helps to have this extra column.
column
Usually, you wouldn’t include this if you were submitting the table to someone. In the meantime, keep these points in mind:
- It is now an actual column.
- Therefore, you can’t cut and paste it.
- However, you can remove it.
- Likewise, you can add it back at any moment.
Filters
Bringing the Column Back
To add it back, go down here. Since we created the column, it is permanently there. It stays unless you delete all that data. So just click the checkmark. This removes the column for us.
Using Filters
Finally, let’s look at the last area we haven’t used. That is Filters. As the name suggests, a filter lets you filter on certain things.
Customer Gender
What Filters Are Not For
First, avoid using filters for fields already in your table. For example, if you add Country down here, everything expands. Then filtering on it breaks it down. Clearly, that is not what filters are meant for.
Example: Filter by Gender
Instead, here is a better example. Look at Customer Gender. Let’s drop it into Filters. Now we see all the revenue, cost, and profit. In addition, we can view them by gender. Best of all, we don’t have to change the pivot table.
males
At a quick glance, here is the profit by gender:
- Male profit: 16.487 million
- Female profit: 15.733 million
Overall, males are spending a bit more. In fact, the gap is about 700,000 dollars.
sales
Create a Second Pivot Table
Now, let’s create one more pivot table. We will put it right over here. Follow these steps:
- First, go back to the sales sheet.
- Next, press Ctrl+Shift+Right Arrow, then Down Arrow again.
- Then click OK.
As before, this selects all our data.
Use the Date Information
This time, we will use the date information.
Year
First, select Country as before. Next, we want to see which year performed best. In other words, when were our sales at their peak?
To find out, put Year in Columns. Now we have 2011 through 2016. After that, we want to look at revenue.
2013
Next, put Revenue down here. Now we have all our revenue figures. Then format them as currency again. As a result, we can quickly see how Australia did each year:
- There was a huge uptick in 2013.
- Then there was another in 2015.
Excel
However, this didn’t happen for every country. Most went up. Meanwhile, some went up only slightly. Even so, we can see the year-to-year picture on a large scale.
In conclusion, within a few minutes, we built some very useful pivot tables. Anyone can read them. That is their biggest use. With a pivot table, you can:
- Group things together.
- Show data on a broad scale.
- Help anybody understand it.
That is why pivot tables are so useful. To wrap up, I hope this guide was helpful. I also hope it helped you understand how pivot tables work. Finally, I hope you can now use them in Excel with confidence.
