This week we will look at how you can include data from one Office Application in another. The most common link is to embed Excel information into a Word document.
I will start by using our basic chart excel spreadsheet from a few weeks back. It looks like this.
Create a simple Chart - I have used a bar chart. My Chart and Spreadsheet look like this
Save the file.
The next stage is to create a document. For the sake of this exercise, mine will be pretty basic.
Save the Document.
Go back to the spreadsheet and Select and Copy (Ctrl-C) the Chart.
To place this chart in your Document, you want to do a Paste Link. You could do a straight Paste, but it means that your document will not be updated if data in the spreadsheet changes.
What we want to do is to link the data in our document with the source data, which is our spreadsheet. Each time you load the document, it will check to see if there is an update.
Save and close the document.
Go back to the spreadsheet and make a change to the data. I increased the National Proposed number to 40. The chart is updated as soon as the change is made.
Save and close the spreadsheet.
Open up the document! If you have created the link correctly, you should get a dialog box similar to the one below
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
Printing Tips
Some printing things that are relevant specifically to Excel!!
Using the Spreadsheet from last week, create a 3D Bar Chart.
Try removing the Blank lines
• Click on the Select Data button. This will show you the data ranges that are included in your chart.
• Remove the two blank areas by clicking on each and clicking remove, then click OK.
• If your chart does not look like you expected, try using the Switch Row/Column button
• Add a title by clicking on Layout Ribbon, Chart Title and Above Chart.
• Type the heading into the text box that appears.
FUNctions
So far we have really only looked at one function - the AutoSum function. Excel actually has lots of inbuilt functions that can be used to perform heaps of different tasks. The advantage of using functions is that you don't need to worry about how the calculation is performed, all you need to do is to tell Excel what values it needs to use for the functions.
Simple Functions
Use file 2015BasicFunctions.xlsx
The simple functions can be easily accessed from the Autosum button on the Home Ribbon.
Use the drop down options to choose what type of calculation you want.
Start by adding in the formulas to complete the spreadsheet.
Start by using Autosum to total the items for 2015.
Once you complete 2015, you can copy the formula across to the other years.
Use the other functions from the drop down in Autosum to complete the Average, Minimum (Min), Maximum (Max) and Count formulas from the simple formula button, the range should include all 5 years.
Once you complete the formula for Swimwear, you copy the formula down for the other items.
This is what mine looks like
Formulas Ribbon
The Formulas TAB can be used to access all of Excel’s huge range of in built Functions.
Insert Function will open the Function Search facility.
The icons within the Function Library section incorporate all of Excel’s Functions broken down into categories.
These notes will not cover all of the possible functions, but will look at the ones most commonly used. By all means, experiment with any of the others once you are comfortable with the process.
Financial Functions
There are many inbuilt Financial Functions. They include things like Present and Future values, Interest Rates, Internal rate of Return and Depreciation as well as Functions relating to Securities.
The amounts for formulas can be entered straight into the Function Arguments box or entered as Cell References. Using Cell References allows you to do simple changes to the values and see the effect immediately.
The first function we will look at is Depreciation – Fixed Declining Balance. Depreciation is used by business to write off an asset over a period of time, usually similar to the expected life of the asset.
Use File 2015Financial Functions.xlsx
Start by clicking in the cell where we want the first year’s depreciation to go in. This is B10. Then select the function from the list – the one we want is DB.
Complete the depreciation for all 4 years. As I have mentioned, you dn;t need to know how it calculates the answer - give the formula the information it requires and assume that the answer is correct.
Our second financial function is going to be PMT – this will calculate the repayments for a loan.
Start with your cursor in cell B20 and select the function.
The Rate is the rate for the loan, but it must match the frequency of repayments. If you are looking Monthly repayments, the annual interest rate needs to be divided by 12.
The advantage of using the cell references is that you can play with the figures. If you find that you can’t afford the repayments, you can try with a different loan amount or even different interest rate.
Activity 3
Set up a spreadsheet that will compare a couple of loans so that you can make a decision on how much you can afford to borrow.
The interest rate will be the same as you have negotiated with your credit union for a great rate on a car loan.
The annual rate will be 9%. You will be making fortnightly repayments to correspond with your wages payments.
You want to compare how much the payments will be for a loan of $6500, $8000, $10000 and $15000.
Logical and Lookup Functions
The most commonly used Logical Function is the IF Function. This simple spreadsheet is completed using an IF statement and a LOOKUP function.
Use file 2015IfLookupBasic.xlsx
The first part of this exercise is to get the Hourly Rates into the spreadsheet from the Pay Rates Table using the VLOOKUP function.
The function will use the value that is in column B and find it in the lookup table. You can then use the values in any adjacent columns. The lookup column itself is column 1, so values can be in column 2 or higher if your lookup table contains multiple columns.
N.B. The table that you are looking up into, MUST have the lookup column sorted lowest to highest i.e. A - Z; 0 - 99
Start in cell D7
The formula can then be copied down to complete the Pay Rate column.
A simple calculation can be used to get the total Pay.
We would like to award all of our employees who have worked more than 20 hours this week.
Using an IF function, we can highlight this information very easily.
The first part of this function requires a logical test that can equate to being either true or false. This would usually be done using comparison operators greater than (>), Less than (<) or equal to (=)
= (equal sign) A1=B1
> (greater than sign) A1>B1
< (less than sign) A1<B1
>= (greater than or equal to sign) A1>=B1
<= (less than or equal to sign) A1<=B1
<> (not equal to sign) A1<>B1
The rest of the IF function tells Excel what to do if the comparison is TRUE and then what to do if it is FALSE.
Text Functions
As well as functions that deal with numbers, there are a range of functions that deal with text within cells. These Functions include Concatenate, Trim, Upper and Proper. The following spreadsheet shows an example of a couple of the Text Functions.
Use file 2015TextFunctions.xlsx
The completed spreadsheet would look like the following:
Date and Time Functions
Excel also has functions that deal with Dates and Times. Excel knows about dates and as well as the inbuilt functions, you can use dates in calculations. There are functions that will give you the day of the week and today’s current date and or time.
=Today()
=Now()
Today() and Now() will both give you the current date. The difference between the two is that Now() will also include the time. Excel will assume that numbers entered in a certain way are dates and will display them as such, even though Excel really treats the date like a number. The date is actually a number based on the number of days since 1/1/1900. This date actually has a date number of 1. Because Excel stores dates as numbers, you can do what I call "date maths". It is also how Excel compares dates so you can subtract one date from another and work out how many days are between the two dates.
Naming Ranges
As well as using the official range name such as A1:D20, you can give a range of cells a more meaningful name, which can then be used in formulas and functions. In the Formulas TAB there is a section that relates to named areas.
You can use these buttons to manage named areas within your spreadsheet - naming is not compulsory, but can make it easier to modify a formula later as the names have more meaning than the default Excel range.
You can create a range name quickly by selecting the range on the spreadsheet and typing in the name in the Name Box.
Now is the time to practice our skills that we have just learnt.
Activity 4 - Simple Exercise
Complete the following spreadsheet given that Full Fee students will pay $4.75 per hour and Health Care Card (HCC) students will pay $1.20
The following information relates to this spreadsheet:
• Assume that the date returned is 1/04/2015
• Overdue – was the item overdue? If overdue, this should say "Yes", otherwise "No".
• Days Overdue – how many days is the item overdue by?
• Fine Amount – DVDs are fined at $1.00 per day. Books are fined at $0.25 per day.
