I have been taking a data analysis course with Coding Invaders, and this module focuses on pivot table exercises. By completing this module, you will gain a good amount of confidence in using pivot tables.

let's grow together

Video and instructions on how to use pivot tables in Excel for data analysis.

Summarize big data with pivot table and charts and slicers

PROJECT OBJECTIVE

We are a part of XYZ Co Pvt Ltd company who is in the business of organizing the sports events at international level. Countries nominate sportsmen from different departments and our team has been given the responsibility to systematize the membership roster and generate different reports as per business requirements.

Questions (KPIs)

TASK 1: STANDARDIZING THE DATASET

• Populate the FULLNAME consisting of the following fields ONLY, in the prescribed format: PREFIX FIRSTNAME LASTNAME.{Note: All UPPERCASE)
• Get the COUNTRY NAME to which these sportsmen belong to. Make use of LOCATION sheet to get the required data
• Populate the LANGUAGE_!poken by the sportsmen. Make use of LOCTION sheet to get the required data
• Generate the EMAIL ADDRESS for those members, who speak English, in the prescribed format :lastname.firstnamel@xyz .org {Note: All lowercase) and for all other members, format should be lastname.firstname@xyz.com (Note: All lowercase)
• Populate the SPORT LOCATION of the sport played by each player. Make use of SPORT sheet to get the required data

TASK 2: DATA FORMATING

• Display MEMBER IDas always 3 digit number {Note: 001,002 ...,D2D,..etc)
• Format the BIRTHDATE as dd mmm'yyyy (Prescribed format example: 09 May' 1986)
• Display the units for the WEIGHT column (Prescribed format example: 80 kg)
• Format the SALARY to show the data In thousands. If SALARY is less than 100,000 then display data with 2 decimal places else display data with one decimal place. In both cases units should be thousands (k) e.g. 87670 -> 87.67 k and 12 250 -> 123.2 k

TASK 3: SUMMARIZE DATA - PIVOT TABLE (Use SPORTSMEN worksheet after attempting TASK 1) • Create a PIVOT table in the worksheet ANALYSIS, starting at cell B3,with the following details:

• In COLUMNS; Group : GENDER.
• In ROWS; Group : COUNTRY (Note: use COUNTRY NAMES).
• In VALUES; calculate the count of candidates from each COUNTRY and GENDER type, Remove GRAND TOTALs.

TASK 4: SUMMARIZE DATA - EXCEL FUNCTIONS (Use SPORTSMEN worksheet after attempting TASK 1)

• Create a SUMMARY table in the worksheet ANALYSIS,starting at cell G4, with the following details:

• Starting from range RANGE H4; get the distinct GENDER. Use remove duplicates option and transpose the data.
• Starting from range RANGE GS; get the distinct COUNTRY (Note: use COUNTRY NAMES).
• In the cross table,get the count of candidates from each COUNTRY and GENDER type.

TASK 5: GENERATE REPORT - PIVOT TABLE (Use SPORTSMEN worksheet after attempting TASK 1)

• Create a PIVOT table report in the worksheet REPORT, starting at cell A3, with the following information:

• Change the report layout to TABULAR form.
• Remove expand and collapse buttons.
• Remove GRAND TOTALs.
• Allow user to filter the data by SPORT LOCATION.

Process

• Verify data for any missing values and anomalies, and sort out the same.
• Made sure data is consistent and clean with respect to data type, data format and values used.
• Created pivot tables according to the questions asked.

In the Europe bikes dataset, Extract the insight into sales in each country and each state of their countries using Excel.

This dataset contains annual Excel pivot tables that display summaries of the patients treated in each hospital-based and freestanding Ambulatory Surgery Clinic licensed by the California Department of Public Health (CDPH). The summary data includes discharge disposition, expected payer, preferred language spoken, age groups, race groups, sex, principal diagnosis groups, principal procedure groups, and principal external cause of injury/morbidity groups. The data can also be summarized statewide or for a specific facility county, type of control, and/or type of license (hospital or clinic). Note: Physician-owned ambulatory surgery clinics do not report their data to HCAI and, therefore, are not included in the statewide frequencies.

Analyzing Coffee Shop Sales: Excel Insights 📈

