2015 Certificate IV Applications 1: Session 13

Excel has a number of database features built into it that allow you to deal with large amounts of information and create queries, sort information and use input forms. The main difference between using Excel and Access is that Excel loads everything into RAM and is limited by the amount of RAM that is installed as well as being limited by the number of Rows and Columns available - not that that is a limitation - you'll run out of RAM before you run out of Spreadsheet.

Access, on the other hand, stores and uses information directly from your hard disk. It also saves your data as you go, whereas you need to save an Excel Spreadsheet regularly or risk the chance of losing information.
Excel has 1048576 rows and heaps of columns that are available for your spreadsheeting pleasure.

You can also use Excel to access data from other sources. Database functions are accessed from the Data TAB.

Sorting Data

Sorting data is probably the most basic database activity as well as the most common. Excel allows you to Sort your data by individual columns or more than one column. To sort a single column, have your cursor in that column and use either ascending (A-Z) or descending (Z-A).

Download and Open the sample Spreadsheet 2016ExcelDatabaseNotes.xlsx from Moodle, then you can follow along with the examples.

To sort on an individual column, move your cursor into the column and click on the required sort button A-Z or Z-A

To sort using multiple criteria, use the Sort button to bring up the dialog box.

The above example will sort by Suburb and where there is more than one of the same suburb, the items will be listed in Business name order.

Filtering Data

To filter data based on certain criteria, use the Filter button. A dialogue box will appear that will allow you to enter the criteria.

To use the filter button, start by selecting the data, including the headers.

Click on the Filter Button from the Data Ribbon.

You can also access the filter options by formatting your data as a table.

There are many pre-styled table options that you can select from, with many including shading for better viewing. I chose a pretty purple one.

The drop down arrows will perform the same task as if you were to choose filter from the main menu.

Using the drop down arrows, you can select what values within the column you want to filter.

Click on Clear or deselect the filter button to display all items again. If you wanted to show all those located in Cranbourne that Supply Flowers, in the filtered view, filter firstly on suburb to get just the Cranbourne businesses, then filter it again on Supplier type.

Using the Filter button, you can do a range of basic AND and OR queries. It is important to know the difference between an AND and an OR query. An OR query will usually give you more results than an AND query. With an AND query, all of the conditions that you define must be true. In an OR query, as long as one of the conditions are true, that item will be displayed in your results.

If you want to do more complicated queries, you can use the Custom AutoFilter to add more criteria. You can do this from the filter dialog box - select Text Filters then Custom Filters.

Using the Custom Autofilter, if we want to see all the businesses that supply Natives, then we would use the following criteria:

If you wanted all business names that start with the letter F then you would use:

If you wanted all the business names that contain the word flower:

Advanced Filters

Advanced Filters allow you to place criteria on a number of different columns. You do advanced Filters using the Dialog box and by placing the criteria “in-line”.

Start by copying the heading line somewhere beneath your data. This is where your criteria will be placed.

Placing the criteria on the same line uses an “AND” filter and if you place them on different lines it is an “OR” criteria. Use the Advanced Filter dialog to tell Excel where your data is and the selection criteria

So if we want all suppliers who are in either Cranbourne or Frankston, we would use the following:

Use this technique to display business that match the following criteria
•    All Suppliers in Pakenham
•    All suppliers in Cranbourne who supply Natives or Supplies
•    All suppliers who have the word "flower" in the business name (HINT: use the * wildcard)

Format as Table

Load the file 2015DatabaseExercise.xlsx from Moodle.

Complete the spreadsheet using formulas to calculate the Extended Cost, Sales Tax, and Total. Complete the formula for % of Total as well as the totals at the bottom of the Spreadsheet.

Increase column widths as necessary and format the relevant fields as dollars and percentages.

Use Format As Table to format it so that it looks similar to the following:

Using the drop down simple query, perform the following:
•    All the ads placed with Advertising Concepts
•    All the Newspaper ads
•    All the Invoices due in March
•    All the Invoices due in March for Discovery Channel
•    Sort the table by ad type

Use a number filter to perform the following:
•    All the ads with a total cost of between $2000 and $4000
•    All the ads that use more that 10% of the total cost
•    All the ads where the total cost is above average

Use Advanced Filters for the following:
•    All Invoices due after 1/4/2010
•    All invoices due in April 2010
•    All invoices due in April for iPodAds
•    All ads placed with Food Network
•    All ads that total between $4000 and $5000