Concepts

By Team 2Elisa RamosCinthya MontalvoNora RomeroAgustin ValadezMatias RoquetaJorge SalazarEva Morales

In a spreadsheet the CELL is defined as the space where a specified row and column intersect. Each CELL is assigned a name according to its COLUMN letter and ROW

number. When referencing a cell, you should put the column first and the row second.

In a spreadsheet the COLUMN is defined as the vertical space that is going up and down the window. Letters are used to designate each COLUMN'S location.

In a spreadsheet the COLUMN is defined as the vertical space that is going up and down the window. Letters are used to designate each COLUMN'S location.

Labels are text entries. They do not have a value associated with them. We typically use labels to identify what we are talking about.

Macro Template

The macros are special instructions that control Excel. By writing macros, a series of instructions in a module or macro sheet in the workbook, Excel may have run any number of commands or actions for you.

A template is a workbook that has been adapted to suit a specific need. You can format the workbook, add macros, insert text and images and change the page layout so that the workbook contains all the key information.

A spreadsheet is the computer equivalent of a paper ledger sheet. It consists of a grid made from columns and rows.

Range

A range is a group of cells. A cell is also considered a range. When you write ranges in Excel, you use parenthesis and between the two names of the cells, the beginning one and the ending one, you put a colon. Ex. (A1:A7)

Relative References

• When you have a formula on a cell, and you copy the formula to paste, for example, in the same row, but different column, the formula will automatically adjust to the new column it is.

Absolute References

• When you have a formula on a cell, and you copy the formula and you will paste it, on another cell, of any column or row, the formula will stay the same because it has the symbol “$” on it. The “$” makes the formula stay the same.

Relative References In the example above, Row 8 contains Relative References. Excel has

automatically adjusted the copied formulas based on their location. For example, by copying the formula =SUM(B3:B7) from cell B8 to cell C8, Excel

automatically adjusted it to read =SUM(C3:C7). The formula becomes =SUM(D3:D7) after being copied to cell D8.

The formula has changed relative to its location.

Absolute References

In the example above, Row 9 contains constant or Absolute References. The first part of the formula is not adjusted, despite its new location. When the formula =$B$10-B8 was copied from cell B9 to cell C9, the B10 reference was not changed, even though the B8 reference was. The formula became =$B$10-C8. The constant or absolute reference to cell B10 was made by placing a $ in front of the column and row designation, in this case $B$10. Thus, no matter where the formula is copied, $B$10 stays the same. The formula becomes =$B$10-D8 after being copied to cell D9. The $B$10 reference is constant, or absolute.

Cell Formats

• Cell formats allow you to change the way cell data appears in the spreadsheet. It is important to keep in mind that it only alters the way the data is presented, and does not change the value of the data.

• The formatting options allows for monetary units, scientific options, dates, times, fractions, and more. Positive and negative values can have different colors and formats for aiding in keeping track of values.

• There are also a large variety of date and time formats for virtually any time and date format one can think of. Formatting also allows you to set font, background color, and borders for selected cells.

IF

• The next function we will discuss is IF. The IF function will check the logical condition of a statement, and return one value if true, and a different value if false.

• The formula is: “=IF (condition, value-if-true, value-if-false)”

• value returned may be either a number or text

• if value returned is text, it must be in quotes

Paper files or Electronic files• When you have your information

in paper, you need a big place to put them, also you can simply lost them, but when you have them like electronic files, it is easier to have them in order, and it is more difficult to loose them, because you have them all in the same place.

• Also if you have a file on paper, and you need a copy of it, you will need to write it again or make a copy, but if you have electronic files is very easy to make a copy, also you can make paper files if you have them first on electronic ways.

Reliability and Data Integrity.• Electronic files have a risk of

being stolen by a hacker, but the files can be easily protected, also you have a facility to access them. Data would be easily stored and safer than having it on paper, because you can loose the paper file in any place, and in electronic media, you can’t loose it so easily.

Transfer of DataTransfer of Data

Between a Databaseand a Spreadsheet

