K2's Road to Excellence with Excel Part 8 and VohCom

This is the eighth of sixteen sessions in Excel that will provide you with the information you need to become an Excel expert. This session will focus on formula tips.

Formulas are a necessary component of most Excel workbooks. If you know which functions you should insert into formulas to achieve specific results, you are far more likely to be successful when working with Excel. Further, if you are familiar with some of the fundamentals of formulas such as absolute and relative cell referencing, it will help to minimize the likelihood of errors in your workbooks. In this session, we will explore these and other formula fundamentals.

Learning Objectives

  • A review of Relative, Absolute, and Mixed Address Formulas
  • Utilize Parsing and Accruals and Deferrals with Date Arithmetic
  • Manage the combining of Labels with String Arithmetic
  • Parsing Data with Text to Columns
  • Understand the importance of using Defined Names
  • Using Names in Formulas

Accelerate Your Practice with VohCom Page
Accelerate workflow in your office by eliminating your paper files and streamlining your processes with VohCom Page.  Learn key elements to creating time savings of up to 20%.  Explore the inefficiencies that effect your time management and bottom line.  Time saving steps from the initial intake of records, through to file completion and on to invoicing and collection will be explored.  Learn some of the efficiencies employed by Page power users to accelerate you practice.

Learning Objectives

Upon completing this session, you will understand: 
  • The benefits of digital data management and workflow vs traditional methods
  • Best practices in tracking client and internal communications
  • Benefits of built in real time reports and budget monitoring
  • Advantages of one Page to go to for everything for your client

More Ways to
Earn Verified CPD


Access the many courses from the leader in providing CPD to professionals throughout North America.

K2's Mastering Advanced Excel Functions and Formulas

Catalogue of upcoming webinars

Questions & Answers from the Webinar

Excel Questions
Answers
I think I got lost on the property and equipment formula. How did Alan fit the full sentence on the cell with that formula? I usually do it on wrap text and merge.
I put an apostrophe before the formula to turn it into text. It then flowed across the blank cells beside it. If I wanted the text in a single cell I would wrap the text.
Is it possible to embed a comment inside a formula?
Yes you can. Check out this link.
Is it easier to use MID function?
There are always different ways to get a result in Excel. Yes I could have used the MID function. I chose to use the LEN function with the right function to illustrate calculating the length of text and/or a value.
If I use the name range in one tab, does the program recognize that field or do I need to reference the tab?
Named Range Rules:

1)      You can set a Named Range to have the scope of a single Worksheet or the whole Workbook.  The whole Workbook is the default, so if you enter a Named Range directly into the Name Box, as explained above, it will have a scope of the whole Workbook. This means:

a. If you set the Named Range to have a scope of the whole Workbook you can reference it on any sheet in the Workbook, and you can only use the name once.

b. Or if you set it to have a scope of the Worksheet, you can only reference it on the Worksheet you specify when setting up the Name Range.

One advantage of this is you can have a range with the same Name on each Worksheet.  This would be useful if you had a sheet for each region of your business with quarterly figures on each sheet.  You could then have a Named Range for Qtr1, Qtr2 etc on every sheet.  This would make creating formulas very quick.

To enter a Named Range with the scope of the Worksheet only you need to insert it using the Define Name button on the Formulas tab of the ribbon. When you click on the Define Name button the New Name dialog box will open.

Enter your range name in the Name: field, then click on the drop down list on in the Scope: field and select the Worksheet you want the name available in.

What does Scope mean and how will it affect my Named Ranges? Let’s say on Sheet 1 you select a range A1:A10 and give it a name ‘Jobs’, and you then select Sheet 2 as the Scope.  This means when you’re in Sheet 1 you will NOT see the Named Range ‘Jobs’ in the Name Box or be able to use it in any formulas.

But when you’re in Sheet 2 you will be able to pick it from the Name Box list and use it in formulas.  For example, on Sheet 2 you could enter a formula =SUM(Jobs) and it would actually sum the range A1:A10 on Sheet 1.

2)      Named Ranges cannot have any spaces in the name, so for names containing multiple words use UpperCase letters to distinguish the separate words, or use an underscore e.g. Name_Box.  Of course you don’t have to format your names in this way, but I recommend you do as it makes them easier to read.

3)      Named Ranges are not case sensitive. When you’re entering a formula using a Name, say ‘Sales’, you can enter it as =SUM(sales) and Excel will correct your entry to match your Name e.g. =SUM(Sales) when you hit enter.

4)      Named Ranges must be 253 characters or less in length.  Excel will let you enter a name that is 255 characters long, but it won’t let you choose it from the Name Box.

5)      Named Ranges cannot be single letters ‘C’ or ‘R’.  You can however use other single letters, although this is not recommended because using single letters defeats one of the main purposes of making formulas easy to follow.

6)      Once you delete a Named Range in the Name Manager you cannot undo the action using CTRL+Z or the Undo key and any formulas using the name will return a #NAME? error.

7)      If you delete the cells containing the Named Range any formulas referencing the Name will return a #REF! error, however the Named Range will remain in the Name Manager, where you can Edit it and correct the range of cells.