Five Tricks to Quickly Analyse Data with Microsoft Excel Web App

Class/subject – Personal Finance / Maths or Business

Project/assignment: Spreadsheet analysis of typical consumer loan types and terms

Meet Kevin, an undergrad at a large university in Oregon. Kevin lives on campus and uses his laptop everywhere from the library to his dorm, to the hallway outside the classroom. And he never goes anywhere without his smart phone, which he checks before, during, and after class. Kevin’s university provides Live@EDU for all its students and staff. With these great tools, he can do his homework, stay on top of his social life, and have non-stop access to all of his files. And the fact that it’s free? Bonus. 

This semester, Kevin’s course load will help illustrate each of the tools within Live@EDU, their features, and the many ways the Microsoft Office Web Apps make life easier for both students and faculty.

Spreadsheets and data analysis are often stigmatized as being boring, but a few simple tricks could show you how to compute information that can truly revolutionize your life—or at least your wallet. Excel puts exceptional abilities at your fingertips. As the industry-leading tool for data analysis, Excel can help you do everything from planning a budget to running sophisticated formulas.

Kevin’s class: Finance

Assignment: Payment analysis on common loans to determine amount paid in interest over the life of the loan.

Understanding the types, terms, and repayment information on common consumer loans are not only a cornerstone component of the finance curriculum, but a powerful life skill. The Excel Web App will be the tool used to run the calculations on the project. And by learning a few simple tricks, the students will become informed financial decision-makers.

Each student will be given a profile of a consumer and issued a set of terms for a home loan, a car loan, and student loan. Every profile is different, and after the students have run their calculations, they will collaborate together to view and analyse all the scenarios across the class.

To do this, some core functionalities of Excel will be used:

For the finance class loan comparison project, the class will be both calculating and comparing data. The multiple tabs at the bottom of the sheet organize your work into Worksheets. Kevin and his classmates use one sheet to calculate payment information, another to compare the data from the other profiles, and a third Worksheet to create a chart of the information they have calculated.


How to Format via different worksheets

Spreadsheets open in a Workbook, and there are individual sheets known as Worksheets. Using multiple pages or worksheets allows you to manage lots of information, yet break out data by different categories.

Screenshot of Excel Web App

The finance professor walks Kevin and his class through the use of the PMT function on her shared Excel Class Workbook. Sharing documents allows you to grant permission to others to either access or even edit each individual file. The professor uses it in a class setting so that all students have immediate access to the up-to-date file.

By displaying the information in class and demonstrating how it works, the students can open and review her sample from the shared file when preparing their homework. The students will be able to calculate the information necessary by understanding the syntax or the way to write functions. Kevin and his class will calculate the payment on a home loan, a car loan, and a school loan.


How to Write Formulas in Excel - PMT

To calculate the payment you need to know:

-the annual percentage rate (interest_rate)

-the number of monthly payments over the term of the loan (number_payments)

-the present value, or the total amount being loaned to you (PV)

The syntax of the function, or how you would write the function, is:

=PMT(interest_rate, number_payments, PV),

So, for a 30-year, $200,000 at 4.5%, the equation would look like: =PMT(.045/12,360,-200000)

Screenshot of Excel Web App

Once the students have generated a payment for each of the home, car, and student loans, they are able to calculate how much they will be paying across the life of the loan by multiplying the payment by the number of payments. Once they subtract the principal, they are left with the amount of interest paid across the life of the loan. This calculation needs to be run on all the loans, and the best way to quickly do that is using Auto-fill. Auto-fill is a feature in Excel that lets you drag the formula over to other cells, automatically populating the content.


How to use Auto-fill

Because Excel is often used for sorting and analysing data, it’s common that the same formula will be used to assess different sets of data. If these equations fall within the same row you can use the cell-copy function and transfer it to rows to the right or left.

For the formula you would like to copy, hover your cursor over the lower-right corner. A cross or plus-sign will appear, and by clicking and dragging to either side, the formula is copied. It’s that easy.

Screenshot of Excel Web App

By defining the amount of interest paid across each of the home loans, the car loan, and the student loans, it’s time to calculate the grand total. They can do this by using the SUM formula, a helpful shortcut any Excel user will implement time and again.


How to use the sum formula

Formulas run basic calculations on values in your worksheet. You can create a formula using constant numbers, or by using functions, which are short terms that run a specific type of formula.

Formulas all begin with the equals sign (=) before any other information. A common function is the sum function, which adds all the numbers in a given set of data. Imagine you had a list of cells with numbers in it. To add all the numbers, you would type the function

=SUM(B2:B9), and hit enter. The colon sign (:) tells the equation to add all the numbers within the two you specified. Excel is built with a formula nomenclature, so it will tell you if your function is notated incorrectly.

Screenshot of Excel Web App

Kevin’s class must determine the best option for each of the profile from all the scenarios in the class. By sharing each of their files with their fellow students, they are able to immediately access the other profile breakdowns. Filtering the results by profile name and total amount paid in interest will instantly show the most ideal loan term.


How to Sort and Filter

The filter function allows you to select a set of data in a column, and sort or filter it in a handful of helpful ways: sort ascending or descending, or run specific filters whether your data are numbers or words/text.

Sorting and filtering are slightly different; when you sort, all of your data remains visible, however, if you filter, you will only view the information you have chosen to filter. To view all of your data, remove the filter.

You can also select two columns to sort together, for example, if you need to know the name of the profile with the lowest paid in interest.

Highlight the columns you would like to filter and select ‘Sort & Filter as Table’. You will be prompted to whether or not you included headers; check or uncheck the box as necessary. The content will be divided into columns. Click on the arrow above the column you wish to sort, and select the filtering option you choose. The corresponding column will be filtered along with it.

Screenshot of Excel Web App

Excel also provides excellent options for graphs and charts. With the results of total amount paid in interest across each profile, Kevin and his class will use the data to create a graph. A bar graph is the correct option for the nature of the data they are creating, and by selecting the correct sets of data, Excel creates a bar graph automatically.

How to create a graph or diagram

The Excel Web App will help you automatically create charts and graphs with the data in your spreadsheet.

Select the populated cells in the columns you would like to chart, and with them selected, click on the type of chart you would like to have created. Note that certain data sets lend themselves to certain types of graphs and charts, but by clicking through them you can find the type that displays your information best.

Screenshot of Excel Web App

By working through this example, the students have learned how Excel can quickly sort, analyze, and graph data, as well as serve as a pivotal tool in making financial decisions.

All of this is possible with the Microsoft Excel Web App, but more powerful capabilities are available in the software version. The software version of Excel allows you to:

  • Create PivotTables
  • Publish complex data models to SharePoint
  • Robust data manipulation possibilities
  • Customizable Ribbons

To learn more, find helpful Excel resources here.

Want to share this with your students? Click here to find this information in an email template you can send to others, or share the link directly.

Comments (0)

Skip to main content