DEV Community

Cover image for The Beginner's Guide to Ms Excel
JOAN NJOKI
JOAN NJOKI

Posted on

The Beginner's Guide to Ms Excel

#analytics #beginners #datascience

Microsoft Excel is a spreadsheet program that allows users to organize, format, and calculate data in a spreadsheet and perform detailed analysis across both personal and professional settings.
Excel supports a wide range of tasks, including:

  • Data entry and organization
  • Creating charts and visualizations
  • Automating tasks with Excel macros
  • Advanced calculations with built-in functions
  • Managing large datasets with Pivot Tables
  • Time tracking and project management

Excel for Data Analytics
The first step to analyzing data on Excel is cleaning the data.

1. Removing Duplicates
ctrl+A to select the data in the spreadsheet > Click Data from the ribbon > select the 'remove duplicates" icon

2. Filtering Data/Conditional Formatting
Highlight the data to be filtered> click data>filter> select the desired field


For conditional formatting, click home>conditional formatting>highlight cell rules> select the desired criteria> select text color

3. Performing Calculations
Basic Excel functions include:

=SUM(E2:E15) computes the total for all the numbers in a column
=AVERAGE(E2:E15) computes the average for all the numbers in a column
=MAX(E2:E15) computes the maximum number in a column
=MIN(E2:E15) computes the minimum number in a column
=COUNT(E2:E15) computes the count for all the cells in a column
Enter fullscreen mode Exit fullscreen mode

Conditional functions
Same as basic functions, but they only work if the given condition is met.
Example: =SUMIF(E2:E877,">100,000") returns the sum of salaries from column E greater than 100,000
=SUMIFS(I2:I877,O2:O877,"Intern",P2:P877,"Berlin") returns the total performance score for interns in Berlin
Other functions include:

AVERAGEIF
COUNTIF
COUNTIFS
Enter fullscreen mode Exit fullscreen mode

Text functions
They're used to manipulate text data and include:

=UPPER(B2) converts the text in a cell into uppercase
=LOWER(B2) converts the text in a cell into lowercase
=PROPER(B2) capitalizes the first letter of text in a cell 
=TRIM(B2) removes any excess characters e.g space before the text
=LEN(B2) counts the number of characters in a cell
=LEFT(B2,x) extracts the first "x" characters from the text in a cell
=RIGHT(B2,x) extracts the last "x" characters
=CONCATENATE(B2," ",C2) joins text from multiple cells into one e.g combining the first and last name
=VLOOKUP finds data vertically
=HLOOKUP finds data horizontally
Enter fullscreen mode Exit fullscreen mode

4. Pivot Tables
They allow one to turn large datasets into clear summaries, grouped insights, and simple side‑by‑side comparisons.
How to create one:

  1. select all the data from the original dataset
  2. Click insert, select pivot table, choose to start on a new worksheet

  1. drag fields into rows, values and columns

One can also add slicers to filter out more data faster.

5. Visualizing data with charts/graphs.
Data visualization is commonly done using pie charts, line graphs and column charts

6. Summary
While advanced coding languages like Python and SQL handle massive datasets, Excel remains the industry standard for rapid data exploration, ad-hoc cleaning, and quick visualization. Its powerful features, specifically Power Query for data transformation and Pivot Tables for instant summarization, allow analysts to spot errors, test ideas, and turn raw numbers into simple data.

Top comments (0)