This weekend, I worked on my first project while studying at Tech Quest STEM Academy, from which I learned a lot and felt the need to share. I was given a dataset of 1002 rows and 20 columns to analyze.
Below is what it looks like, along with the questions containing the analysis I was supposed to perform on the dataset.
Opening the dataset and looking at it, I honestly didn't know where to start. If future me told me that I'd be able to complete the project in less than five hours, I would have laughed off the idea. But once I started, I just kept going, and before I knew it, I was done. So now I will provide a step-by-step tutorial on how I went about this.
*PIVOT TABLES*
Pivot tables were a vital part of this project. If you don't know what pivot tables are, here is your answer: A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail and answer unanticipated questions about your data.
To get your pivot table, tap any cell within your dataset, go to insert, and on your far right, there would be an icon labeled pivot table. Click on it, then an option should pop up asking if the data range selected is correct (make sure it is). It will also ask if you want to move your pivot table to a new spreadsheet; well, that's up to you. In this project, I moved my pivot table to a new spreadsheet as I would be inserting several pivot tables, and I needed a fresh workspace. Once that is done, you should have your pivot table in a new spreadsheet.
My first project question asks to calculate the store's total income with the given data. My pivot table can easily help me retrieve that. Once you click on your pivot table, on the far right of your spreadsheet, your field lists should pop up asking you what exactly you want your pivot table to analyze. For this question, I want it to analyze the store's total income. So, I'll click on the field name that states "total" because that's the field that contains everything the store made, then I'll move it under values so that Excel takes everything under the Total column and sums it up, returning it as a value. Now, the value option doesn't only sum up cells; it can do other functions such as getting the average, counting them, getting the max and min; all you have to do is double-click it, and you see the range of operations it can do. Note that it is important to name your pivot table, as it will come in handy. That option can be found under "Pivot Chart Analyze."
Now, I'll need about 10 pivot tables, and instead of constantly going back to my dataset to insert it, I can easily copy the pivot table I already have, paste it, and then change what I want it to analyze. With the next question asking how many orders, I can just go to my copied and pasted pivot table and change its content to Invoice IDs, putting it under Values as well. Doing this will help count how many invoice IDs are in the dataset, in return answering the question of how many orders the store got. I do the same thing for the next question, selecting the "ratings" field name and putting it under values. But now I don't want it to sum up the ratings; I want the average, so I double click and select the average operation. Same for question 4; I put the "Time" field name and put it under values and columns. "Columns" lists out all the values under a selected field in a vertical order.
Now, question 5 is where it gets tricky. It asks you to group the hours into Morning, Afternoon, and Evening periods and to analyze the amount of orders gotten within these 3 periods. This data isn't included in our dataset, meaning that we'll have to include it. NOTE: creating a new column at the edge of our dataset won't include that column in the range selected for the pivot charts, and we want the new column created to be included in the pivot chart; hence, we have to include the column somewhere in the middle of our dataset, not at the edge.
*Functions*
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure.
Using the function: =IF(AND(M2>=TIME(9,0,0), M2<TIME(12,0,0)), "Morning", IF(AND(M2>=TIME(12,0,0), M2<TIME(16,0,0)), "Afternoon", IF(AND(M2>=TIME(16,0,0), M2<TIME(21,0,0)), "Evening", "Other"))), I should be able to retrieve whether the time in M2 is "Morning," "Afternoon," or "Evening." I autofill so that this function applies to all the cells under it in the dataset. After doing this, I can add a new pivot chart in the separate spreadsheet created to analyze the number of orders gotten during these 3 periods. In Excel, coloring newly calculated columns is a best practice.
Question six wants me to analyze the gross income by breaking it down into month, customer type, gender, product type, city, and branch of the store, meaning that we'll need to have pivot tables containing that information. However, month, customer type, gender, product type, city, and branch of the store are not in the current dataset and spreadsheet. They are in a different spreadsheet in the same workbook, meaning that we need to transfer the information to the spreadsheet that contains the dataset we're working on, and to do that, we'll need to use the function "VLOOKUP."
The layout for the VLOOKUP function is: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE or 0/FALSE). It is used to transfer data from spreadsheets with at least one common column. Here is an example of how I used the VLOOKUP formula to extract the gender from the Customer details spreadsheet: =VLOOKUP(A2,'Customer-details'!$A$1:$C$1001,3,0).
I had to put the table A1:A1001 in an absolute reference so that the table range remains constant. An absolute reference in Excel is a reference that cannot be changed when copied*, so you won't see changes in rows or columns when you copy them. Absolute references are used when you want to fix a cell location. These cell references are preceded by a dollar sign.*
I use the VLOOKUP Formula to also extract the month, customer type, product type, city, and branch from the other spreadsheets. Now your new and improved dataset should look like this, with your spreadsheets containing your pivot tables looking like this as well.
*Pivot Charts*
If you've come this far, a big thumbs up and congratulations. I'm proud of you. Luckily for you, you've done all the hard work; now you can relax for this part.
To create a pivot chart from a pivot table, click on the table, go to Insert, and you should find the option there. I forgot to mention this earlier, but it's advisable to already have your table title ready so you can easily reference your chart title to it using the =cellname. To create my title, I used the CONCATENATE function to combine the words from the two table headers, separated by the word "by" or "per." In detail: =CONCATENATE(B22, " ", " per ", A22); this is for the Orders per day table.
Back to Pivot charts, after inserting your chart and referencing title cells, you should have as many charts as you would need for your dashboard. Your dashboard should be done on a new and fresh spreadsheet. To move all your charts to that new spreadsheet, on your keyboard, holding onto Ctrl on Windows or Cmd ⌘ on Mac, select all your charts, and use Ctrl X to cut them. Go to your new spreadsheet and press Ctrl V to paste them.
Formatting pivot charts
Step 1: Design a Layout That Speaks Volumes
Think of your dashboard as a canvas where each pivot chart is a vibrant stroke of color. Arrange them on a separate sheet, like puzzle pieces coming together to form a cohesive picture. Resize and align them for a visually appealing layout.
Let your dashboard radiate personality with a consistent color scheme. Choose hues that resonate with your data story. Add chart titles that tell a tale, and label your axes so everyone can follow the narrative. Adjust gridlines and axes to create a comfortable reading experience.
Resize and Align: Adjust the size of each pivot chart to fit the layout. Align them neatly for a professional look. You can find that option by clicking on your pivort chart and going to the "format" contextual tab.
Grouping: Group related charts together. For instance, if you have charts related to sales, group them in one section.
Step 2: Format Pivot Charts for Consistency
Maintain a consistent look for a cohesive and professional dashboard.
Color Scheme: Choose a consistent color scheme for all charts. This aids in creating a unified visual experience.
Chart Titles and Labels: Ensure all charts have clear titles and appropriately labeled axes. Include legends if needed.
Gridlines and Axes: Adjust gridlines and axes for better readability. Consider removing gridlines or modifying axis scales as necessary.
you can find the option to do this by clicking on your pivot and going under design. There you will find the add chart element option.
Step 3: Introducing Slicers for Interactivity
We are almost done, there are just a few more things we need to do.
In Microsoft Excel, slicers are a powerful and user-friendly feature that provides an interactive way to filter and analyze data in a PivotTable or PivotChart. Slicers act as visual buttons or filters that allow users to easily slice and dice data without having to navigate complex filter menus.
How do I insert a slicer? you must be wondering. Well it's pretty easy.
Select the cell within your PivotTable or PivotChart.
Go to the "Insert" tab in the Excel ribbon.
Click on the "Slicer" button in the "Filters" group.
Choose the fields you want to filter by and click "OK."
They look like this.
It is important that you connect your slicers to your charts. You do this by clicking on the slicer and going to your "Slicer" Contextual tab. There you'll find a "Report Connections" Option. Click on it and select all the charts you want your slicer to connect with.
Step 4: Share Your Data Insights in a Story
Don't just display numbers; tell a story.
Text Boxes: Insert text boxes for your dashboard title (optional) to provide context and guide your audience through the narrative. Explain the peaks, valleys, and twists in your data journey.
Key Performance Indicators (KPIs): Introduce KPIs to highlight the stars of your data show. These are the superheroes of your dashboard, representing the key metrics that everyone should pay attention to.
By following these detailed steps, you can create a polished and user-friendly dashboard using pivot charts in Excel. Tailor the instructions based on your specific data and the insights you aim to convey. I really hope this helped you. Below is my dashboard reveal!!!
