Skip to content

WP Template

Description

You Will Learn

Outline-Excel Features

Prerequisites

See WP_NH samples

Maybe only three columns

This Course will teach how to construct a loan amortization schedule.

The user will be presented with the financial concepts of loans, interest and the amortization…..

 

Relative and Absolute Cell References

Defined Names

=PMT Function

Blah….

 

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

EXCEL SOLUTIONS

The links below take you to other Solution pages:

Free Downloads

Paid Downloads

Free Articles

Paid Articles

These pages are Solutions based. They will solve a problem using one or more Excel features to accomplish such.

Print Ranges

Dynamic Arrays

DEFINED NAMES-NAMING RULES

Defined Names-Recap

Defined Names is an Excel feature where a name is assigned to a cell or range cells. How to name cells is at the Defined Names page

Naming rules:

  • Names must begin with a non-number; a letter of the alphabet, the underscore ( _ ) character or the backslash ( \ ) character
  • The names may contain numbers, but must begin with text
  • Periods ( . ) are allowed
  • Upper and lower case letters are interpreted the same in Excel.
    SalesReport, salesreport, and SALESREPORT are all the same name. By creating a name using caps, such as SaleReport, you may find it a bit easier to work with.
  • Spaces and most punctuation character are not allowed.
  • The underscore character helps to display a name with the visual spacing provided by the underscore character. Examles:
    Sales_Report, UnitsSold_Jan, March_Payroll, _Commissions
  • A name can contain up to 255 characters.
  • Many times Excel will prompt you when trying to use a non-allowable name
  • The name cannot look like a cell reference
    For example, you desire to name your payroll summary W2 (IRS form W2).
    Excel will think this is cell W2.
  • C, c, R, r cannot be used as names. Excel reserves for selection shortcuts

 
 

Articles

Excel

Other

Dynamic Arrays

DEFINED NAMES

Introduction

Defined Names is an Excel feature where a name is assigned to a cell or range cells.  Think of it this way; you assign the name Sales_Report to the range B5:F42. So rather than thinking B5:F42 you simply think about the Sales_Report. The power of defined names is quite substantial as you will see in these pages.

Defined Names is the Excel official name of this feature. Named Ranges and Range Names are also sometimes commonly used. We will use Defined Names throughout this website.

Naming the Range

There are three basic ways to name a range. They follow with commentary on each one. More advanced ways to name a range are beyond the scope of this page.

See the Defined Names Rules page for naming rules, allowable names, etc.

See the Using Defined Name page for common uses of defined names and how they save significant amounts of time and help with reducing spreadsheet errors.

The Ribbon Menu

  • Select the range to be named
  • On the Formulas tab, in the Defined Names group, click Define Name.  (Figure 1)
  • In the New Name dialog box, in the Name box, type the name you want to use. Depending on the text in the selection, Excel may try to guess at the name and will per-populate the name. (Figure 2)
  • Accept the proposed name or type in the desired name
  • Press Enter or click OK
  • Alternatively jump straight into the menu (without selecting a range) and select range in the Refers to: box.
  • Defined Names Rules
 

Figure 1

Figure 2

Alternatively, use the Name Manager. See Figure 1.

Click New which takes you to the New Name dialog box.

The Name Manager is typically used for edit an existing name or the underlying range or other unique circumstances.

The Name Box

  • Select the range to be named
  • Click in the Name Box (Figure 3)
  • Type the new defined name and press Enter
  • Defined Names Rules

This method is may be considered the easiest, but has its limitations. Once you get familiar with naming ranges you can decide which method works best for your specific circumstances.

This only works with defining new names. Editing a name or the range for the name, is done using the Name Manager.

Figure 3

Using Existing Text in the Range