Between a Databaseand a Spreadsheet

There are several ways to exchange data between Microsoft Office Access and Microsoft Office ExcelThere are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel

To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.

To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.

To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

NotesNotes The word "import" has two

different meanings between Excel and Access. In Excel, when you import, you make a permanent connection to data that can be refreshed. In Access, when you import, you bring data into Access once, but without a permanent data connection.

The word "import" has two different meanings between Excel and Access. In Excel, when you import, you make a permanent connection to data that can be refreshed. In Access, when you import, you bring data into Access once, but without a permanent data connection.

You cannot save an Excel workbook as an Access database. Neither Excel nor Access provides functionality to create an Access database from Excel data

You cannot save an Excel workbook as an Access database. Neither Excel nor Access provides functionality to create an Access database from Excel data

Work with Access data in Excel

Work with Access data in Excel

You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or the many functions that are not available in Access.

You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or the many functions that are not available in Access.

Copy Access data into ExcelCopy Access data into Excel1. Start Access, and then open the table, query, or form that

contains the records that you want to copy.

2. On the Home tab, click View, and then click Datasheet View.

3. Select the records that you want to copy.

4. Click Copy.

5. Start Excel, and then open the worksheet where you want to paste the data.

6. Click in the upper-left corner of the worksheet area where you want the first field name to appear.

7. To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell.

8. Click Paste.

1. Start Access, and then open the table, query, or form that contains the records that you want to copy.

2. On the Home tab, click View, and then click Datasheet View.

3. Select the records that you want to copy.

4. Click Copy.

5. Start Excel, and then open the worksheet where you want to paste the data.

6. Click in the upper-left corner of the worksheet area where you want the first field name to appear.

7. To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell.

8. Click Paste.

Export Access data to ExcelExport Access data to Excel

By using the Export Wizard in Access, you can export an Access database object, such as a table, query, or form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details for future use, and even schedule the export operation to run automatically at specified intervals.

By using the Export Wizard in Access, you can export an Access database object, such as a table, query, or form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details for future use, and even schedule the export operation to run automatically at specified intervals.

Connect to Access data from Excel

Connect to Access data from Excel

To bring Access data into Excel, you can create a connection stored in an Office Data Connection file (.odc), to the Access database and retrieve all of the data from a table or query. The main benefit of connecting to Access data instead of importing it is that you can periodically analyze this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically updateyour Excel workbooks from the original Access database whenever the database is updated with new information.

To bring Access data into Excel, you can create a connection stored in an Office Data Connection file (.odc), to the Access database and retrieve all of the data from a table or query. The main benefit of connecting to Access data instead of importing it is that you can periodically analyze this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically updateyour Excel workbooks from the original Access database whenever the database is updated with new information.

1. Click the cell where you want to put the data from the Access database.

2. On the Data tab, in the Get External Data group, click From Access.

3. In the Look in list, locate and double-click the Access database that you want to import.

4. In the Select Table dialog box, click the table or query that you want to import, and then click OK.

5. In the Import Data dialog box, do the following:

1. To view the data as a table, select Table.

2. To view the data as a PivotTable report , select PivotTable report.

3. To view the data as a PivotChart and PivotTable report, select PivotChart and PivotTable report.

1. Click the cell where you want to put the data from the Access database.

2. On the Data tab, in the Get External Data group, click From Access.

3. In the Look in list, locate and double-click the Access database that you want to import.

4. In the Select Table dialog box, click the table or query that you want to import, and then click OK.

5. In the Import Data dialog box, do the following:

1. To view the data as a table, select Table.

2. To view the data as a PivotTable report , select PivotTable report.

3. To view the data as a PivotChart and PivotTable report, select PivotChart and PivotTable report.

6. Optionally, click Properties to set refresh, formatting, and layout options for the imported data, and then click OK.

7. Under Where do you want to put the data? do one of the following:

6. To return the data to the location that you selected, click Existing worksheet.

7. To return the data to the upper-left corner of the new worksheet, click New worksheet.

8. Click OK.

