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
OR
=COUNTIF(G2:G877,"50") - For ages equal to or above 50 years
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
OR
=MIN(F2:F22) - the lowest value - e.g., age, salary, fares
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
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.
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.
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.
Top comments (0)