Introduction
This tab offers you many tools to manage and analyze data coming from simple data list in Excel to information from massive databases. It gives you a way to connect to different types of databases. After that, you can sort the information according to one or many criteria. You can also filter the information with simple to avanced filters. With Data validation, you can make sure that the user will enter values that are within certain bounds. You can force the user to enter data that is between two values (highest and lowest) or from a list. The what if analysis button has three tools that you can use: Scenario manager, Goal seek and Data table. Each can help you find the answers that you seek in different ways. Then, there is the case for the Solver. It's an "add-in" that has been available for a long time with Excel. You only need to activate it. Its role is to help you optimize your model. You only need to give it four things: the cell you want to optimize, whether you want to maximize, minimize or reach a certain value, select the cells you Excel to use to optimize your model (variable cells) and their constraints. You can "play" with the options. It's a simple that just works when you use it correctly. You can buy better and more specialized solver tools from solver.com. |
Get External Data
 |
Before we can start the analysis, you must have a data list. You can use data that is already entrered in Excel. But you can also access and import of data from external sources; that come from other applications. |
From Access
 |
If you know Microsoft Access, you know that you can analyze your data with queries. But you can also transfer the data from tables or from queries to continue your analysis with Excel. |
From Web
|
Some websites can provide you with very useful data. You can access it and import it into Excel. |
From Text
 |
You can also import data coming from simple text files. If the data is structured well, it can easily be imported to Excel. |
From Other Sources
 |
You can also access data from corporate databases. This data can come from many sources. The data structures can be very different. |
Existing Connections
 |
You cannot import data from every data sources. For some data types, Excel can connect to a database. You will then be able to do your own analysis. |
 |
|
Refresh All
 |
In some cases, you must update the data after important changes. This section allows you to refresh the data for your analysis. |
Connections
 |
The command allows you to determine of connexions to a database from many sources. |
Properties
 |
Edit Links
 |
|
Sort and Filter
 |
This section allows you to sort and of filter a data list. |
Sort
 |
You can quickly sort a list by ascending or descending order. You only need to place the cursor in the column that you want sort your data list. Then, press the one of the sort buttons (ascending or descending order). If you want sort on many fields, press Sort button and determine the order in witch the fields will be sorted. |
Filter
|
The filter command allows you to mask temporarely all the recordings (rows) from the data list that doesn't respond to your criteria. The filter will only show the list of all the values that correspond to the criteras selected. You can also filter for many fields at the same time. An advanced filter allows you to do more precise searches by creating a criteria range where you can place many criteria at the same time. You can place the fields of your choice. Unlike the filter option, you can use you can place many criteria in as many rows or columns that is required. Each criteria for the same rows is the equivalent the logical operator AND: Criteria1 AND Criteria2 AND Criteria3 ... Criterias on different rows is the equivalent of using the logical operator OR: Criteria1 OR Criteria 2 ... You can combine many criteria with the AND and OR logical operators to create elaborate and specific filters. Another advantage to an avanced filter is that you can recopy the result to another location in your worksheet. After that, you can analyze only the result without affecting the original data list. |
Clear
 |
You can remove automatic filters to show the entire data list. |
Reapply
 |
As you can reapply them if necessary. |
Advanced
 |
An advanced filter will allows you more control by selecting more criteria and where the result will be shown. |
Data Tools
 |
The data tools give you some options to convert the current data into a more useful form, to remove duplicate data, to validate the data that was entered or to use various tools to analyze your Excel models. |
Text to Columns
 |
This option allows you to convert data that was entered or came from a database into a more useful form. |
Remove Duplicates
 |
This option look at the content of your data list and removes duplicate data. It's much easier than trying to do it alone. |
Data Validation
 |
Data validation allows you to place limits to your values that will be accepted in a cell or the range of cells. You can determine the criteria with the data validation option. |
Consolidate
 |
|
What-If Analysis
 |
|
Scenario Manager
Follow this link to view the exercise for this command
The scenario manager allows you to create a comparaison table filled with the results of many "scenarios" that you created. You will then be able to decide which "scenario" best suits your needs. |
Goal Seek
Follow this link to view the exercise for this command
You want to reach a target, a target value, but you can only change a single variable or cell. To put it simply, what must be the value of this cell so that I can reach my target value? You could try to enter many value until you finaly reach your goal. Or you can let Excel find you the solution. But you must give it three informations. What is the cell address that must contain the devired goal value? What is the value that you want to reach? What is the cell address that you will allow Excel to change so that it can try to attain your goal value? |
Data Table
Follow this link to view the exercise for this command
The Data Table to one and two variables allows you to create quickly a table that affiche the result to a cell with many possibilities.
Outline
 |
These options allow you to group rows or columns together. You can group them and decide to show or hide the group. You can concentrate on the totals or the details. Excel also offers an automatic option to group/ungroup your model. Excel also has an option to add subtotals to a group of values. But you have to make sure that the values are sorted properly before adding subtotals. |
Group
 |
You will notice on top or on the left side of your worksheet a line with a + or - sign before it. You can press that + or - button to show or hide the group you just created. By hiding the values, you can concentrate on the totals. The Auto outline options allows Excel to determine how to group the rows and columns of your worksheet. |
Ungroup
 |
You can decide to remove a group.
You can also let Excel ungroup everything by using the Clear Outline command.
|
Subtotal
 |
Excel offers an interesting option of adding rows in a data list that show the subtotal of a group. Not only can you determine the sum, but also the count, the smallest, the highest, average values among other mathematical functions. But, before using this option, you must sort the data list according to the fields you wish use the subtotal. |
Show/Hide Detail
 |
After you have grouped many rows or columns, you can either group or ungroup each one individualy or all at the same time by using Show/Hide details. |
Analysis
 |
Another group of commands can be activated to help you analyze and solve some problem that you may have with your workbook. You can easily activate some add-ins such as Analysis Toolpack or the Solver to help you in these cases. |
Press the Office button. For Excel 2010 and 2013 users, press the File Tab.
Select Options.
From the grop of options, select Add-ins.
At the bottom of the window, make sure that you have the Manage Excel Add-ins option selected and press the Go... button.
From the list of add-ins that you can activate, select Solver and press the OK button.
A new Analysis group of commands will appear at the end of the Data tab.
Solver
Follow this link to view the exercise for this command
The Solver is an add-in to Excel. It's used to find the optimal solution to a problem according to the criteria that you have determined.
Activate the solver
Use the Solver to solve optimization problems.
| You like what you read?
Share it with your friends. |
This site is hosted by 1&1.com
