4 Data analysis techniques in Microsoft Excel

If you are at work or school has to dive into the ocean and look for numbers in them confirmation of their hypotheses, you will definitely come in handy these techniques work in Microsoft Excel. How to apply - show using gifok.

Julia Perminov

Coach Softline Training Center since 2008.

1. summary tables

Basic tool for work with a huge amount of unstructured data from which to draw conclusions quickly, and do not bother with filtering and sorting by hand. Pivot tables can be created with just a few actions, and quickly set up, depending on how you want to display the results.

A useful addition. You can also create a pivot chart on the basis of summary tables, which are updated as they change. This is useful if you, for example, you need to create regular reports on the same parameters.

How to work

Raw data can be anything: data on sales, shipments, deliveries, and so on.

1. Open the file with a table whose data is to be analyzed.
2. Highlight data range for analysis.
3. Click on the tab "Insert» → «Table» → «Summary Table" (for "analysis" on MacOS "Data" tab in the group).
instagram viewer
4. You should see a dialog box "Create a PivotTable."
5. Adjust the display of data, that you have at the table.

Before us is a table with unstructured data. We can organize them and configure the display of the data that we have in the table. "The amount of orders" ship to "values" and "seller", "sale date" - in "Strings". According to different vendors for different years, then we calculate the sum. If necessary, you can expand each year, quarter or month - to obtain more detailed information for a specific period.

Set options will depend on the number of columns. For example, we have five columns. They just need to properly locate and choose what we want to show. For example, the amount.

It is possible to detail, for example, by country. Transfer the "Countries".

You can see the results of the sellers. Change the "Country" to "sellers". According to vendors, such results will be.

2. 3D-card

This method of visualization of geo-referenced data allows to analyze the data to find patterns having regional origins.

A useful addition. Coordinates nowhere do not need to register - just specify the correct place name in the table.

How to work

1. Open a spreadsheet file, which you want to visualize the data. For example, information on the different cities and countries.
2. Prepare the data to display on the map, "Home» → «Format as Table".
3. Highlight data range for analysis.
4. In the "Insert" is the 3D-map button.

Point on the map - it's our city. But just to the city, we are not very interesting - it is interesting to see the information tied to these cities. For example, the amount that can be displayed through the height of the column. When you hover over a column shows the amount.

Also informative is enough pie chart data. The size of the circle is given by the sum.

3. forecasts sheet

Often in business processes observed seasonal patterns that need to be taken into account when planning. forecast sheet - the most accurate tool for predicting to Excel, than all of the features that have been before and it is now. It can be used for the planning of commercial, financial, marketing and other services.

A useful addition. To calculate the forecast data will be needed for longer periods. Prediction accuracy depends on the number of the periods - better not less than one year. You need the same interval between data points (for example, a month or an equal number of days).

How to work

1. Open the table with the data for the period and the corresponding parameters, for example, by the year.
2. Highlight the two data series.
3. On the "Data" tab in the group, click "Forecast List".
4. In the "Creating the forecast sheet" select chart or bar chart for a visual representation of the forecast.
5. Select the date of the end of the forecast.

In the example below we have data for 2011, 2012 and 2013. It is important not specify the number, namely the period of time (ie, not March 5, 2013, and March 2013-go).

For the forecast for 2014, you will need two sets of data: the date and the corresponding values ​​of the indicators. Select both data series.

On the "Data" tab in the group "forecast," click on "forecast Sheet". In the window "Create forecast sheet" select presentation format of the forecast - a chart or histogram. In the "End of prognosis," select the end date, and then press the button "Create". The orange line - this is the forecast.

4. Quick analysis

This feature is perhaps the first step in what could be called business intelligence. It's nice that this functionality is the most friendly to the user in a manner: the desired result is achieved in just a few clicks. Nothing needs to be considered, it is not necessary to write down any formulas. Just select the desired range and choose which result you want to obtain.

A useful addition. Instantly, you can create different types of charts and sparklines (micrographs directly in the cell).

How to work

1. Open the table with the data for analysis.
2. Highlight the desired assay range.
3. When you select a band at the bottom always appears the button "Quick analysis". She immediately offers to make data a number of possible actions. For example, to find the results. We can find out the amount they are put at the bottom.

In a quick analysis as there are a few formatting options. See what value more and what is less possible in the very cells of the histogram.

You can also fill up the cells of multi-colored icons: green - the highest values, red - the smallest.

We hope that these techniques will help to speed up the work with data analysis in Microsoft Excel and quickly conquer the top of this complex, but such a useful in terms of working with the application numbers.

See also:

• 10 quick tricks with Excel →
• 20 Excel secrets that will help simplify →
• 10 Excel templates that will be useful in everyday life →