My Journey at LuxDevHQ: MS Excel For Analytics

#luxdevhq #learning #productivity #analytics

Microsoft Excel running in Windows OS

TLDR;

This post seeks to provide an beginner-friendly introduction into using Microsoft Excel for basic data analysis.

Click here to Get Started, particularly if Excel is fully functional on your machine.

Introduction

Microsoft Excel is a proprietary spreadsheet editing software developed by Microsoft as part of its Microsoft 365 and Microsoft Office suites of software. ¹

First released in September 1985, it has evolved to support macOS, Android, iOS and the cloud. Some of it's key features, which make it invaluable for data analysis, (and which we shall utilize later) include:

Spreadsheets and Data Organization
Formulas and Functions
Data Visualization
PivotTables and PivotCharts
Data Validation
Conditional Formatting
Data Sorting and Filtering and so much more. ²

There exist alternative spreadsheet programs such as LibreOffice Calc and Google Sheets which are free and have the additional benefit of being able to process Excel's proprietary file format (XLS / XLSX).

However, for the course of this article, and for suggested optimal performance, we shall use Excel running on Windows OS. Follow this video as a guide on how to download and install MS Excel on Windows.

Overview

MS Excel is designed to work with spreadsheets. It enables performance of various functions such as data storage and organization, arithmetic calculation and report creation. Its basic window is a workbook consisting of user-created worksheets with a default sheet typically named Sheet1. This enables easy organization and categorization of data.

Typically, a sheet primarily consists of a workspace, which is a grid-based structure arranged in numbered rows and letter-named columns. This structure allows for efficient data entry and organization of manipulations like arithmetic operations. An individual intersection of a column and row forms a cell, which can be referenced by a value based on its intersection. For example, A1 references the cell composed from the intersection of row A and column 1. Each cell can hold data which can be of different types, including Number (default), Text, Date and Currency, among others.

At the top of the window is the command bar (Ribbon), organized into tabs (e.g. Home, Insert, View, etc.) that contain related groups, which in turn hold commands (e.g. buttons, menus, dialog launchers etc.) to perform tasks, offering quick access to features such as formatting, data analysis and charts. ³ Between the ribbon and work area is a row containing the Name Box, where the active cell is referenced, and Formula Bar, which shows either the active cell's content or formula.

Excel Workbook

Getting Started

We shall be working with this workbook containing a sample HR dataset for this article. Once your Excel is activated, download the file (File -> Download -> Microsoft Excel (.xlsx)) and open the workbook. You should have a window with the workbook in view mode as shown below.

Sample HR Dataset in MS Excel

We shall then rename the default worksheet (Sheet1) using the following steps:

Right click on the sheet tab.
Select Rename from the menu that appears.
Change the name of the original worksheet from Sheet1 to, say, OriginalData.

Renaming Original Worksheet

Data Cleanup

When working with Excel workbooks, particularly those populated by multiple people, data is likely to:
a) have discrepancies, such as duplicates and invalid data, and
b) be unpresentable, e.g. due to poor formatting.
Thus, when carrying out data analysis, the first task will most likely be cleaning up the data.

Creating Cleanup Worksheet

Starting off, we shall begin with duplicating the existing worksheet in preparation for cleanup. This is particularly important in preserving data integrity by ensuring the original data is intact, particularly if, for example, an issue occurs while working on the workbook.

To achieve this, while pressing on the Ctrl (or Command on macOS) key, drag and release the OriginalData sheet tab to duplicate. Rename this new sheet to CleanupData.

Cleanup Data Worksheet

Fitting Data

Primarily, we shall change the column widths to ensure that all the data is fully visible. Click on the top left corner of the work area (between A and 1 - there's an arrow pointing down) to select the entire work area. Then, in the Ribbon, click on Home tab then, in the Cells group, click on the Format button to reveal various formatting options. Select AutoFit Column Width to have Excel adjust all the columns based on the width of the largest content in each respective column.

Adjusting Column Widths

This process may need to be repeated continuously as the data is added or updated.

Setting Data Types

By default, all cell data in Excel is stored as Number and assigned General data type. However, this may not be appropriate for different data such as names, dates and prices. As such, it is important to have appropriate types assigned to the different data.

First, we shall convert Employee ID to Text. Why would we do this, particularly since the IDs are numeric? Well, unique identifier information, such as IDs, email addresses, phone numbers etc., are not involved in arithmetic operations outside of counting rows and as such should be saved as Text.

Click on the column label (A) to highlight and select the whole column. Then, in the Ribbon, click on Home tab then, in the Number group, click on the small "arrow down" icon in the bottom right corner.

Changing Data Type of Column

This will open a Format Cells dialog. Under Number tab, in the Category list, select Text then click OK.

Format Cells Dialog

The Employee ID will be formatted as Text, which is illustrated by the shifting of the column cell contents from right to left.

Employee ID formatted as Text

For practice, convert the other columns to the following data types.

Column Name	Data Type	Details
First Name	Text	-
Last Name	Text	-
Department	Text	-
Salary	Currency	Decimal places: 2 Symbol: $
Hire Date	Date	Type: 2012-03-14 Locale (location): English (United States)
Age	Number	Decimal places: 0 Use 1000 Separator (,)
Gender	Text	-
Performance Score	Number	Decimal places: 0 Use 1000 Separator (,)
Full-Time	Text	-
Bonus	Currency	Decimal places: 2 Symbol: $
Marital Status	Text	-
Education Level	Text	-
Work Experience (Years)	Number	Decimal places: 0 Use 1000 Separator (,)
Employee Type	Text	-
Office Location	Text	-
Project Count	Number	Decimal places: 0 Use 1000 Separator (,)
Last Promotion Year	Text	-
Remote Work Status	Text	-
Annual Training Hours	Number	Decimal places: 0 Use 1000 Separator (,)
Manager Feedback Score	Number	Decimal places: 1