DEV Community

Cover image for Microsoft Excel Guide to Beginners using a HR Dataset Example
Kahindi Kevin
Kahindi Kevin

Posted on

Microsoft Excel Guide to Beginners using a HR Dataset Example

#beginners #data #msexcel

Microsoft Excel (Ms Excel) happens to be a key toolset in any data analyst's skillset. It helps any data to be understood with ease. People are able to draw conclusions and make interpretations when it is presented in terms of sums, averages, making comparisons from period to period and through presentations in the form of charts and responsive dashboards.
Ms Excel is represented in rows (vertical), columns (horizontal) and cells where formulas of data manipulation are input.

Data on View Mode on Ms Excel

Ms Excel View on Opening a Dataset
The above image is a representation of Ms Excel - the rows, columns, cells and sheets of a dataset to be analysed.

Freezing of First Column
This helps the analyst not to lose track of the data that they are analysing. This allows easy manipulation of a large dataset without losing track of the data titles.
The process used to achieve this is as follows:

  • On the ribbon/ toolbar, click on View
  • Choose the item called Freeze Panes
  • For ease of data access, with a set of data titles, select freeze the first column.

Cursor on Ms Excel highlighting Frozen area
Spilling Over Columns
For data titles in the columns spilling over to other cells, rendering some data hidden in view could lead to the data not being well understood. It is good to auto-adjust the entire dataset for easier reading of titles for proper analysis and interpretation.
This can be done through:

  • Click on Home on the toolbar
  • Choose Format

  • Autofit Column Width; or

  • Click on the row and column intersection

  • Hover your cursor on the column until a cross with arrows on the left and right appears

  • Double-click on the area that this cross appears

  • The column splits are removed all across the dataset.

Removing Duplicates from the Raw Data
This is an important aspect that every person who intends to manipulate data for it to be easily understood needs to work on.
This will ensure that the raw data does not have any repetitions that could make the analysis poor.
The procedure used to achieve this is as follows:

  • Select the entire document; Ctrl + A
  • Select Data on the toolbar
  • Under data tools, select remove duplicates

Using Operator Functions in Ms Excel

These operators are used to help users analyze raw data for easier interpretation.
They include:

  • Addition (+)
  • Subtraction (-)
  • Division (/)
  • Multiplication (*)
  • Exponent (^)

The above operators are used to get averages/ mean, MAX and MIN values, counts/ sum and predictions from raw data with ease.

ADDITION OPERATOR

  • This helps to find the sum of a set of data.
  • This can be done using COUNTS, COUNTIF, SUM and SUMIFS. 
=SUMIF(E2:E877,">100000") - For salaries equal to or above  $100000
Enter fullscreen mode Exit fullscreen mode

OR

=COUNTIF(G2:G877,"50") - For ages equal to or above 50 years
Enter fullscreen mode Exit fullscreen mode

MAX AND MIN VALUES

  • This is one of the ways to analyse data.
  • We end up arriving at the maximum/ highest or minimum/ lowest values of raw data. 
=MAX(D2:D22) - the highest value - e.g., age, salary, fares
Enter fullscreen mode Exit fullscreen mode

OR

=MIN(F2:F22) - the lowest value - e.g., age, salary, fares
Enter fullscreen mode Exit fullscreen mode

Using Lookup Functions in Ms Excel
These are divided into three:

  • VLOOKUP Function

This searches for data of relevance on the vertical items.

  • HLOOKUP Function

This searches for data of relevance on the horizontal items.

  • XLOOKUP or Index and Match Function

This indexes the column with the information that you want to know.

VLOOKUP in Use

Illustration One
How much is employee ID 10520s bonus?

=VLOOKUP(10520,A1:P877,15,FALSE)

 The answer is $630.98
Enter fullscreen mode Exit fullscreen mode

HLOOKUP in Use

Illustration Two
What is the marital status of ID 10102?

=HLOOKUP(10102,A1:AGS26,16,FALSE)

The answer to this is MARRIED.
Enter fullscreen mode Exit fullscreen mode

XLOOKUP in Use

Illustration Three
What is the name/ID of employee earning $77498?

=XLOOKUP(77498,E2:E877,B2:B877,"NOT FOUND")

The answer to this is John.
Enter fullscreen mode Exit fullscreen mode

PIVOTTABLES
Used to develop tables that interpret raw data to set conditions.

To create a suitable pivot table, this is the procedure:

  • Click Insert on the toolbar and select Pivot Table

How to Insert a PivotTable

  • Click OK to proceed.

PivotTable continued

  • On the right side under PivotTable Fields, drag and drop data title items into rows, column or filter sections to meet your analysis needs.

PivotTable Fields to the right of the screenshot

Developing Charts on Ms Excel for ease of Interpretation of Raw Data

  • This is used to interpret the analysed data.
  • Charts come in various forms and shapes - bars, pie chart and line charts.
  • The procedure used to get charts is as follows:
  • Click on the developed PivotTable
  • Pivot chart analyze
  • Pivot chart

  • Select the appropriate chart type from your available options

  • The developed PivotTable for analysis

PivotTable with Column, Row and Sum

  • Click on PivotChart on the Toolbar

Under PivotChart Insert the Appropriate Chart Type

  • Select the Chart Type to be displayed

Column Chart Displayed

For simple data analytics, Microsoft Excel is a helpful tool. It facilitates data organization, basic analysis, and straightforward result presentation. Ms Excel is a good starting place for novices learning advanced data analysis.

📌To all learners, keep on practising!

Top comments (0)