Excel enables users to give meaningful names to specific ranges in their worksheets. As the number of different named ranges on a worksheet grows, you will need tools for identifying the areas referenced by your named ranges.
Category: Excel
Dynamic Named Ranges
Dynamic range is a way to create a named range that can automatically expand and contract to the size of your data. It is useful when you want to use the range in formulas, charts, or PivotTables without having to update it manually every time you add or delete data.
Define Names to Store a Value or Formula
Although referencing data by name is convenient, it’s sometimes more helpful to store a constant value or even a formula, especially if you’ve been creating custom functions in VBA.
Usually, when you name a range, the name is at the workbook level, meaning that one name refers to a specified range on a specified worksheet wherever it is used in the workbook. Once the name has been used, it cannot be used again to represent a range on another worksheet. There is a way around this, however.
Address Data by Name
Although cell numbers are at the foundation of everything Excel does, but sometimes you may want to use more descriptive names for your cells (or ranges), such as "Item Number" and "Quantity", instead of "A1:A100". This can make your formulas and references easier to understand and maintain. Excel allows you to create and use names for your cells, ranges, tables, and other objects. You’ll learn how to create and manage Names in Excel in this article.
Format Date and Time Values
Date and time formatting in Excel allows you to display the values of cells, reports, or charts that contain date or time data in different ways. You can use the built-in formats or create your own custom formats to suit your needs.
Show Total Time as Days, Hours, and Minutes
When you add hours in Excel, you can have the result return as total hours and minutes, but unfortunately, not as days, hours, and minutes. Displaying that will take some extra work.
One of the common tasks in Excel is to perform date and time calculations. For example, you may need to find out when a project will be completed, how long a task will take, or when a payment is due. To help you with these calculations, Excel provides two functions: WORKDAY and WORKDAY.INTL. These functions allow you to add or subtract a number of working days from a given start date, taking into account weekends and holidays. This way, you can easily determine realistic deadlines, schedules, or due dates for your work.
Calculate Working Days and Holidays
If you want to calculate the number of working days between two dates in Excel, you can use the NETWORKDAYS or NETWORKDAYS.INTL functions. Both functions can exclude a list of holidays from the count, but the NETWORKDAYS.INTL function also allows you to specify which days of the week are considered weekends.
In Excel, you can determine the number of specified days (e.g., Mondays, Tuesdays, etc.) in any given month or (between two dates) using some methods described in this tutorial.