Excel Peer Learning Community - PowerPoint PPT Presentation

Excel peer learning community
1 / 22

  • Uploaded on
  • Presentation posted in: General

Excel Peer Learning Community. UBI700. peer learning. communities. Overview.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

Excel Peer Learning Community

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Excel peer learning community

Excel Peer Learning Community


peer learning




  • Peer Learning Communities provide opportunities to explore resources and tools while connecting with campus peers and discovering better ways of conducting business. Questions and topics will be solicited from registrants prior to the session and discussion topics will be selected based on registrants’ interest. Prior or continuous attendance is not required.

Today s topic outcome

Today’s Topic - Outcome

Today’s Topic:

  • “Common Excel Functions for Budget Tracking”

    3 Simple Ways:

  • Learn It!

  • Practice It!

  • Review It!



By the end of this session, you will be able to…

  • Understand how Excel Functions help with budget tracking tools

  • How to create a monthly summary running total

  • How to use the unique SUMPRODUCT Functions

    • Student Assistant Budget Workbook Formulas

  • Learn how to find/trace errors within Excel formulas

Learning functions

Learning Functions

  • All Functions can be found in the Function Library group on the Formulas tab.

  • Today’s Focus – Sum-based Functions

    • Sum-based Functions are helpful when tracking:

      • Hourly wages earned against a budget amount

      • Departmental Expenses using data extracted from OBDIEE Revenue & Expense Reports

Why it matters to you

Why It Matters To You

  • Using Functions, creates proficiency, speeds up analysis, eliminates manual entry, and makes us more comfortable with data that isn't 'perfect'.

  • There are times when you want to calculate the budget amount still available for certain expenses

    • Example:

      • Departments may have a need to track the payroll wages earned by student assistants by:

        • The total number of hours worked year-to-date (YTD)

        • Wages paid to all their students for a given month.

      • Discover how much of grant dollars has been spent

Excel function categories

Excel Function Categories

Common functions

Common Functions

Today’s Date: CTRL&+ &; is a shortcut for entering the current date (hold down all 3 keys and release.

Note: Putting extra spaces into formulas can cause an error message!

Learning basic formulas

Learning Basic Formulas

  • Student Monthly Gross wages:

    • Hours worked x pay rate

  • Budget Balance Available (BBA)

    • Add: Initial Budget Amount provided

    • Less: Prior month expenses

    • Less: Current month expenses

    • EQUALS Budget Balance Available

Calculating a running balance

Calculating a Running Balance

  • Basic Worksheet with Debit and Credit columns

  • In this scenario, cell E1 contains our starting balance; Column C contains the debits; and Column D contains the credits.

  • Starting Balance Formula in cell E4: =sum(E1,C4,-D4)

  • Reoccurring Formula in cell E5: =sum(E4,C5,-D5)

Let s practice running balance

Let’s Practice - Running Balance

  • In this scenario, cell E1 contains our starting balance; Column C contains the debits; and Column D contains the credits.

  • Enter expense amounts in the Credit column (Column D)

  • Enter the following formulas:

    • Cell E4: =sum(E1,C4,-D4) Cell E5: =sum(E4,C5,-D5)

  • Copy the formula into the balance cell for each new transaction—use the handle to pull the formula down.

Learning excel functions

Learning Excel Functions

  • Q: How do I add the Amounts in every other column w/o manually putting in the actual cell value?

  • A: A Simple Solution would be:

    • =B4+D4+F4+H4+J4+L4+N4+...etc.

    • We would have to HOLD DOWN the CTRL key, click on each cell, add a “+” sign and repeat to make the formula

    • Very time consuming!

Learning excel functions1

Learning Excel Functions

  • Bigger & Better Function!:

    • Assuming that for row 1 our range of #'s are in A1:O1

  • To add the ODD columns, enter this (no spaces):=SUMPRODUCT((1-MOD(COLUMN($I$1:$O$1),2))*$I$1:$O$1)

  • To add the EVEN columns, enter this (no spaces):=SUMPRODUCT(MOD(COLUMN($I$1:$O$1),2)*$I$1:$O$1)

Practice student assistant workbook

Practice – Student Assistant Workbook

Auditing for formula errors

Auditing for Formula Errors

  • By tracing the relationships, you can test formulas to see which cells are to blame for:

    • Incorrect values in the formula of a particular cell,

    • Non-working formulas in the worksheet

  • Direct precedentsare cells that directly feed the formulas,

  • Dependents are cells that depend on the results of the formulas..

    Source: Excel for Dummies, http://www.dummies.com/how-to/content/understanding-excel-2010s-formula-auditing-tools.html

Auditing for formula errors1

Auditing for Formula Errors

To Trace Formula, we have the following steps.

Step 1

Select the cell that contains the formula result to be traced..

Step 2

Auditing for Formula Errors

Step 2

On the Formula tab, click in the

Formula Auditing group.

Then, click on the Trace


Step 3

Auditing for Formula Errors

Step 3

Then, the Formula Precedents will be displayed.

Step 4

Step 4

To remove the formula precedents,

Click on Remove



Source: http://www.dotnetheaven.com/article/tracing-formula-in-excel-2010

Excel peer learning community

Precedent and dependent lines are always inserted from or to the active cell. From the active cell:

  • To see what other cells are referenced in the active cell's formula, click the Trace Precedents button.

  • To see which other cells contain a reference to the active cell, click the Trace Dependents button.

Let s review

Let’s Review

  • Common Budget Tracking Functions

  • Name some Excel Functions to help with budget tracking tools?

  • What is the formula to create a running total?

  • Why do we use the SUMPRODUCT Functions?

  • In tracing errors, why is Formula auditing used? What is a dependent? What is a precedent?

Excel peer learning community

  • Allyson Bates

    Business Analyst

    Financial Services



  • Suchi Patel

    Business Analyst

    Admin & Finance IT



  • Greg Sweet

    Univ. Tax Compliance Manager

    Financial Services

    Adjunct Faculty, Dept. of Accounting



Thank You

Happy Holidays!

  • Login