Mastering Data Validation and Pivot Tables for Data Analysis
Data Validation and Pivot table are some of the ways we can increase the accuracy and speed of our data analysis. We will discuss how we can use data validation almost as a preventive measure from inputting wrong or illogical values.
Then will go on to discover how we can create pivot tables and use them to really speed up our analysis to the point where you will be creating tables full of insightful data faster than making a cup of coffee. What are you waiting for? Lets get started!
Data Validation and Drop Down Lists
If we have data in a column like lets say a specific range of years that have to be inputted. We need to make sure that dates that don't make sense are not in. While there are many ways to do this like the use of conditional formatting to reduce errors. We can also use a more preventive measure called data validation.
This will prevent the user from inputting values or even texts that aren’t spelled correctly or don’t even fall within a range that would make sense. Lets see the steps on how we might achieve that.
Steps to Apply Data Validation
- Access Data Validation: The tool is located at the data tab. Then go to the data tools section and click on Data Validation.
- Set Validation Criteria: Make sure you have selected a column to validate. Go to the data validation settings and choose a specific criteria you want like whole numbers etc.
- Input Message: You can also include a message that will show when someone clicks on the cell in that column that you applied data validation to. This will help guide the user on what to input.
- Error Alerts: In the data validation tool you also add customer error titles and messages to also tell the user why they can’t enter that specific data.
Creating Drop Down Lists
- Prepare the List: We can take data validation one step forward by allowing the user to click a set of choices instead of manually typing it in. Reducing the chances of a misspelling. First create the list of options you want to be in that column like genres of a movie.
- Apply Data Validation: Now select the column as always that you want your data validation to be in. Then click lists are the choice of the data type. Then add the source to be the list that your created. Press ok and your done!
- Adding Additional Values: If for whatever reason you want to add a additional value that isn't include in your list. Then you have to update that list. Type your new value underneath the list and press shift click to drag it in the middle. The last step of pressing shift is important so you don’t replace the value but add it.
- Hiding the Source List: Their are many ways to hide the list you made. From taking it to another worksheet or even hide the column itself by right clicking the column and selecting hide to prevent it from being accidentally deleted.
Nested IF Statements
Remember when we talked about IF statements last time? We can now make them even more versatile by adding Boolean expressions like “AND” , “OR” and even “NOT”. This will allow us to create more complex but efficient IF statements.
Syntax: =IF(AND(condition1, condition2), value_if_true, value_if_false) This is the syntax for when we use “AND”. As you see its similar to a regular IF statement but now allowing us to include multiple conditions. In this case both condition 1 and 2 have to be true for it to be true since we are using “AND”.
Examples of Nested IF Statements
Lets say we wanted to give employees a bonus that had BOTH a full-time status and a good rating. In this case we would write this IF(AND(B2=="full-time",B4>3 ), 3000,"") . So now only those who meet both conditions would get 3000 else nothing.
One more thing we can also use multiple Boolean operators in one IF statement. For example IF(AND(NOT(condition1)),condition1, condition2), value_if_true, value_if_false).
Pivot Tables
Pivot Tables are a very popular and powerful feature of Excel. They allow us to summarize and organize large amounts of data that are suitable for analysis in a matter of seconds.
They help us identify trends and patterns that we can’t see if we were to just look at the regular dataset with a naked eye. Lets see how we can make one and use to solve common business questions.
Steps to Create a Pivot Table
- Prepare Your Data: First and foremost make sure all your columns have individual headers. Make sure you have also cleaned your data properly. Leaving no empty rows/columns.
- Insert Pivot Table: Go to insert then select Pivot Table. One thing I forgot to mention is when you add new data to your original raw data it won’t be included in your pivot table once it was added before. One way to get around this in to turn your data into a table first before converting into a pivot table.
- Recommended Pivot Tables: To the right of pivot table we can choose predefined sets of pivot tables based on common types of analysis that you might be interested in.
Customizing Pivot Tables
Values Section: In the values tab you can change it from lets say the sum of sales to the average of sales by clicking the arrow for the drop down then go to value field settings and click what you want.
Filters: As the name entails we can add columns to the filter section. If you want to filter the data by lets say region or a specific customer or even product.
Design Options: Once a pivot table is created their a design tab. In this tab you can change whether a grand total or subtotal should be shown. You can also change the reports layout and pivot table styles.
Removing Buttons: You can also remove the buttons near the rows by going to pivot table analyze tab and clicking show and removing buttons.
Advanced Techniques
Percentage of Total: Right-click the column of sales and select Show Values As > % of Grand Total.
Refreshing Pivot Tables: Right-click the table and select Refresh to update data.
Advanced Pivot Table Techniques
Adjusting Field List Settings
Gear Icon: Click the gear icon in the PivotTable field list to adjust settings. Then choose which layout you prefer to your liking.
Creating Multiple Reports
Using Report Filter Pages: We can filter by customer and generate multiple worksheets for each customer. Thus we can create multiple reports with basically one click. In this case we can do this by putting customers in the filter section. Then go to analyze > pivot table section> click options > click show report filter pages.
Viewing Details
Double-Clicking: You can see the information of a value in the column like a number by double-clicking a number to see underlying details.
Adding Data Bars
Conditional Formatting: You can apply data bars via Home > Conditional Formatting > Data Bars > More Rules. Then highlight the range of cells you want to change into bars and click ok.
Creating Calculated Fields
Calculated Fields: A Calculated Filed is basically us adding another column that has some sort of formula/calculation applied from another column. Go to PivotTable Analyze > Calculations > Calculated Field.
Calculating Differences
Difference From: Make sure we have a list of dates. Then right click the sales column for example and then click “shows value as” > “difference from” and make sure to set the base item as previous to show the change in sales over time compared to previous day or month etc.
Smart Custom Formatting
Custom Number Format: Right-click, select Number Format then custom, and create custom formats with arrows and colors. You can for example apply this to the calculated difference from earlier!
Custom Groupings
Grouping Products: If we want to group by men and women we can do that simply select products, right-click, and choose Group.
Grouping Date Fields: We can also group dates into minutes hours months and days. All you have to do is right-click dates, select Group, and set intervals.
Adding a Timeline
Insert Timeline: Go to PivotTable Analyze > Insert Timeline. Select date fields and click OK. Make sure you have dates to work with of course.
Overall, from validating our data to prevent errors from occurring in the first place. To utilizing pivot tables to organize our raw data into meaningful insights in a matter of seconds. Making sure you learn these tools are critical to your journey of becoming better at excel.
Let me know if you have any questions or concerns. In the next article we will learn about to add filters to our pivot tables and make sure our formulas to go to ranges we don't want them to go to. Until then, Good morning, Good evening and Good night!
