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.

Dataset

This dataset was created by Derrick Mallison

Contents

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.

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="">

Summarize big data with pivot table and charts and slicers

Warning: Large file size (over 1GB). Each monthly data set is large (over 4 million rows), but can be viewed in standard software such as Microsoft WordPad (save by right-clicking on the file name and selecting 'Save Target As', or equivalent on Mac OSX). It is then possible to select the required rows of data and copy and paste the information into another software application, such as a spreadsheet. Alternatively, add-ons to existing software, such as the Microsoft PowerPivot add-on for Excel, to handle larger data sets, can be used. The Microsoft PowerPivot add-on for Excel is available from Microsoft http://office.microsoft.com/en-gb/excel/download-power-pivot-HA101959985.aspx Once PowerPivot has been installed, to load the large files, please follow the instructions below. Note that it may take at least 20 to 30 minutes to load one monthly file. 1. Start Excel as normal 2. Click on the PowerPivot tab 3. Click on the PowerPivot Window icon (top left) 4. In the PowerPivot Window, click on the "From Other Sources" icon 5. In the Table Import Wizard e.g. scroll to the bottom and select Text File 6. Browse to the file you want to open and choose the file extension you require e.g. CSV Once the data has been imported you can view it in a spreadsheet. What does the data cover? General practice prescribing data is a list of all medicines, dressings and appliances that are prescribed and dispensed each month. A record will only be produced when this has occurred and there is no record for a zero total. For each practice in England, the following information is presented at presentation level for each medicine, dressing and appliance, (by presentation name): - the total number of items prescribed and dispensed - the total net ingredient cost - the total actual cost - the total quantity The data covers NHS prescriptions written in England and dispensed in the community in the UK. Prescriptions written in England but dispensed outside England are included. The data includes prescriptions written by GPs and other non-medical prescribers (such as nurses and pharmacists) who are attached to GP practices. GP practices are identified only by their national code, so an additional data file - linked to the first by the practice code - provides further detail in relation to the practice. Presentations are identified only by their BNF code, so an additional data file - linked to the first by the BNF code - provides the chemical name for that presentation.

The Single-Family Portfolio Snapshot consists of a monthly data table and a report generator (Excel pivot table) that can be used to quickly create new reports of interest to the user from the data records. The data records themselves are loan level records using all of the categorical variables highlighted on the report generator table. Users may download and save the Excel file that contains the data records and the pivot table.The report generator sheet consists of an Excel pivot table that gives individual users some ability to analyze monthly trends on dimensions of interest to them. There are six choice dimensions: property state, property county, loan purpose, loan type, property product type, and downpayment source.Each report generator selection variable has an associated drop-down menu that is accessed by clicking once on the associated arrows. Only single selections can be made from each menu. For example, users must choose one state or all states, one county or all counties. If a county is chosen that does not correspond with the selected state, the result will be null values.The data records include each report generator choice variable plus the property zip code, originating mortgagee (lender) number, sponsor-lender name, sponsor number, nonprofit gift provider tax identification number, interest rate, and FHA insurance endorsement year and month. The report generator only provides output for the dollar amount of loans. Users who desire to analyze other data that are available on the data table, for example, interest rates or sponsor number, must first download the Excel file. See the data definitions (PDF in top folder) for details on each data element.Files switch from .zip to excel in August 2017.

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

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

Information on accidents across Leeds. Data includes location, number of people and vehicles involved, road surface, weather conditions and severity of any casualties.

Please note

• The Eastings and Northings are generated at the roadside where the accident occurred. Sometimes due to poor internet connectivity this data is may not be as accurate as it could be. If you notice any errors please contact accident.studies@leeds.gov.uk.

Due to the format of the report a number of figures in the columns are repeated, these are:

• Reference Number
• Easting
• Northing
• Number of Vehicles
• Accident Date
• Time (24hr)
• 1st Road Class
• Road Surface
• Lighting Conditions
• Weather Conditions

Reference Number

Grid Ref: Easting

Grid Ref: Northing

