This tab offers you many tools to manage and analyse 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 criterias. You can also filter the information with simple to avanced filters. With Data validation, you can make sure that the user will enter 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 solvers 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 to of data from bases of data of the entreprise. Ces data can provenir of sources with of structures of data very différentes.
Existing Connections
You cannot import data from every data sources. For some types of data, Excel connects to the source of data. You will then be able to do your analyses.
Connections
Refresh All
In some cases, it is necessary to update the data after important changes. This section allows you to update the data for your analysis.
Connections
Ce command allows you to determine of connexions to of data from many sources.
Properties
Edit Links
Sort and Filter
This section allows you to sort and of filter one list of data.
Sort
You can quickly sort a list by ascending or descending order. You only need to place the cursor in the column for laquelle you want sort your list of data. Ensuite press the a of boutons to sort by order croissant or décroissant. If you want sort for many champs, il faut press for the button Sort and determine the order of champs to sort and also the order of chacun.
Filter
The option filter allows you to masquer temporairement all the enregistrement (rows) of the list of data that ne répondent pas to your criterias. A filter automatique affiche the list of all the values to un champ. You can ensuite choose parmit cette list of values. You can same filter for many champs by same time.
A filter avancé allows you to be more précis in your recherches by créant one area of criterias. You can y place the champs of your choice. Contrairement to a filter automatique you can use the opérateurs =, <, >=, >, >=. <> (différent of) to regroup many values. You can also place these criterias for many rows or columns. Each criteria for the same rows is the équivalent of dire Critère1 ET Critères 2 ET Critères 3 ... Des criterias for of rows différentes is the équivalent of dire Critère 1 OU Critère 2 ... You can combiner of combinaisons of criterias and of opérateurs ET and OU to create of filters very élaborés.
A dernier avantage to un filter avancé is that you can recopier the data résultantes to a autre location in your folder Excel. You will ensuite analyser the result sans affecter the data sources.
Clear
You can remove automatic filters to show all the entire data list.
Reapply
As you can reapply them if necessary.
Advanced
An advanced filter will allows you more control by selecting more criterias and where the result will be shown.
Data Tools
Text to Columns
Remove Duplicates
Data Validation
A 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 criterias with the data validation option.
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.
You want to reach a target, one value cible, but you can only change one variable. Quel doit be the value of cette variable to reach your target? You pourriez make many essais avant of find your solution. Or you can ask to Excel of you find the solution. It suffit of lui donner three informations. Quelle is the cell that contains the value of the target? Quel is the amount of the target? Quelle is the cell address dont allows you totez to Excel to change to reach cet target?
