Using MS Excel like the Pros
Microsoft Excel Training is a spreadsheet software used by individuals and organizations to calculate financial information, work with and represent statistical figures, or to simply maintain a set of records. Excel contains built-in functions for basic and advanced math, statistics and engineering calculations with the added advantage or writing your own.
Calculations can be simple, or iteratively complex that may require you to write a macro to perform time-consuming operations.
Excel also provides users with being able to manage data in a spreadsheet and represent it in a way you find most comfortable. Data entered into an excel sheet can be visually represented using pie charts, bar graphs, 3-D graphs and even scattergrams.
Charts in Excel
Charts are used to display a table of numeric data in a graphical format to make it easier to understand large quantities of data, trends and the relationship between different series of data. Before we create a chart, lets identify the elements of a visual chart:
1. The chart area of the chart.
2. The plot area of the chart.
3. The data points of the data series that are plotted in the chart.
4. The horizontal and vertical axis which represents data plotted in the chart.
5. The legend of the chart.
6. A chart and axis title.
7. A data label that you can use to identify the details of a data point in a data series.
Lets now look at the steps involved to create a chart from scratch:
- Enter data into a spreadsheet. Typically this data would be represented on an X and Y axis and would include data headers. Let's take a time-sheet as an example. A time sheet will have the tasks you are working on represented on one axis, and perhaps the day of the week, to map out the amount of time each task is consuming and the frequency or repetition of each task.
- Identify the data that is relevant to you and simply drag your cursor over all the cells that you wish to represent - please remember to select the header cells as well, so excel can correctly understand the data. You could choose from many chart formats (bar and pie charts being most common) and also have a combination of two charts in the same representation (a bar and a line chart is an example of something that is used commonly). Taking the time sheet as an example again, you could analyze tasks through the week by selecting the days of the week, the tasks and the associated hours.
- Change how chart elements are displayed by moving them within the chart, resizing them, or by changing the format. Elements you don't want can be deleted.
- Another way of creating a chart is to use the chart building wizard, located under Insert > Chart. This is particularly useful when you are creating chart types where data must be represented in a specific or unusual format. Using the wizard you can also easily browse through the pre-defined professional looking themes and pick the one that serves your need.
If you have spreadsheets with large amounts of data, pivot tables are a handy way of extracting the most significant information. Pivot tables are powerful and are widely used by organization to understand large amounts of structured data.
To illustrate the use of a pivot table let's take an example of an ecommerce product spreadsheet that contains the product name, product category, price, quantities sold and shipping locations. Ensure that all columns and rows have headers that let Excel and the user identify the data.
Managing Pivot tables
To insert a pivot table, on the spreadsheet click any single cell and on the Insert tab, click PivotTable.
A dialogue box will pop-up and excel should have automatically selected the data for you. If not, you can specify the table/range.
Next, you will see the pivot table field list. Here is where you decide on how you want your data represented. Lets use our previous example of the ecommerce product sales and say we want to identify what products sell the most, organized by shipping location.
From the field list drag and drop the fields to relevant areas. Shipping location can be dragged to “Report Filter” to be able to filter orders by location. Drag the product name to the “Row Labels” area and the quantities sold to the “Values” area.
Once applied, the pivot table should show you the products that sold the most filtered by the shipping location.
You can now further sort the list to view values in ascending or descending order. By default, Excel will summarize the data by summing the values. You can change these calculations to an average or count the data values.
To do this click any cell inside the “Total” column, right click and click on Value Field Settings and choose the type of calculation you wish to perform.
Using powerful modules like pivot tables and charts in Excel, you can represent and view data in amazing ways. Go ahead and give it a try!
>> To create powerful dashboards and analyze data like a professional take the Advanced MS Excel course with Manipal ProLearn.