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 Excel

For 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:

• Conditional Formatting
• Remove Duplicates
• Split
• Concatenate

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 Formatting

To 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 Select

Click Find and Select and select Replace.

Replace ‘”NA” by ‘0’

Click Replace all.

All the “NA” values are converted to 0

Hence, 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 cities

Additionally, 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 [Tex]∑[/Tex] 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 2

• Hence, got the value.

4.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 [Tex]∑[/Tex] 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 year

4.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 [Tex]\sum[/Tex] Values field except the last column.
• In the [Tex]\sum[/Tex]Values 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 [Tex]\sum[/Tex] 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 trend

Step 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 Chart

The 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:

1. 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.
2. 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.