DEV Community

Rajasekaran Palraj
Rajasekaran Palraj

Posted on

Data Analysis with Excel - Notes

Array Functions:

Modern Dynamic Array vs Classic Array

unique
Sort(R2#)

Lookup

VLookup
Hlookup
XLookup

Unique:
=UNIQUE(A:A,FALSE,FALSE) - to get unique values

=UNIQUE(A:A,FALSE,TRUE) - to get values exist only one

Duplicate
=UNIQUE(FILTER(A2:A100, COUNTIF(A2:A100, A2:A100)>1))

Quick Analysis

Quick Analysis tool makes it possible to analyze your data quickly and easily using different Excel tools.

You can use Quick Analysis with a range or a table of data. To access Quick Access tool, select the cells that contain the data you want to analyze. The Quick Analysis tool buttonQuick Analysis Tool Button

Formatting
Charts
Total
Tables
Sparklines

Lookups

Find values in a range of data - VLOOKUP and HLOOKUP
Obtain a value or the reference to a value from within a table or range - INDEX
Obtain the relative position of a specified item in a range of cells - MATCH

Using VLOOKUP Function
The syntax of the VLOOKUP function is

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Using VLOOKUP Function with range_lookup FALSE
_
Use range_lookup False when data not in order and not matching exact values_

Using HLOOKUP Function
You can use HLOOKUP function if the data is in rows rather than columns.

_
Use range_lookup False when data not in order and not matching exact values_

Using INDEX Function
When you have an array of data, you can retrieve a value in the array by specifying the row number and column number of that value in the array.

Pivot Tables:

Data analysis on a large set of data is quite often necessary and important. It involves summarizing the data, obtaining the needed values and presenting the results.

Excel provides PivotTable to enable you summarize thousands of data values easily and quickly so as to obtain the required results.

Creating PivotTable
To create PivotTables, ensure the first row has headers.

Click the table.
Click the INSERT tab on the Ribbon.
Click PivotTable in the Tables group. The PivotTable dialog box appears.

In the Table / Range Box, type the table name.
Click New Worksheet to tell Excel where to keep the PivotTable.
Click OK.

Charts:

Excel charts transform raw data into clear, visual representations, enabling us to analyze trends, compare datasets and communicate insights effectively. Charts visually represent data, making complex information easier to interpret. Different chart types include:

Column Chart: Compares values across categories.
Bar Chart: Similar to column charts but with horizontal bars.
Line Chart: Displays trends over time.
Pie Chart: Shows proportions of a whole.
Combo Chart: Combines multiple chart types (e.g., column and line).

  1. Creating a Chart in Excel Follow the steps below to create a chart in Excel:

Step 1: Select the data for which we want to create the chart.
Step 2: Go to Insert tab and Select type of Chart

  1. Changing the Chart Type Follow the below steps to change the chart type:

Click on Chart Design
Click on Change Chart Type
Select the type of chart we want

  1. Customizing the Chart Type 3.1 Switch Row/Column To switch the rows or columns follow the below steps:

Go to the Design tab in the Ribbon.
Click on Switch Row/Column

3.2 Legend Position
Follow the below steps to place the legends

Go to Chart Design in Insert chart
Click on Add Chart Element
Navigate to Legend
Select where we want to place our legends.

  • Use Recommendated Charts
  • Chart Design
    • Chart Element to help show visual indication
    • Quick Layout to try different themes

Line Chart:

  • Job Title
  • Include/Remove Axes From Chart Elements
  • Add Trend Line
  • we can use Chart Design to change Quick Layout and Chart Elements

Pie chart

  • Job no degree mention
  • Chart Design -> Quick Chart
  • Change Colors from Quick Chart
  • Change Font from Format
  • Customize percentage - Lable/Number Decimal places
  • Change Chart Tiles with valid name

Bar-Column Chart:

  • Names are long in column chart
  • Not organized
  • Sort by Job Count
  • Create Bar chart
  • Change sort order and create bar chart again
  • select chart and right click select data
  • Try Change Chart Type
  • Add DAta lables to shwow count
  • Change number Format by double clicking count

Scatter Plots

  • Salary year avg, salary hour avg
  • Select only yearly Median avg and Hourly Median avg
  • Click axis and change Axis Option Bounds min/max
  • Change Number format
  • Enable Axis Titles
  • select Axis Tiles, Enter= then select cell which have title name
  • Enable Data Labels
  • Add values from cell otion from format
  • select Range for job names
  • Add Trend Lines

Add values in bar chart

Map Charts

  • Job country
  • get Job count and Median Salary
  • Create Map chart by select job title and Median Salary
  • Change Title
  • Apply Filter

Histogram:

  • Skewed Write Distribution
  • Filter --> Number filter --> less than 300000
  • Double Click Bar - change bin width
  • Change Number Format
  • Change Title, Enable Axes Titles

Box & Whiskers Charts

  • To analyse different jobs titles

  • Select salary and title

  • Use REcommended chart

  • Change Y axis maximum values

  • Change Number format

  • change Color

  • Change Title

Spark Lines

  • Its Mini Charts
  • select data / Insert / sparklines Column
  • Add High Point/ Low Point
  • Change color for High Point

Dashboard

Power Query

it used to copy data from multiple places into excel or powerBi (ETL)

Combine Quereis (Merge and Append)

M Language

Power Pivot

DAX

https://github.com/lukebarousse/Excel_Data_Analytics_Course/tree/main

Top comments (0)