Let’s say you have a column data with the heading Sales with the data below the heading.

  • Select the entire range, from the heading to the last cell with data.
  • Press Ctrl+Shift+F3
  • Ensure Top rOW is selected, correct if necessary
  • Press Enter
  • The data (without the heading) is now named Sales
  • To verify the newly define range, click the drop down arrow in the name box
  • Click the desired name
  • The range will be selected upon click the name
  • Defined Names Rules
 
 

Naming Rules

The naming rules are quite simple and intuitive, but there are some traps. Many times Excel will prompt you when using illegal name.

  • Names can refer to single cell or range of cells.
  • Names must begin with a non-number; a letter of the alphabet, the underscore ( _ ) character or the backslash ( \ ) character
  • The names may contain numbers, but must begin with text
  • Periods ( . ) are allowed
  • Upper and lower case letters are interpreted the same in Excel.
    SalesReport, salesreport, and SALESREPORT are all the same name. By creating a name using caps, such as SaleReport, you may find it a bit easier to work with.
  • Spaces and most punctuation character are not allowed.
  • The underscore character helps to display a name with the visual spacing provided by the underscore character.
    Examples: Sales_Report, UnitsSold_Jan, March_Payroll, _Commissions
  • A name can contain up to 255 characters.
  • Many times Excel will prompt you when trying to use a non-allowable name
  • The name cannot look like a cell reference
    For example, you desire to name your payroll summary W2 (IRS form W2).
    Excel will think this is cell W2.
  • C, c, R, r cannot be used as names. Excel reserves for selection shortcuts
  • Names apply to the entire workbook unless sheet specific names are created when assigning the name. The resulting name will then include the sheet name.
 

 

Using Defined Names

Defined names are a great tool for saving time, helping to reduce spreadsheet errors, make it easier to follow and much much more.  Here are the more common uses:

Jumping (GoTo) a range

Let’s say you have five sales divisions in a very large spreadsheet:

  • Simply press GoTo (F5 or Ctrl+G), click the name and click OK, or
  • Click the down arrow on the Name Box and click the name
 

Printing

Excel’s printing features are quite robust when there is good spreadsheet layout and creation of print areas. However, more complex spreadsheets may need additional features.  Simply name the ranges that will be printed as a part of your spreadsheet layout. When printing your steps may be something like:

  • GoTo the print range
  • Press Ctrl+P (File Print in the menu)
  • Click Settings…. Print Selection
  • Print
 

Of course there are many variations of these steps, pick the one that works best with your style.

Wait a minute, you may say. We’re in a paperless world and very seldom print. Here’s three of many scenarios where this still may apply.

  • The project requirements require printed documents, such as court submissions and the several meetings leading up the final stage. Board of Director and similar meeting still tend to use printed reports.
  • While paper may not be the final product, printing to a PDF file many times is.
  • For the larger and complex projects, including in these steps in macro driven custom menus can be a huge time saver.
 

Formulas

The more complex the formulas or the spreadsheet the more value names add.

  • Example: Let’s say you have a loan amortization schedule, referring to the cell named Interest is much easier to interpret that cell C5
  • Referring to Sales_2022 (in the formula) helps to ensure you are not referring to Sales_2021 in error.  This also helps when reviewing or modifying a spreadsheet.
  • =SUM(Sales) is much easier to read than =SUM(C5:C40)
  • Names are Absolute in formulas eliminating the need for entering the $ signs, such as $B$5
  • Instead of typing the name in a formula, use the F3 key. Type =, then press F3 and double click on the desired name and continue with the formula
 
 

War Stories

Financial Modeling

Client developed a very sophisticated financial modeling spreadsheet, over 35 worksheets, each with complex formulas. Defined names were not used. The tool was enhanced and updated continuously over a period of years.

While working with the client I noted how much time he spent navigating the worksheets. This was not just a few seconds here or there, it could have been 10x fold that amount of time.  Why does this matter. He was always working in a frustrated mode.

He was showing me the flow of a NPV page combined with IRR and I happened to see a reference to the wrong worksheet caused by a major worksheet revision. With so much going on, and the expectation of similar results on each page, it was very hard to see the error(s).