9. Excel puts the external data range in the location that you specify.

6. Optionally, click Properties to set refresh, formatting, and layout options for the imported data, and then click OK.

7. Under Where do you want to put the data? do one of the following:

6. To return the data to the location that you selected, click Existing worksheet.

7. To return the data to the upper-left corner of the new worksheet, click New worksheet.

8. Click OK.

9. Excel puts the external data range in the location that you specify.

Work with Excel data in Access

Work with Excel data in Access

You may want to work with Excel data in an Access database to take advantage of Access data management, security, or multiuser features. Although there are many useful features in Access, there are two features that users might find particularly useful for their Excel data: Reports and Forms.

You may want to work with Excel data in an Access database to take advantage of Access data management, security, or multiuser features. Although there are many useful features in Access, there are two features that users might find particularly useful for their Excel data: Reports and Forms.

Reports  If you are familiar with designing Access reports and you want to summarize and organize your Excel data in this type of report, you can create an Access report. For example, you can create more flexible reports, such as group and summary reports, printed labels, and graphical reports.

Reports  If you are familiar with designing Access reports and you want to summarize and organize your Excel data in this type of report, you can create an Access report. For example, you can create more flexible reports, such as group and summary reports, printed labels, and graphical reports.

Forms  If you want to use a form to find or to display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the order of columns in your worksheet, or view a lengthy row of data more easily on one screen.

Forms  If you want to use a form to find or to display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the order of columns in your worksheet, or view a lengthy row of data more easily on one screen.

Copy Excel data into AccessCopy Excel data into Access1. Start Excel, and then open the worksheet with the data that you

want to copy.

2. Select the row

3. s that you want to copy.

4. On the Home tab, in the Clipboard group, click Copy .

5. Start Access, and then open the table, query, or form in which you want to paste the rows.

6. On the Datasheet tab, in the Views group, click View, and then click Datasheet View.

7. To replace records, select those records, and then on the Home tab, in the Clipboard group, click Paste.

8. To append the data as new records, on the Home tab, in the Clipboard group, click Paste Append on the Edit menu.

1. Start Excel, and then open the worksheet with the data that you want to copy.

2. Select the row

3. s that you want to copy.

4. On the Home tab, in the Clipboard group, click Copy .

5. Start Access, and then open the table, query, or form in which you want to paste the rows.

6. On the Datasheet tab, in the Views group, click View, and then click Datasheet View.

7. To replace records, select those records, and then on the Home tab, in the Clipboard group, click Paste.

8. To append the data as new records, on the Home tab, in the Clipboard group, click Paste Append on the Edit menu.

Import Excel data into Access

Import Excel data into Access

To store data from Excel in an Access database, and then use and maintain the data in Access from then on, you can import the data. When you import data, Access stores the data in a new or existing table without altering the data in Excel. You can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

To store data from Excel in an Access database, and then use and maintain the data in Access from then on, you can import the data. When you import data, Access stores the data in a new or existing table without altering the data in Excel. You can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

Link to Excel data from Access

Link to Excel data from Access

You can link an Excel range into an Access database as a table. Use this approach when you plan to continue maintaining the range in Excel but also want it to be available from within Access. You create this type of link from within the Access database, not from Excel.

You can link an Excel range into an Access database as a table. Use this approach when you plan to continue maintaining the range in Excel but also want it to be available from within Access. You create this type of link from within the Access database, not from Excel.

When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel are reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file.

When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel are reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file.

• An excel presentation is a tool that we have to show a control about we need or have.

• For example: we have a lot of business but we do not have order so excel help us to have there order.

• So we need some tools to have this order.

• The graphics are one of these tools.

• To make a graphic we put the specific value about a series of information in order to the time, money, or the variable that you need.

• There are some types of graphics: the cake graphic the graphic of bars or whatever.

• Other tool is the specific formula.

• The specific formula works when you have a value but you do not know these value divided or subtracted by other, so you can make a lot of combinations to obtain the certain value, there is the sum, average, subtraction, etc…

• So the point is that excel is a good tool to present one ore more documents.