Excel is a powerful tool that Data Analysts use to transform raw data into actionable insights, aiding decision-making and business success. Analysts play a detective-like role, identifying patterns and interpreting trends to convey complex data in a straightforward manner.
In this tutorial, we will develop a project aimed at analysing Indian Cities Electricity Consumption using Excel providing guidance on how to effectively gather, process, analyse, and present the relevant data to derive actionable insights for the stakeholders.
The Six Steps of Data Analysis in Excel
In Data Analysis, there are 6 steps in total:
- Ask: This step, defines the problem statement to solve. Analyst collaborate with the stakeholders and understand their needs.
- Prepare: Here, we will choose what type of data needs to be collected to solve the problem. How to organise this data.
- Process: This step involves tasks related to data cleaning like error removal, data formatting, and combining data from various sources.
- Analyse: To deep dive into the processed data to extract insights and draw conclusions, like Data Aggregation.
- Share: The focus shifts towards communicating findings using charts, graphs, and other visualizations to make the data understandable and conclude insights derived from the analysis to relevant stakeholders.
- Act: The emphasis is on taking action based on the insights and recommendations derived from the analysis.
Excel Project for Data Analysis: Indian Cities Electricity Consumption 2017-19
Let's demonstrate the data analysis process using dataset Indian cities' electricity consumption (2017-2019), This dataset contains records of electricity consumption for various cities across India over the years 2017 to 2019 with multiple rows and columns representing different aspects of electricity usage.
Usage of the Dataset:
- Trend Analysis: Analysing the trends in electricity consumption over the years for planning and management.
- City Comparison: Comparing electricity usage across different cities to identify high and low consumption patterns.
- Sector Analysis: Understanding the consumption patterns across different sectors (Industrial, Residential, Commercial).
Analysing this dataset can be beneficial to make informed decisions based on consumption patterns and trends.
Step 1: Define Problem Statement
For the phase 1: The stakeholders want some details:
- Consumption of Electricity (in lakh units) for Commercial purpose for Indore City.
- Calculate the total electricity consumption by Indian cities.
- Which city consume maximum and minimum amount of electricity for Industrial Purpose?
- Total electricity consumption on yearly basis.
- If there is any trend(increase/decrease) in the electricity consumption?
Step 2: Load and Prepare the Dataset
After downloading, we will upload the dataset on Microsoft Excel.
Dataset uploaded in ExcelFor more refer to : How to Import, Edit, Load and Consolidate Data in Excel Power Query?
After uploading the data, it is necessary to check whether the data is clean or not.
Step 3: Data Processing : Cleaning
Data integrity refers to the quality of data being accurate, complete, consistent, and trustworthy throughout its entire lifecycle, ensuring its reliability and suitability for analysis. Cleaning data, in this context, signifies that the data possesses these attributes before undergoing analysis.
For Data Cleaning, we will use below tools and techniques:
Within this project, we aim to address the presence of missing values, specifically identifying columns containing "NA" values through the application of conditional formatting. This approach enables the visual highlighting of such occurrences for further scrutiny. Additionally, we will employ the COUNTIF function to quantitatively assess the extent of missing values within the dataset.
By leveraging these methods, we can systematically identify and evaluate the presence of missing data, facilitating the subsequent steps of data cleaning and analysis. After applying conditional formatting and COUNTIF, the application is shown below.
Conditional FormattingTo count the number of cells that matched a specified value, we will use the formula: =COUNTIF(C2:H48, "NA")
COUNTIF Example We will remove the "NA" values because it makes the dataset incomplete. While in big datasets, we can remove these values by deleting the complete row or Interpolate Missing Values.
But since our dataset is not very large, it is better to change the "NA" values to 0 as the result will be negligible. For which we will use "Find and select" option.
Find And SelectClick Find and Select and select Replace.
Replace '"NA" by '0'Click Replace all.
All the "NA" values are converted to 0Hence, data is cleaned.
Note: Before deleting any incomplete data, just make sure your sample size doesn't become too small which may effect your overall result.
Step 4: Analysing The dataset
After data cleaning, dataset can be analysed.
4. 1 To know the value of Consumption of Electricity (in lakh units) for Commercial purpose for Indore City.
For this, we will use VLOOKUP function, It searches for a certain value in a column to return a corresponding piece of information. The formula for VLOOKUP is given below:
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).
Example: Vlookup function (Indore City electricity consumption - Commercial purpose)4.2 To determine the aggregate electricity consumption across Indian cities.
We will employ the SUM function for data aggregation. This function allows us to calculate the total consumption by summing up the values within the specified range, which in this case is from cell H2 to H48 using the formula: =SUM(H2:H48)
Using Sum function to find the total consumption by all the citiesAdditionally, the Pivot Table tool can serve as an alternative method for aggregating and summarizing data. By utilizing Pivot Tables, we can efficiently analyze and present the total electricity consumption across various cities in a structured and customizable format. It's a powerful tool to calculate, summarize and analyze data that lets you see comparisons, patterns, and trends in your data.
For moving on to next problem statement, we will be using: Exploring Data with PivotTables in Excel
To accomplish the tasks in the project, we will leverage the functionality of PivotTables within Excel, facilitating a structured and comprehensive analysis of the provided dataset. Through this approach, we aim to derive detailed insights into the electricity consumption patterns of Indian cities, enabling informed decision-making by the stakeholders.
4.3 Now, next major problem, which city consumes maximum and minimum amount of electricity for Industry Purpose?
Steps to find the value:
- Select the table and add pivot table.
Pivot Table Options- In the Pivot Table options, click the dropdown symbol of Consumption of Electricity (in lakh units)-Industrial purpose and select Add to Values.
- In the ∑ Values box, Consumption of Electricity (in lakh units)-Industrial purpose is added. Click the Dropdown menu there and select Value Field Settings.
Value Field Settings- In the Summarize Value By, choose SUM and click OK.
Click OK
Pivot Example 24.4 Total electricity consumption on per year basis.
Steps to find the value:
- Select the table and add Pivot Table.
- In the Pivot Table options, click the dropdown symbol of Consumption of Electricity (in lakh units)-Total Consumption and select Add to Values.
- In the ∑ Values box, Consumption of Electricity (in lakh units)-Total Consumption is added. Click the Dropdown menu there and select Value Field Settings.
- In the Summarize Value By, choose SUM and click OK.
Pivot Table to calculate maximum and minimum electricity consumption per year4.5 If there is any trend(increase/decrease) for the electricity consumption?
Let's search for varanasi city, steps to find the trend of Varanasi City for the year 2016-17 and 2017-18:
- Select the table and choose Pivot Table.
- Add City to the Filters and choose Varanasi City.
- And then move City from Filters to Rows box.
Selecting Varanasi City - Add Year and choose 2016-17 and 2017-18.
Selecting Year- Add all other remaining columns to the \sum Values field except the last column.
- In the \sumValues box, all required columns are added. Click the Dropdown menu there and select Value Field Settings for all .
- In the Summarize Value By, choose SUM and click OK, for all the columns added in the \sum Values.
- Now, add a Column chart and see the trend.
- We find that for the Varanasi city, the trend of electricity consumption is decreasing from the year 2016-17 to 2017-18 for Domestic Purpose, Commercial Purpose and Industrial purpose. However, for Public Water Work and Street Light and Others, it increased.
Difference in electricity consumption trendStep 5: Visualization: Display the findings with correct type of charts
Employing graphical representations such as charts, we can effectively illustrate and present the conclusions drawn from our analysis. Utilizing charts enables us to visually depict various aspects of the data, including trends, comparisons, and distributions, thereby enhancing the clarity and comprehensibility of our findings.
Bar Chart for Total Electricity Consumption Yearly
Data Visualization Charts using Column
Line Chart for Electricity Consumption for Industrial Purpose
Data Visualization using Line ChartThe graph shows that total electricity consumption for industrial purposes varies across the cities measured. The highest electricity consumption is around 140,00 kWh and the lowest is around 258 kWh.
Pie Chart for Total Electricity Consumption- Public Water and Street Light
Data Visualization using Pie Chart
Note: Choose a Chart to visualise the dataset depending on the result. See which chart makes your result easily understandable.
Step 6: Draw Conclusions and Employ Strategic decisions
In the Act phase, stakeholders are presented with the opportunity to enact strategic decisions based on the insights derived from the analysis of electricity consumption patterns in Indian cities. These decisions may include:
- Implementing targeted measures aimed at reducing electricity consumption in cities, particularly for industrial purposes. This could involve the introduction of energy-efficient technologies, the establishment of incentive programs for energy conservation, or the implementation of regulatory measures to promote sustainable energy practices.
- Undertaking further investigation to understand the underlying factors contributing to disparities in electricity consumption among cities. This deeper analysis may involve examining socio-economic indicators, demographic characteristics, industrial activities, and infrastructure development to identify potential drivers of variation in electricity usage. Such insights can inform the development of tailored strategies to address specific challenges and optimise resource allocation effectively.
Conclusion
Data Analysts uses Excel to find some in-depth insights from the data which can blow everyone's mind and use such insights to make the system more efficient.
Similar Reads
Six Steps of Data Analysis Process
This article provides a detailed overview of the data analysis process, outlining the key steps involved and best practices for each stage. Steps for Data Analysis Process Define the Problem or Research QuestionCollect DataData CleaningAnalyzing the DataData VisualizationPresenting DataEach step has
6 min read
Top 10 SQL Projects For Data Analysis
SQL stands for Structured Query Language and is a standard database programming language that is used in data analysis and to access data in databases. It is a popular query language that is used in all types of devices. SQL is a fundamental tool for data scientists to extract, manipulate, and analy
9 min read
Top Excel Interview Questions for Data Analysis
Excel helps data analysts change and look at data to find patterns. It arranges and shows facts so that decisions can be made. It does data cleaning, transformation, report and dashboard creation, and more. Preparing for popular Excel interview questions can help you get the job. Here, you can find
13 min read
What-If Analysis with Data Tables in Excel
What-if analysis is the option available in Data. In what-if analysis, by changing the input value in some cells you can see the effect on output. It tells about the relationship between input values and output values. In this article, we will learn how to use the what-if analysis with data tables e
5 min read
Top Excel Data Analysis Functions
Have you ever analyzed any data? What does it mean? Well, analyzing any kind of data means interpreting, collecting, transforming, cleaning, and visualizing data to discover valuable insights that drive smarter and more effective decisions related to business or anywhere you need it. So, Excel solve
6 min read
Steps for Mastering Exploratory Data Analysis | EDA Steps
Mastering exploratory data analysis (EDA) is crucial for understanding your data, identifying patterns, and generating insights that can inform further analysis or decision-making. Data is the lifeblood of cutting-edge groups, and the capability to extract insights from records has become a crucial
15+ min read
How to Use SPSS for Data Analysis
Data Analysis involves the use of statistics and other techniques to interpret the data. It involves cleaning, analyzing, finding statistics and finally visualizing them in graphs or charts. Data Analytics tools are mainly used to deal with structured data. The steps involved in Data Analysis are as
5 min read
GRE Data Analysis | Methods for Presenting Data
Data Interpretation Question in GRE will require the knowledge of understanding Data Representation methods. It is very Important to understand data correctly and apply the same to solve the problem. Important Terms to know - Variable: A characteristics which varies with respect to given categories.
3 min read
EDA | Exploratory Data Analysis in Python
Exploratory Data Analysis (EDA) is a key step in data analysis, focusing on understanding patterns, trends, and relationships through statistical tools and visualizations. Python offers powerful libraries like pandas, numPy, matplotlib, seaborn, and plotly, enabling effective exploration and insight
9 min read
How to Write Data Analysis Reports
Reports on data analysis are essential for communicating data-driven insights to decision-makers, stakeholders, and other pertinent parties. These reports provide an organized format for providing conclusions, analyses, and suggestions derived from data set analysis. In this guide, we will learn how
9 min read