In my first Data Analytics Project, I Discover the secrets of a fictional coffee shop's success with my data-driven analysis. By Analyzing a 5-sheet Excel dataset, I've uncovered valuable sales trends, customer preferences, and insights that can guide future business decisions. 📊☕

DATA CLEANING 🧹

• REMOVED DUPLICATES OR IRRELEVANT ENTRIES: Thoroughly eliminated duplicate records and irrelevant data to refine the dataset for analysis.

• FIXED STRUCTURAL ERRORS: Rectified any inconsistencies or structural issues within the data to ensure uniformity and accuracy.

• CHECKED FOR DATA CONSISTENCY: Verified the integrity and coherence of the dataset by identifying and resolving any inconsistencies or discrepancies.

DATA MANIPULATION 🛠️

• UTILIZED LOOKUPS: Used Excel's lookup functions for efficient data retrieval and analysis.

• IMPLEMENTED INDEX MATCH: Leveraged the Index Match function to perform advanced data searches and matches.

• APPLIED SUMIFS FUNCTIONS: Utilized SumIFs to calculate totals based on specified criteria.

• CALCULATED PROFITS: Used relevant formulas and techniques to determine profit margins and insights from the data.

PIVOTING THE DATA 𝄜

• CREATED PIVOT TABLES: Utilized Excel's PivotTable feature to pivot the data for in-depth analysis.

• FILTERED DATA: Utilized pivot tables to filter and analyze specific subsets of data, enabling focused insights. Specially used in “PEAK HOURS” and “TOP 3 PRODUCTS” charts.

VISUALIZATION 📊

• KEY INSIGHTS: Unveiled the grand total sales revenue while also analyzing the average bill per person, offering comprehensive insights into the coffee shop's performance and customer spending habits.

• SALES TREND ANALYSIS: Used Line chart to compute total sales across various time intervals, revealing valuable insights into evolving sales trends.

• PEAK HOUR ANALYSIS: Leveraged Clustered Column chart to identify peak sales hours, shedding light on optimal operating times and potential staffing needs.

• TOP 3 PRODUCTS IDENTIFICATION: Utilized Clustered Bar chart to determine the top three coffee types, facilitating strategic decisions regarding inventory management and marketing focus.

*I also used a Timeline to visualize chronological data trends and identify key patterns over specific times.

While it's a significant milestone for me, I recognize that there's always room for growth and improvement. Your feedback and insights are invaluable to me as I continue to refine my skills and tackle future projects. I'm eager to hear your thoughts and suggestions on how I can make my next endeavor even more impactful and insightful.

THANKS TO: WsCube Tech Mo Chen Alex Freberg

TOOLS USED: Microsoft Excel

DataAnalytics #DataAnalyst #ExcelProject #DataVisualization #BusinessIntelligence #SalesAnalysis #DataAnalysis #DataDrivenDecisions

With a step-by-step approach, learn to prepare Excel files, data worksheets, and individual data columns for data analysis; practice conditional formatting and creating pivot tables/charts; go over basic principles of Research Data Management as they might apply to an Excel project. Avec une approche étape par étape, apprenez à préparer pour l’analyse des données des fichiers Excel, des feuilles de calcul de données et des colonnes de données individuelles; pratiquez la mise en forme conditionnelle et la création de tableaux croisés dynamiques ou de graphiques; passez en revue les principes de base de la gestion des données de recherche tels qu’ils pourraient s’appliquer à un projet Excel.

Vrinda Store: Interactive Ms Excel dashboardVrinda Store: Interactive Ms Excel dashboard Feb 2024 - Mar 2024Feb 2024 - Mar 2024 The owner of Vrinda store wants to create an annual sales report for 2022. So that their employees can understand their customers and grow more sales further. Questions asked by Owner of Vrinda store are as follows:- 1) Compare the sales and orders using single chart. 2) Which month got the highest sales and orders? 3) Who purchased more - women per men in 2022? 4) What are different order status in 2022?

