This
tab offers several tools for managing and analyzing a mass of data. The first part you can import data from several different
sources. You can also create links
to other Excel workbooks. The second grouping allows you to manage connections
between these files. Excel
also allows you to manage "small" data lists and offers options to sort and
filter data. There are
also other tools to manage data and validate their entry. You will find under the
button Scenario analysis three important tools: Scenario Manager, Goal seek and Data tables.
You can find a simple explanation of these options with links to pages of
exercises that give details about their operation. The outline
also helps to consolidate and analyze a data list.
Get External Data
Before beginning
the analysis, you must have data. You can use the data you enter in Excel.
But you can also
access and import data from external sources, which come from other
applications.
From Access
If you know
the software relational database Microsoft Access, you know you can create
queries from analysis. But you can
also transfer data from tables or queries to continue your analysis in
Excel.
Press the button From File Access. From
the Files window, select the Access database you want. Press the Open button.
A new window will open with a list of tables
and queries in the database. You must choose one that meets your needs.
Select the table or query of your choice. Press OK.
It
remains to determine the format and location data will be placed.
You can place them in a
table of data in a PivotTable or PivotChart.
For this exercise, select Table. Select the starting cell A1. Press OK.
The data is then transferred to
Excel. You can then use the controls and software options
for your analysis.
From Web
Some Web sites can provide
data. Il faut que vous
ayez un accès pour importer ces données dans Excel. You must have access
to import data into Excel. Vous devez entrer l'adresse du site ainsi que celle du
fichier que vous recherchez. You must enter the site address and that of
the file you are looking for.
From Text
You
can also import data provention simple text files. If
the data is well structured, they can easily be imported into Excel. Otherwise, it may take a bit of work.
Press the From text button. From the Files window, select the file format: PRN. TXT or. CSV
you want. Press the Import button.
An import wizard files of type "text", or having the extension
". Txt" will activate. You must answer a few questions to import the data into
Excel. VYou can always take bsck the steps to import if you experience
any difficulties.
The first step is to know how data is structured within the file. At this stage, the wizard wants to know if
the length of each field is fixed or delimited by special characters such as
Tab, an area to a semicolon or a comma or other character.
For this
exercise, select Delimited. Press the Next button.
The second step is to inform the assistant of
what character it should use to separate fields from each other.
For this
exercise, select Tabulation.
You will see how the data can be split. You can change or combine separator element if one does not suit
your needs.
Press the Next button.
At this stage, you can tell Excel what kind of
content there is in each column of the data list. Place the
cursor in each column and select the data format. Select the Standard format if you're
not sure.
Once you've determined the type of data for
each column, press the Finish button.
The last step is to determine where the data will
be deposited into your workbook.
Select the cell address of your choice. Press OK.
The
data will be imported into one worksheet in your workbook. You can then start your analysis.
From Other Sources
You can also access data from databases of the
company. These data may come from sources with very different
data structures.
Existing Connections
For some data sources, you do not
import data, Excel connects to the data source in the edit. You can then perform your analysis.
It is also
possible to use values from other files! The following exercise shows you how to do it. Then, how to manage links between
workbooks.
Open a new workbook. Enter 100 in the A1 cell. Save the workbook as test1.xlsx. Do not close the workbook.
The contents of the first workbook is
completed. The second
workbook will pick up this value in a moment. It's easier
to do this when the workbooks are open. Excel can open as many files as your computer's
memory allows. To move
from one workbook to another, go to the View tab and
select the Change window option.
From the Office Button, select New. Select Workbook. Press the Create button. OUOR Press the Ctrl and N keys.
Place the cursor in A1 cell. Press = key. Select the View
tab and change the options window and Test1.xlsx. OR Click
the cell you need. For this exercise, select A1 cell.
Press the Enter key. OUOR Press for the
bouton on the formula bar.
The formula
should be = [test1.xlsx] Sheet1!$A$1. The workbook
name is in brackets, followed by the name of the worksheet (Sheet1) and the
exclamation mark and the cell address ($A$1).
Save the workbook as Test2.xlsx. Close workbooks and Test1.xlsx Test2.xlsx. Open again the workbook Test2.xlsx.
Whenever you open the workbook,
Excel will inform you of the update links between workbooks has been
disabled. For this example, it is the
content of the workbook Test1.xlsx.
Press the Options button ...
To update the workbook,
choose the Enable this content and press OK.
Otherwise, the workbook will keep the data it has the latest update.
If you delete, move or rename the workbook reference, Excel
will not find it. It will go to the Workbook connections window to
correct the situation.
Select the Data tab. Select Edit Links.
From this window you can change the source data, check
status and even cut the ties.
Press the Close button.
Refresh All
In some cases it is necessary to update the data after major
changes. This section allows Excel to update the data it uses for analysis.
Connections
This command allows you to identify connections to data from
several sources.
Properties
Edit Links
Sort and Filter
This section allows you to sort and filter a data list.
Sort
You can
quickly sort a list in ascending or descending. Simply place the cursor in the column you wish to sort your data
list. Then
press a button to sort in ascending or descending. If you wish to
sort on multiple fields, press the Sort button and determine field order and
sorting order of each.
Although Excel is a spreadsheet program. But it offers several tools to
manage a small database. The terminology used by Excel is a data list. You can enter and modify and
remove data. The software also offers options to quickly find the information
you want.
There are also functions to analyze the mass of data you've accumulated.
This web page will give you an
introduction to the lists of data and how to use the operations mentioned
above.
The data
fields and records.
Here are some terms
that you should familiarize yourself. These
same terms are also used with ''real' 'database software such as Access, FileMaker
and Oracle.
Data:
Any information that you want to keep to help you in your
analysis.
Field:
The
same types of information are stored in the same field. Ex.: Nom,
Prénom, Taille, Salaire, Quantité ... Ex: Name, Size, Wages,
Quantity ... With Excel, the data from the
same field are retained in the same column. The field names are always the first line of a data
list.
Record:
All information concerning a person, thing or event.
With, Excel, each row of the data list is a record. So, a data list can contain records
on things, people or events.
Here is a list of
employee data to a company. As with the previous image, the first row of a data list always contains the field names. Each
following line contains a record that describes a person, thing or event
by the fields you have chosen. A data list must never contain blank rows. That is to say that all records must
follow each other without any interruption.
Excel allows you to quickly sort the contents of a data list
to help you quickly find what you're looking to bring order and organize
data.
You can use the Sort buttons ( and ) in the Filter
group under the Home tab or the Data tab to
sort a column by ascending or descending order. All records in the data list
will move according to the order you chose. However, you should be careful not to make a range of cells and
then try to sort it. Otherwise, only the
data and not the records will be sorted. This
will mix the records in the data list.
Excel offers
another more sophisticated way to sort the data list. The next exercise is to sort the records by last name and first
name.
Place the
cursor on any cell in the data list. From the Data tab, select Sort.
You can sort multiple
fields simultaneously in ascending, descending or in a custom list that you
already prepared.
For the first sort,
select the Last Name field. Keep it in ascending order. For the second sort,
select the First Name field. Keep it also in ascending order.
The sort in the first
box will have priority over the second box. This means that
Excel will sort by Last name followed by the first name. For records with
the same last name, Excel will sort the records by their first names.
Press the Options button.
This is just to
show you that you can sort according to the normal order or in a list that
you prepared in advance.
To exit this window, press the Cancel button.
Press the OK button to execute the sort.
The data list is
now sorted according to selected criteria.
Filter
The filter option allows you
to temporarily hide all records (lines) from the data list that does not meet
your criteria. An
automatic filter displays a list of all values of a field. You can then choose from
the list of values. You can even filter on multiple fields simultaneously.
An Advanced Filter lets you be more specific in your research
by creating a zone of criteria. You
can place the fields of your choice. Contrary to an automatic
filter you can use the operators =, <,> =,>,> =. <>
(Not equal) to
group multiple values. You can also put these criteria on multiple rows or
columns. Each test on the same lines is the equivalent of saying Criteria
Criterion 1 AND 2 AND 3 ... Criteria on different rows is
equivalent to saying Criterion Criterion 1 OR 2 ... You can combine the criterion in a combination of AND
and OR operators to create sophisticated filters.
A final advantage of an
advanced filter is that you can copy the resulting data to another location in
your Excel workbook. You can then analyze the results without affecting
the source data.
AutoFilter
Place the
cursor on any cell in the data list. From the Data tab, select the options Filter and AutoFilter.
In the first line of the data
list, now there are selection boxes with the names of the fields.
By clicking the
button to the right of the field name, you can limit what is displayed on
the list that you need.
The
selection box Name field, uncheck the box (Select
All). Select Bibeau. Press OK.
As you can see, there are two recordings, or
people in this case, fulfilling the request.
It is always possible to combine filters to further limit the results.
The remaining records are
hidden for now. Note also that there is a filter on
the Name field to indicate that the data
list is being filtered on that field. You can also set filters on multiple fields
simultaneously.
From the selection box of the Title field, select Administrator.
Clear
You can remove the automatic filters to display all data in the
data list.
From the Home tab, select Sort & Filter and Clear. From the Data tab, select Clear .
Reapply
You can reapply the last filter.
Custom AutoFilter
You can
accomplish a little more from the AutoFilter. You can filter a field on two
criterion.
Place the cursor anywhere on the field
column name. The selection box to the Name field and select Text Filters.
Note:
A similar option is also available
for numeric and chronological fields.
A new window will open giving you to place up to two
conditions. In addition, you
can choose from several conditions that best meets your needs.
For the first box, select the condition
is equal to. For the
second box, select the name Bibeau. Between the two conditions, select Or. For the third box, select the condition is equal
to. For
the fourth box, select the name Dupuis. To filter these conditions, press the OK button.
AutoFilter enables you to perform interesting research. But the advanced filter offers even more options and
flexibility.
From the Data tab, select the options Filter and Show All. OR The selection box to the Name field, select (All).
Advanced
Excel also allows you to
perform more complex searches. An advanced
filter gives you more control over the criteria and where the result will
affiché.Vous can choose the desired fields for the criteria and enter
values. You can also use the AND and OR
conditions in the form of a grid. Before
using an advanced filter, you need at least two elements: a data list and a test
area.
Add the
following information in the appropriate cells.
The first row of the criteria range must contain the
names of the fields you need for criteria. For this exercise, this Title and Category fields.
! Be careful with your spelling! If the name does not match the field
name of the data list, Excel will not show you the result you want.
The following lines include
criteria. By default, Excel assumes the = sign. For example, Title = Administrator. You can also use other ways to assess such as >, <,
<=,> =, <>. To apply
the terms AND and OR, you must use the grid to Excel.
The
criteria that are on the same line using the criterion. In the example, the third row of the
criteria area equals Title = Worker AND Category = 4.
The criteria that are on different rows is the equivalent of using the OR condition. This gives us
the result of several conditions simultaneously.
For this exercise, all criteria equivalent to showing all Administrator OR Title = Worker AND Category 4.
Place the cursor on any cell in the data list.
Otherwise, Excel will not want to apply the
filter.
From the Data tab, consolidation
of orders Sort and filter, select the Advanced option .
The advanced filter allows you two things:
filter the list or copy data to another location. For
the next step, let the action Filter the list instead. The next exercise is to use the action copy to another
location. Normally,
Excel would have correctly chosen the correct range of cells for the data
list. But always check. It
should not be content in the cells around the data list. We must also ensure that Excel has correctly chosen the
right area of criteria. Otherwise ...
Click in
the box area criteria. Select a range of cells needed for the area of criteria. For purposes of this exercise, select the cells I1 to J3.
Do not take
more cells need for the criteria range. Otherwise, the result will include every other possibility.
Press OK.
An
advanced filter gives you more options than automatic filter. You can
apply as many criterion as needed with a combinaison of AND and OR operators.
From the Data tab, select Filter.
Advanced filter with extraction
The filter also has another interesting option. Instead of hiding records that do not
meet the criteria, the filter can copy the filtered records to another location on
the worksheet. The next exercise is to use the advanced filter with the
option of copying the records.
Add the
following information in the appropriate cells.
The first
line of the destination of the advanced filter must contain field names, spelled
correctly, you want to see the result. The cells below them must be free. It is in these records that meet the criteria will be
copied.
Place the
cursor on any cell in the data list. From the Data
tab, consolidation of orders Sort and filter, select Advanced. From the list
of possible actions, select Copy to another
location.
The range of the data list and the criteria range should
be correct. But always
check. All that remains is to determine the
destination.
Place the cursor in the Copy to box. Select the cells with the field names you want to see the result. For this exercise, select the cells to I6
K6. Enable Unique
records.
The latter option avoids
repeatedly see the same record if it meets several criteria you entered.
Press OK.
Note:
Agrandissez la colonne of salaires si vous voyez le
symbole ###. Enlarge the Earning column if you see the ### in any cell.
Excel copies the records to the location you have requested.
Data Tools
Text to Columns
Remove Duplicates
Data Validation
Data validation allows you to place limits on the data to be accepted in a
cell or range of cells. You determine the criteria with the option
of data validation.
The Scenario Manager allows
you to create a comparison chart that looks at the result of several "scenarios"
that you create. You will then be able to decide which one best suits
your needs.
You want to reach a goal, a target value, but you can only
change one variable. What should be the value of this variable to achieve your
goal? You could try
several before you find your solution. Or you can ask Excel to find you the solution. You need to give it three pieces of
information. What is the cell that
contains the value of the objective? What is the
amount of the target? What is the cell address that you allow Excel to change to achieve this goal?
The data
tables one and two variables allows you to quickly create a chart that shows the
result of a cell by several possibilities.
Outline
The
outline view allows you to group data together and analyze the results.
Place the cursor within the grounds of
the data list. If necessary, sort the fields (columns) on the appropriate
values before using the subtotal. Press Subtotal.
Select the field you
want to consolidate your data and have a subtotal.
You can
request several types of sub-total under the operation you want: Sum,
Count, Average, Min, Max, Product, values, standard deviation, population
standard deviation, variance, variance of the population.
You can also choose which field (column) you wish to
have a subtotal.
Once you've selected
all the options, press OK.
Group
Ungroup
Subtotal
Show/Hide Detail
Analysis
Excel allows you
to select additional tools, called add-ins such as Solver. This tool has existed for
several versions of Excel and used to find the optimal solution for your
model. However, you
must determine the constraints that will limit your model. Otherwise, it will tend to go to
infinity. This option must be
enabled to use it. There is a page of practice that describes how to enable the
option and how to use it. Click
on the link at the bottom of this section.
If you know
the software relational database Microsoft Access, you know you can create
queries from analysis. But you can
also transfer data from tables or queries to continue your analysis in
Excel. 
Press the button From File Access. 
Some Web sites can provide
data. Il faut que vous
ayez un accès pour importer ces données dans Excel. You must have access
to import data into Excel. Vous devez entrer l'adresse du site ainsi que celle du
fichier que vous recherchez. You must enter the site address and that of
the file you are looking for. 
You
can also import data provention simple text files. If
the data is well structured, they can easily be imported into Excel. Otherwise, it may take a bit of work.
For some data sources, you do not
import data, Excel connects to the data source in the edit. You can then perform your analysis.
In some cases it is necessary to update the data after major
changes. This section allows Excel to update the data it uses for analysis. 
This command allows you to identify connections to data from
several sources. 
You can
quickly sort a list in ascending or descending. Simply place the cursor in the column you wish to sort your data
list. Then
press a button to sort in ascending or descending. If you wish to
sort on multiple fields, press the Sort button and determine field order and
sorting order of each. 
and 
) in the Filter
group under the Home tab or the Data tab to
sort a column by ascending or descending order. All records in the data list
will move according to the order you chose. However, you should be careful not to make a range of cells and
then try to sort it. Otherwise, only the
data and not the records will be sorted. This
will mix the records in the data list. 
The filter option allows you
to temporarily hide all records (lines) from the data list that does not meet
your criteria. An
automatic filter displays a list of all values of a field. You can then choose from
the list of values. You can even filter on multiple fields simultaneously. 
Place the
cursor on any cell in the data list. 
to indicate that the data
list is being filtered on that field. You can also set filters on multiple fields
simultaneously. 
.
You can reapply the last filter.
.
The
outline view allows you to group data together and analyze the results.
Excel allows you
to select additional tools, called add-ins such as Solver. This tool has existed for
several versions of Excel and used to find the optimal solution for your
model. However, you
must determine the constraints that will limit your model. Otherwise, it will tend to go to
infinity. This option must be
enabled to use it. There is a page of practice that describes how to enable the
option and how to use it. Click
on the link at the bottom of this section. 
