Microsoft Excel is a spreadsheet software that allows you to collect, organize, analyze, calculate, and visualize data efficiently. It organizes data into a grid of rows and columns, where each intersection is called a cell. Each cell can hold text, numbers, dates, or formulas that perform calculations automatically.
In real world, excel has been widely used in data analysis. For instance, where large data sets are involved, excel becomes instrumental in organizing the data and creating a dashboard for visualization. In essence this makes the data easily consumable for insights by management and aids in decision making.
In my first week of study as a data analyst, I have come to appreciate the capabilities of excel. What has really intrigued me is data clean up, use of pivot tables, charts and creating an interactive excel dashboard.
Did you know you can remove extra spaces from your text using text functions? Just use the following formula;'=TRIM(text)'. Additionally, you can combine two columns using the function "Concatenate". Here is how; =Concatenate(text). You have to specify if you need a separator, such that you put the space in parenthesis e.g. '=CONCATENATE(A2, " ", B2)'
The most insightful lesson has been on using pivot tables to show case relationship analysis, and using the pivot tables as primary data source for creating charts and excel dashboard.
A PivotTable is a tool that automatically summarizes large amounts of data. Instead of writing formulas, you simply drag and drop fields to group and calculate your data.
Example use: You have 5,000 rows of sales data. A PivotTable can instantly show you total sales per region per month instantly.
- Drag a field to rows - groups your data e.g., by product category
- Drag a field to values -calculates totals, count, average, Mid etc.,
- Drag a field to filters -narrows down the criteria of what to see.
NB: Slicers are also used in place of filters. To insert a slicer, click inside any pivot table, Go to insert tab, click "slicer" and then select the fields you want slicers for.
Charts visualize your data so patterns and trends are easier to understand. Excel offers many chart types, most commonly used being; bar chart, line chart, pie chart and column chart.
Lastly, creating an interactive dashboard on excel is the main output for visualization. You start by creating a title for your dashboard and KPIs by inserting a shape. You then copy the pivot tables previously mapped from your data source and insert slicers. Ideally, slicers should be on the left side, while KPIs should be on top. You then right click on the slicers then click "report connections". This was the dashboard is now interactive.
Lastly, you create an excel dashboard using pivot tables as the source data, but first you need to write the title of your dashboard and the KPIs.
The next step is to copy the pivot tables previously mapped into the dashboard, including slicers. You then create connections for the slicers such that any change is reflected in all charts.
Excel has been life changing to me and I am already utilizing some of the skills learnt so far in my day to day work activities.
Top comments (0)