And some other questions related to business. The owner of Vrinda store wanted a visual story of their data. Which can depict all the real time progress and sales insight of the store. This project is a Ms Excel dashboard which presents an interactive visual story to help the Owner and employees in increasing their sales. Task performed : Data cleaning, Data processing, Data analysis, Data visualization, Report. Tool used : Ms Excel The owner of Vrinda store wants to create an annual sales report for 2022. So that their employees can understand their customers and grow more sales further. Questions asked by Owner of Vrinda store are as follows:- 1) Compare the sales and orders using single chart. 2) Which month got the highest sales and orders? 3) Who purchased more - women per men in 2022? 4) What are different order status in 2022? And some other questions related to business. The owner of Vrinda store wanted a visual story of their data. Which can depict all the real time progress and sales insight of the store. This project is a Ms Excel dashboard which presents an interactive visual story to help the Owner and employees in increasing their sales. Task performed : Data cleaning, Data processing, Data analysis, Data visualization, Report. Tool used : Ms Excel Skills: Data Analysis · Data Analytics · ms excel · Pivot Tables

Blockchain data query: Price Impact Competition Analysis (Pivot Table)

This dataset contains annual Excel pivot tables that display summaries of the patients treated in each Emergency Department (ED). The Emergency Department data is sourced from two databases, the ED Treat-and-Release Database and the Inpatient Database (i.e. patients treated in the ED and then formally admitted to the hospital). The summary data include number of visits, expected payer, discharge disposition, age groups, sex, preferred language spoken, race groups, principal diagnosis groups, and principal external cause of injury/morbidity groups. The data can also be summarized statewide or for a specific hospital county, ED service level, teaching/rural status, and/or type of control.

The link for the Excel project to download can be found on GitHub here. It includes the raw data, Pivot Tables, and an interactive dashboard with Pivot Charts and Slicers. The project also includes business questions and the formulas I used to answer. The image below is included for ease. https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F12904052%2F61e460b5f6a1fa73cfaaa33aa8107bd5%2FBusinessQuestions.png?generation=1686190703261971&alt=media" alt=""> The link for the Tableau adjusted dashboard can be found here.

A screenshot of the interactive Excel dashboard is also included below for ease. https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F12904052%2Fe581f1fce8afc732f7823904da9e4cce%2FScooter%20Dashboard%20Image.png?generation=1686190815608343&alt=media" alt="">

This dataset contains annual Excel pivot tables that display summaries of the inpatients treated in each hospital. The summary data include discharges, discharge days, average length of stay, age groups, race groups, sex, expected payer, type of care, do not resuscitate orders, admission source, admission type, discharge disposition, principal diagnosis groups, principal procedure groups, and principal external cause of injury/morbidity groups. The data can also be summarized statewide or for a specific hospital county, bed size grouping, and/or type of control.

In the beginning, the case was just data for a company that did not indicate any useful information that would help decision-makers. In this case, I had to ask questions that could help extract and explore information that would help decision-makers improve and evaluate performance. But before that, I did some operations in the data to help me to analyze it accurately: 1- Understand the data. 2- Clean the data “By power query”. 3- insert some calculation and columns like “COGS” cost of goods sold by power query. 4- Modeling the data and adding some measures and other columns to help me in analysis. Then I asked these questions: To Enhance Customer Loyalty What is the most used ship mode by our customer? Who are our top 5 customers in terms of sales and order frequency? To monitor our strength and weak points Which segment of clients generates the most sales? Which city has the most sales value? Which state generates the most sales value? Performance measurement What are the top performing product categories in terms of sales and profit? What is the most profitable product that we sell? What is the lowest profitable product that we sell? Customer Experience On Average how long does it take the orders to reach our clients? Based on each Shipping Mode

Then started extracting her summaries and answers from the pivot tables and designing the data graphics in a dashboard for easy communication and reading of the information as well. And after completing these operations, I made some calculations related to the KPI to calculate the extent to which sales officials achieved and the extent to which they achieved the target.

In the beginning, the case was just data for a company that did not indicate any useful information that would help decision-makers. In this case, after collecting a number of revenues and expenses over the months. Needed to know the answers to a number of questions to make important decisions based on intuition-free data. The Questions:- About Rev. & Exp.
- What is the total sales and profit for the whole period? And What Total products sold? And What is Net profit? - In which month was the highest percentage of revenue achieved? And in the same month, what is the largest day have amount of revenue? - In which month was the highest percentage of expenses achieved? And in the same month, what is the largest day have amount of exp.? - What is the extent of the change in expenditures for each month? Percentage change in net profit over the months? About Distribution - What is the number of products sold each month in the largest state? -The top 3 largest states buying products during the two years? Comparison - Between Sales Method by Sales? - Between Men and Women’s Product by Sales? - Between Retailer by Profit?