Number of vehicles

Accident Date

Time (24hr)

21G0539

427798

426248

5

16/01/2015

1205

21G0539

427798

426248

5

16/01/2015

1205

21G1108

431142

430087

1

16/01/2015

1732

21H0565

434602

436699>

1

17/01/2015

930

21H0638

434254

434318

2

17/01/2015

1315

21H0638

434254

434318

2

17/01/2015

1315

Therefore the number of vehicles involved in accident 21G0539 were 5, and in accident 21H0638 were 2. Overall in the example above a total of 9 vehicles were involved in accidents

A useful tool to analyse the data is Excel pivot tables, these help summarise large amounts of data in a easy to view table, for further information on pivot table visit here.

Further Information

• Please see the guidance document for further information on categories.

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

Information on accidents casualites across Calderdale. Data includes location, number of people and vehicles involved, road surface, weather conditions and severity of any casualties.

Please note

• The Eastings and Northings are generated at the roadside where the accident occurred. Sometimes due to poor internet connectivity this data is may not be as accurate as it could be. If you notice any errors please contact accident.studies@leeds.gov.uk.

Due to the format of the report a number of figures in the columns are repeated, these are:

• Reference Number
• Easting
• Northing
• Number of Vehicles
• Accident Date
• Time (24hr)
• 1st Road Class
• Road Surface
• Lighting Conditions
• Weather Conditions

Reference Number

Grid Ref: Easting

Grid Ref: Northing

Number of vehicles

Accident Date

Time (24hr)

21G0539

427798

426248

5

16/01/2015

1205

21G0539

427798

426248

5

16/01/2015

1205

21G1108

431142

430087

1

16/01/2015

1732

21H0565

434602

436699>

1

17/01/2015

930

21H0638

434254

434318

2

17/01/2015

1315

21H0638

434254

434318

2

17/01/2015

1315

Therefore the number of vehicles involved in accident 21G0539 were 5, and in accident 21H0638 were 2. Overall in the example above a total of 9 vehicles were involved in accidents

A useful tool to analyse the data is Excel pivot tables, these help summarise large amounts of data in a easy to view table, for further information on pivot tables visit here.

Plant species collected throughout Benin were published on GBIF site. Data concerning those species were downloaded from GBIF site. Using Excel dynamic pivot table we derived and achieved the checklist of plant species of Benin from the dataset downloaded.

This dataset contains video game sales data prepared for an Excel data analysis and dashboard project.

It includes detailed information on:

Game titles

Platforms

Genres

Publishers

Regional and global sales

The dataset was cleaned, structured, and analyzed in Microsoft Excel to explore patterns in the global video game market. It can be used to:

Practice data cleaning and pivot tables

Build interactive dashboards

Perform sales comparisons across regions and genres

Develop business insights from entertainment data

🧩 File Information

Format: .xlsx (Excel Workbook)

Columns: Name, Platform, Year, Genre, Publisher, NA_Sales, EU_Sales, JP_Sales, Other_Sales, Global_Sales

💡 Use Cases

Excel dashboard and chart creation

Data visualization and storytelling

Business and market analysis practice

Portfolio or learning projects

👤 Prepared by

Adewale Lateef W — for data analysis and Excel dashboard learning purposes.

Abstract

The purpose of the WASH KAP survey was to collect primary data on several indicators related to the WASH Program implemented in the refugee and host communities of Palabek Settlement, Uganda. The survey aimed at assessing the level of improvement on the accessibility of WASH facilities after a 2 year intervention project.

The survey used cross-sectional design used and both qualitative and quantitative techniques such as use of UNHCR standard WASH questionnaires, field visits and observations were employed during the study. In the 2019/20, the LWF provided WASH services to both refugee settlements and host community living in and around Palabek settlement. In order to gauge the coverage, the LWF conducted this KAP survey. The respondents were drawn from the host community (238 households) and the refugee settlement (446 households).

Geographic coverage

Palabek

Analysis unit

Households

Universe

Refugees and host community

Kind of data

