DEV Community

Beverline Otiende
Beverline Otiende

Posted on

Evaluating Microsoft Excel as a Data Analytics tool in Modern Business Decision Making

#analytics #data #microsoft #productivity

Microsoft Excel remains one of the most commonly used tools for Data Analytics in businesses and organizations. This is mainly because it is very easy to use, it is easily accessible and powerful enough to handle many data analysis tasks.
This article explores ways in what Excel is and how it is used in data analysis.

WHAT IS MICROSOFT EXCEL
Excel is a spreadsheet program that can be used to organize data, to perform calculation, clean data, create visualizations and analyze data.
Data Analytics on the other hand involves examining raw data in order to identify patterns and draw data-driven insights that support decision making in organizations and businesses.
Excel as a tool.

EXCEL TEXT FUNCTIONS
Text Functions in Excel are used as part of data cleaning to get a section of data or to extract relevant information from text fields.
They include;
LEFT Function: extracts text from the left side of a cell. Its syntax is,

=LEFT(text, number_of_characters_to_extract)
Enter fullscreen mode Exit fullscreen mode

RIGHT Function: extracts text from the right side of a cell. Syntax is,

=RIGHT(text, number_of_characters_to_extract)
Enter fullscreen mode Exit fullscreen mode

MID Function: extracts text from the middle of a cell. Syntax is

=MID(text, start_position, number_of_characters)
Enter fullscreen mode Exit fullscreen mode

LEN Function: counts the number of characters in a text. syntax is,

=LEN(text)
Enter fullscreen mode Exit fullscreen mode

TRIM Function: removes extra spaces from a text. Syntax is,

=TRIM(text)
Enter fullscreen mode Exit fullscreen mode

CONCATENATE Function: joins texts from different cells. Syntax is,

=CONCATENATE(text1, " ", text2)
Enter fullscreen mode Exit fullscreen mode

DATA VALIDATION
This is done to restrict what is entered in a cell during data entry to avoid errors.

REMOVING BLANKS
Removing blanks is a common aspect in data cleaning.
To remove blanks;

  1. Choose column you want to remove blanks from
  2. Go to Home tab and then to Find and Select
  3. Go to Special then Remove Blanks.

PERFORMING BASIC ARITHMETIC OPERATIONS IN EXCEL
With Excel you can do Basic Addition, Subtraction, Division, Multiplication, Power and many more.
All you have to do is put "=" before you write what you want to add or Multiply or Divide;
e.g
= 2+2 gives you 4 for Addition
= 10/2 gives 5 for Division
= 5* 3 gives 15 for Multiplication
=17-9 gives 8 for Subtraction
= 2^3 gives 8 for Powers

AGGREGATE FUNCTIONS
We have special functions we use when performing arithmetic operations like Sum, difference, Average just to mention but a few.
Assume you have a column of data from range E2 TO E16 you want to calculate different parameters.

Adding the numbers together

 = SUM(E2:E16)

SUM function

Finding the Average
 = AVERAGE(E2:E16)
Average function

Finding Maximum Value
=MAX(E2:E16)
Maximum function

Finding Minimum value
 =MIN(E2:E16)

Minimum Function
Finding the Count
 =COUNT(E2:E16)

Count function

LOGICAL FUNCTIONS
Logical functions in Excel help you make decisions in your data.
They check whether a condition is TRUE or FALSE and then return a result based on your condition.
They include;

IF Function: Checks a condition and returns a value if the condition is True and another value if it is False. Its syntax is shown below;

=IF(condition, value_if_true, value_if_false)
Enter fullscreen mode Exit fullscreen mode

AND Function: Returns TRUE only if all conditions are met. Its syntax is shown below;

=AND(condition 1, condition 2)
Enter fullscreen mode Exit fullscreen mode

OR Function: Returns True if at least one of the conditions is True. Its syntax is shown below;

=OR(condition 1, condition 2)
Enter fullscreen mode Exit fullscreen mode

NOT Function: It reverses the logical result. Its syntax is shown below;

=NOT(condition)
Enter fullscreen mode Exit fullscreen mode

PIVOT TABLES
A Pivot Table is a powerful Excel tool that helps us summarize, analyze and explore large amounts of data without writing formulas.
To insert a Pivot table;

  1. Select your entire table
  2. Go to the Insert tab and Select Pivot Table(on the extreme left)
  3. Select New Worksheet then press Okay
  4. Then drag and drop your columns on the Row, Value and Column section on the Right depending on the summary you want. On the Values section, you can specify if you want Sum, Average, Count or anything else. To do that, you select under the 'Values' section, Select your Value, the Value field settings, select the summary you want eg Sum, count. then press Okay

Inserting Pivot table

Select New worksheet

Blank Pivot table

Pivot Table with Rows and Values

Value field settings

Pivot table with Rows, Columns and Values

SLICERS
Slicers provide Visual Filters for Pivot tables and pivot charts.
To insert a slicer for a Pivot Table;

  1. Select anywhere on the Pivot Table
  2. Go to PivotTable Analyze Tab and Select Slicer
  3. Select the Slicers you want to have and click Okay. Based on your selection on your slicer, your data values change.

Inserting Slicer

Selecting your slicers

Slicers

Filtering with slicers

PIVOT CHARTS
Pivot Charts are used for visualizations and are made from Pivot tables
To insert a Pivot chart;

  1. Select anywhere on your Pivot Chart
  2. Go to the PivotTable Analyze Tab the select Pivot Chart
  3. Select the chart you want to input either Column chart, Bar chart, Pie Chart among others depending on your data and analysis.
  4. Modify the chart according to your preferences.

Inserting pivot chart

Selecting a chart

A column chart

Doughnut chart
Pivot charts show a graphical representation of Pivot tables and you can use them depending on your data and analysis.
Commonly used charts include:

  1. Line charts: To show trends over time
  2. Column charts: Compares values across categories using vertical bars.
  3. Bar charts: Compares values across categories using horizontal bars.
  4. Pie/Doughnut Chats: Shows proportions of a whole.
  5. Combo charts: Combines two chart types.
  6. Histogram: Shows distribution of numeric data.

BUILDING A DASHBOARD
An Excel Dashboard is a visual summary of key metrics and trends, usually on one worksheet and is designed for quick understanding by managers and stakeholders.
Key considerations for a Dashboard include;
• One screen only (no scrolling)
• Focus on key KPIs, not raw data
• Consistent colors and fonts
• Clear titles and labels
• Interactive but simple
When building a dashboard, open a new Excel worksheet, then copy and paste the Pivot Charts you would like to have in there plus the slicer. Ensure your Dashboard is neat and organized.
Also use dashboard colors according to the theme of the company you are presenting to.

Dashboard example

Top comments (0)