Data Analytics With Excel
Excel is a spreadsheet column developed by Microsoft. It is the most used tool for data analytics and it serves as a foundation for every data analyst's journey.
Excel is used for:
Analysis
Data entry
Data management
Accounting
Budgeting
Data analysis
Visuals and graphs
Programming
Financial modeling etc
Excel Interface overview
Major Parts of Excel Interface
Title Bar
It show the workbook name, the application name(Excel), window control buttons, minimize, maximize and close. It helps in identifying the type of file you're working on and mange the working space
The Ribbon
The command center for the excel containing tabs such as Home, Insert, Formulas, Data, Review and View. Most analysis tools are found here.
Home Tab
Mostly used for cleaning operations.
Tools contains include clipboard Group, Font Group, Alignment Group, Number Group, Styles Group, Editing Group.
Data Tab
Major tools for analysis and data cleaning
Tools contain includes Sort and Filter, Remove Duplicates, Data Validation, Text to Columns, Flash Fill, Refresh and Connections.
Worksheet
The main working space made up of cells arranged in rows (numbers) and columns (letters). This where the data is entered, structured, formatted and analyzed.
Formula Bar
It display the formula in the current sheet selected and allow editing of the formulas and text.
Column and Row Headers
Columns are labeled A, B, C, D across the top vertical while Row Headers are numbered 1,2,3,4.. down the left side horizontally.
Worksheet Tabs
They are located at the bottom of the workbook for example (Sheet1, Sheet2) that switch between spreadsheets within a workbook
Status Bar
Is located at the bottom of the Excel window and serves as always on mini dashboard. It show quick insights such as Sum, Average, and Count.
Analytical Best Practices
Preserve Raw data - Do not work directly on the raw data, always duplicate it first and keep the original as back up reference
Use Clear Naming - Consistent sheet naming for example Raw_data
Color-Code Sheets - Use tabs color for visual structure.
Separate Calculation from Preparation - This makes it easier to update analysis without breaking the reports.
Data Entry
Avoid Leading spaces, always check on hidden spaces
Maintain Consistent Formats, each column should contain one data type only.
Use Ctrl + Enter, Fill multiple selected cells at once with the same value.
Double-Click the Fill Handle, auto fill the formulas down the large dataset.
Freeze panes
To improve readability and navigations in a large dataset
For example
Freeze Top Row
Freeze First Column
Freeze Panes (custom)
Data Cleaning
Process of preparing raw data for analysis.
Issues commonly faced are:
Blank Rows, where entries are skipped.
Mixed Capitalization, different key entries.
Extra Spacing, caused manual Typing
Inconsistent phone formats, mixed hyphens, spaces or missing
Amount formatting issues, with words, commas, or numeric text.
Multiple data formats, based on each data entry setting.
Duplicate header rows and merged cells
Data Cleaning Steps
Sheet Preparation
Copy pasted the original data to a new sheet and rename it cleaned.
Assign a tab Color by right click and choose tab color and choose the color.
Freeze the Top Pane, for visibility
-
Auto fit All Columns -for full visibility of each cell.
Remove Irrelevant Elements
Blank rows, row 12.
Duplicate rows, row 6 and 15.
Remove missing Values, cell B8.
Covert phone number to Text, the phone number column to avoid dropping zero.
Standardize the date formats for consistency.
Text fields are text branch column to text.
Use TRIM() Function, to remove the extra spaces
Use PROPER() Function, to standardize the CustomerNames column
Bold Header row to distinguish field names from data.
Apply subtle fill color to headers
(light blue).Use borders sparingly — only where they add clarity.
Ensure consistent alignment: Text left, Numbers right, Dates right
Apply number formatting: Currency, percentages, decimals
Adjust column widths so data is fully visible without excessive whitespace
Cleaned Dataset
Top comments (0)