Sample survey data [ssd]

Sampling procedure

A sample size of 578 respondents was determined using the sample size calculator for the survey with a 4% margin of error and an 95% confidence level. Through the entire exercise, the survey process however reached a total of 684 respondents. Analysis, interpretation, and presentation of data were undertaken using Microsoft Excel Pivot tables and chats.

Mode of data collection

Computer Assisted Personal Interview [capi]

This dataset contains sales transaction records used to create an interactive Excel Sales Performance Dashboard for business analytics practice.

It includes six columns capturing essential sales metrics such as date, region, product, quantity, sales revenue, and profit. The data is structured to help analysts and learners explore data visualization, PivotTable summarization, and dashboard design concepts in Excel.

The dataset was created for educational and demonstration purposes to help users:

1. Build dashboards that visualize total sales and profit trends
2. Identify top-performing products and high-profit regions
3. Practice Excel-based business analytics workflows

Columns: Date – Transaction date (daily sales record) Region – Geographic area of the sale (East, West, North, South) Product – Product category or item sold Sales – Total revenue generated from the sale (USD) Profit – Net profit made per transaction Quantity – Number of units sold

Typical uses include: Excel or Power BI dashboard projects PivotTable practice for business reporting Data cleaning and chart-building exercises Portfolio development for business analytics students Built and tested in Microsoft Excel using PivotTables, Charts, and Conditional Formatting.

This dataset contains a structured HR Analytics report for the year 2024–2025, prepared entirely in Microsoft Excel. It has been designed to help students, analysts, HR professionals, and data enthusiasts practice real-world HR analytics using clean and well-organized data.

The dataset covers key HR areas such as employee demographics, salary structure, performance scores, promotions, attendance, and attrition indicators. All data is synthetic and manually curated for educational and analytical purposes.

The main purpose of this dataset is to provide users with a practical Excel-based resource to:

Explore and analyze employee trends

Build HR dashboards in Excel

Practice pivot tables, formulas, and HR KPIs

Learn workforce analytics without Power BI or coding

Work on beginner-friendly and professional HR case studies

This dataset does not contain personally identifiable information and is safe for public sharing. It serves purely as a digital learning resource inspired by real HR scenarios.

Performed in-depth analysis of Myntra's e-commerce data using Excel to identify sales trends, customer behavior, and performance metrics. Leveraged advanced Excel functionalities, including pivot tables, charts, conditional formatting, and data cleaning techniques, to derive actionable insights and create visually compelling reports.

This dataset illustrates customer data from bike sales. It contains information such as Income, Occupation, Age, Commute, Gender, Children, and more. This is fictional data, created and used for data exploration and cleaning.

The link for the Excel project to download can be found on GitHub here. It includes the raw data, the cleaned data, Pivot Tables, and a dashboard with Pivot Charts and Slicers for interaction. This allows the interactive dashboard to filter by Marital Status, Region, and Education.

Below is a screenshot of the dashboard for ease. https://www.googleapis.com/download/storage/v1/b/kaggle-user-content/o/inbox%2F12904052%2Fcbc9db6fe00f3201c64e4fdb668ce9d1%2FBikeBuyers%20Dashboard%20Image.png?generation=1686186378985936&alt=media" alt="">

This dataset was created as part of a data analysis project to build an interactive Excel dashboard for performance monitoring and decision making. The data is either simulated or aggregated from multiple open-source references and does not reflect any specific real-world company or institution.

The goal was to analyze and visualize trends, patterns, and key performance indicators using Microsoft Excel’s advanced features such as Pivot Tables, Slicers, Charts, and Conditional Formatting.

Project Focus: Dashboard creation and performance analysis

Tools Used: Microsoft Excel 2016+

Purpose: Practice data analysis, insights generation, and dashboard design

nspiration: Business intelligence, operational monitoring, and reporting needs across various industries (e.g., healthcare, finance, education)

This dataset is ideal for learners, analysts, or professionals looking to understand how structured Excel files can be used for real time insights and visualization storytelling.