What I did? - Understanding the data - preprocessing and clean the data - Solve The problems in the cleaning like missing data or false type data - querying the data and make some calculations like "COGS" with power query "Excel". - Modeling and make some measures on the data with power pivot "Excel" - After finishing processing and preparation, I made Some Pivot tables to answers the questions. - Last, I made a dashboard with Power BI to visualize The Results.

Non-traditional data signals from social media and employment platforms for INCZY stock analysis

The National Pollutant Release Inventory (NPRI) is Canada's public inventory of pollutant releases (to air, water and land), disposals and transfers for recycling. This database contains the full NPRI dataset from 1993 to the current reporting year. To help you navigate, a Microsoft Word file provides information on the database’s structure and schema. The database is available in Microsoft Access format (accdb). The data are in normalized or “list” format and are optimized for pivot table analyses. The data are also available in a CSV format : https://open.canada.ca/data/en/dataset/40e01423-7728-429c-ac9d-2954385ccdfb. Please consult the following resources to enhance your analysis: - Guide on using and Interpreting NPRI Data: https://www.canada.ca/en/environment-climate-change/services/national-pollutant-release-inventory/using-interpreting-data.html - Access additional data from the NPRI, including datasets and mapping products: https://www.canada.ca/en/environment-climate-change/services/national-pollutant-release-inventory/tools-resources-data/exploredata.html Supplemental Information This data is also available in non-proprietary CSV format on the Bulk Data page. http://open.canada.ca/data/en/dataset/40e01423-7728-429c-ac9d-2954385ccdfb These files contain data from 1993 to the latest reporting year available. These datasets are in normalized or ‘list’ format and are optimized for pivot table analyses. Supporting Projects: National Pollutant Release Inventory (NPRI)

Materials from workshop conducted for Monroe Library faculty as part of TLT/Faculty Development/Digital Scholarship on 2018-04-05. Objectives:Clean dataAnalyze data using pivot tablesVisualize dataDesign accessible instruction for working with dataAssociated Research Guide at http://researchguides.loyno.edu/data_workshopData sets are from the following:

BaroqueArt Dataset by CulturePlex Lab is licensed under CC0 What's on the Menu? Menus by New York Public Library is licensed under CC0 Dog movie stars and dog breed popularity by Ghirlanda S, Acerbi A, Herzog H is licensed under CC BY 4.0 NOPD Misconduct Complaints, 2016-2018 by City of New Orleans Open Data is licensed under CC0 U.S. Consumer Product Safety Commission Recall Violations by CU.S. Consumer Product Safety Commission, Violations is licensed under CC0 NCHS - Leading Causes of Death: United States by Data.gov is licensed under CC0 Bob Ross Elements by Episode by Walt Hickey, FiveThirtyEight, is licensed under CC BY 4.0 Pacific Walrus Coastal Haulout 1852-2016 by U.S. Geological Survey, Alaska Science Center is licensed under CC0 Australia Registered Animals by Sunshine Coast Council is licensed under CC0

Pre-processed TCGA LGG data used for PIVOT analysis.

📊 Student Performance Analysis

Project: Data Analysis using Excel Pivot Tables & Charts

Executive Summary

Based on the analysis of 6,607 students, this project identifies that active student habits (Attendance, Tutoring) are stronger predictors of success than environmental factors (Income, Resources).

Key Insights

1. Show Up: Attendance is the #1 driver of success.
2. Get Help: Students attending 6 tutoring sessions/week scored 5 points higher on average.
3. Sleep Myth: Sleep duration showed no correlation with exam scores.

Tools Used

• Microsoft Excel: Pivot Tables, Advanced Charting, Statistical Analysis, Data Cleaning.

Source of Dataset(.csv)

• https://www.kaggle.com/datasets/ayeshaseherr/student-performance