Client had been suspicious of some of the results from the calculations but never had the time to dig into it….tomorrows projects.

While the projects all would have been profitable, over $224,000 was left on the table.

 

Loan Payment and Amortization Schedules

The spreadsheet and calculations were very well designed both technically and easy to follow. Reference to the Interest Rate was an absolute cell reference such as $C$5 which is required for this formula. No problem so far.

There were several scenarios with different interest rates, loan period, balloon payments, loan amounts and so on. The entire range of cells was simply copied to a new range where new facts were used. Since the Interest Rate in cell $C$5 was absolute, each new scenario referred back to the original scenario.  Since defined names are also absolute, by themselves, would not have prevented the error. But a well thought through name would have, such as (simplified) Interest_Rate_01, Interest_Rate_02, etc. would have. A simple glance at the formula would jump out rather the $C$5.  There’s more to this example but I tried to keep it simple.

This resulted in some of the scenarios being a no-go as they looked like loosing projects when, if fact, they were profitable.

Commission Tables

Complex VLookup formulas were used in a complex sales and commission structure.  The formulas were copied for new scenarios.  With hindsight defined names would have helped to eliminate the errors. The formulas were riddled with errors, referring to the wrong division, the wrong incentive program, the wrong commission table and so on.

This resulted in lack of trust within the teams and stalled sales programs. I got involve early which brought the program back to being very successful.

More

Spreadsheet errors are very common and it’s many times hard to weed them out. The uses of names noted in this page, such as time savings and ease of creating and reviewing formulas are just two ways two minimize errors. I’m sure you can think of many more.

 

 

 

 

Author: Jim Colville

Date: 9/8/1998 – Updated: 8/21/2023

 

 

 

Articles

Excel

Other

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

EXCEL - MORE

Defined Names

 

NEW DATA TYPES

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

CONCAT

The CONCAT function joins text located in different cells. The technical term is to Concatenate. This page will show three different ways to Concatenate text from a range of cells and introduces some other related functions and features.

 

Using the CONCAT function

Let’s say you have a customer list with the First Name in cell A2 and the Last Name in cell B2. In cell D2 enter the function:

=CONCAT(A2,B2)   The result will look something like:  BillRogers

Note that there is no space separating the first and last name. To add the space character enter the function:

=CONCAT(A2,” “,B2)   Now the result will look something like:  Bill Rogers
 
You can now copy the formula down the entire row in in the list

 

Using the & Character

Again , let’s say you have a customer list with the First Name in cell A2 and the Last Name in cell B2. In cell D2 enter the formula:

=A2&B2   As with the Concat function the result will look something like:  BillRogers

Note that there is no space separating the first and last name. To add the space character enter the function:

=A2&” “&B2   Now the result will look something like:  Bill Rogers
 

You can now copy the formula down the entire row in in the list

Both the CONCAT function and “adding” or “joining” cells with the  &  (ampersand) character accomplish the same thing. Using the  & character is fairly standard in the computer world, in programming and other related uses where the CONCAT function is an Excel function with intent of simplifying the process.

 

Using the CONCATENATION Function

The CONCATENATION function is the earlier version and is replaced with the CONCAT function. The CONCATENATION function limited to 30 individual references.

What is the Better Choice?

As you can see, the three approaches are about the same so it’s a matter of personal preference. Since CONCATENATION is an older function and has limitations it’s not usually the users choice.  The  &  seems to be used more than the CONCAT function as it’s more like entering regular formulas.

This page is showing the very basic usage in joining (concatenation) cells. Two additional methods are:

The Flash Fill feature  and

The TEXTJOIN  function which is a newer function that is much more robust than the older tools on this page.

Concatenating with Numbers

Concatenation works well with text but has issues with numbers. Once way to think about it is these features “add” text together.  There are times when joining both text and numbers is needed. Using the TEXT function solves this problem.

Example:

Let’s say you have a sales report with many rows of data showing Units Sold and the Dollar Amount sold with totals for both columns and you desire to have a narrative statement at the bottom of the page that would look like:

Total units sold were 842 with total sales of $71,114.  The formula would look something like:

=”Total units sold were “&D112&” with total sales of “&TEXT(F112,”$0,0”)

The TEXT function allows for the display of $71,114 rather than 71114. This more important if a date is to be displayed. This portion of the joined cells would be:
TEXT(A2,”mmmm d”)) to have it display something like April 23 rather than 45039.

Summary

This page is designed to show how to join text from cells using three different, yet very similar features.  Joining/concatenating text is a very powerful set of tools.  The next step up from these example can be found with using the Flash Fill feature and the newer TEXTJOIN Function. The Concat.xlsx file further explains the features along with exercises to help in the full understanding of the tools.

Concat.xlsx

Flash Fill.xlsx

TextJoin.xlsx

 

Excel Version History

#     Name    Released    Comments This is a placeholder page that will be updated with more relevant and readable content soon. 1    Version 1    1985    The first version of Excel was released exclusively for Mac. This remains unknown to many… Read More »Excel Version History

History of Spreadsheets

Excel is today’s leader, preceded by Lotus 1-2-3, Visicalc, Muliplan, Quattro Pro and others. Here’s a rough history of the electronic spreadsheet. This is a placeholder history. It will be updated with a more relevant and readable history soon. VisiCalc… Read More »History of Spreadsheets

SUBTOTAL FUNCTION

Summary

Subtotals will provide a total each time the data in the desired column changes. For example your sales report contains, say Dept 1 Dept 2, …..Dept 9, it will add a subtotal when the column with Jan changes to Feb.  This page is for the Subtotal feature that is found in the ribbon. Subtotals in a Pivot Table is covered in the Pivot Table pages.

In Using the Function

  • The column that creates the subtotal must be sorted as Excel reads down that column and when it changes, it creates the subtotal function.
  • It will create subtotals and a grandtotal.
  • In the sales report, noted above the report must be sorted on the Dept column.
  • When Excel sees that column data changing it adds the SUBTOTAL function in the desired column(s) with the data.
  • It ignores cells existing subtotal functions avoiding double counting.
  • It ignores cell that have been filtered and are not visible.
  • It’s not limited to SUM, other calculations may be AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, VARP
  • While it’s summing the desired data, it enters the SUBTOTAL function

Rules

  • The subtotals are entered when the data in the column changes. Example, Excel scans down the Sport column, when it sees Baseball, it then inserts a row above Baseball and enters the =SUBTOTAL function
  • This means that you must organize the data with the categories to be subtotaled as such. In this case Golf, Baseball, etc.  Sorting is one method of accomplishing this. You may need to use the Custom Sort feature.
  • If you find that the Subtotal function is too restrictive, then use a different function or set of commands, such as SUMIF or Pivot Tables
  • The Subtotal command will appear grayed out if you are working with a Microsoft Excel table. To add subtotals in a table, you must first convert the table to a normal range of data, and then add the subtotal. Note that this will remove all table functionality from the data except table formatting.

Steps

  • Organize the data.
  • Place the cursor in the data
  • Click on Data (in the menu), then in the Outline group:
    • Click on Subtotal
  • Make the appropriate choices in the pop up menu such as
    • Use the SUM function
  • Add Subtotals to the:
    • Units Sold column
    • Monthly Sales column

Additional Notes

  • Grand Total are derived from the detail data, not from the subtotal values
  • The result is in the Excel Outline format so that you can display or hide rows for each subtotal
  • Formatting-The text in the first column is Bolded by Excel, but the subtotaled numbers are not.  You can add Bold or other desired formatting by applying Conditional Formatting or with a Macro
 

Data File

The data file contains several exercises and sample data along with explanations. Review each sheet tab in the file.

Subtotals.xlsx