Tag: Excel Ranges

Change Fonts Schemes in Microsoft Excel

Font Schemes in Microsoft Excel

You can add visual appeal to your workbook by selecting a different font scheme. Each font scheme has two defined fonts: a heading font for the titles and headings, and a body font for the regular worksheet text.

Excel offers more than 20 font schemes. To get the most out of Excel’s font schemes, particularly the heading fonts, you must apply styles to your ranges.

Operations with Ranges in Microsoft Excel

Operations with Ranges in Microsoft Excel

 






In Excel, a range is a collection of two or more cells that you work with as a group rather than separately. This enables you to fill the range with values, move or copy the range, sort the range data, filter the range to show only certain values, insert and delete ranges, hide entire rows or columns, and merge two or more cells.

You learn these and other range techniques in this chapter, and in later chapters you learn techniques such as formatting a range, applying a formula to a range, and building a chart from a range.

Select a Range: To work with a range in Excel, you must select the cells that you want to include in the range. After you select the range, you can fill it with data, move or copy it to another part of the worksheet, format the cells, delete the data, and so on. You can select a range as a rectangular group of cells, as a collection of individual cells, or as an entire row or column.

Fill a Range with the Same Data: If you need to fill a range with the same data, you can save time by getting Excel to fill the range for you. The most common method for filling a range in this way is to use Excel’s AutoFill feature, which makes it easy to fill a vertical or horizontal range with the same value. However, Excel also offers an alternative method that enables you to fill any selected range. See “Select a Range,” earlier in this chapter, to learn how to select a range of cells.

Fill a Range with a Series of Values: If you need to fill a range with a series of values, you can save time by using Excel’s AutoFill feature to create the series for you. AutoFill can fill a series of numeric values such as 5, 10, 15, 20, and so on; a series of date values such as January 1, 2011, January 2, 2011, and so on; or a series of alphanumeric values such as Chapter 1, Chapter 2, Chapter 3, and so on. You can also create your own series with a custom step value, which determines the numeric.

Move or Copy a Range: You can restructure or reorganise a worksheet by moving an existing range to a different part of the worksheet. For example, if you have two related ranges that are far apart on the worksheet, you can move one of them so that the ranges appear close to each other. You can also make a copy of a range, which is a useful technique if you require either a duplicate of the range elsewhere, or if you require a range that is similar to an existing range.

Delete Data in a Range

How to Delete Data in a Range in Excel

 

 

Delete Data in a Range: If your worksheet has a range that contains data you no longer need, you can delete that data. This helps to reduce worksheet clutter and makes your worksheet easier to read. Note that the technique in this section, only applies to deleting the data that exists within each cell in a selected range. It does not apply to deleting the actual range.

Delete a Range: If your worksheet contains a range that you no longer need, you can delete that range. Note that when you delete a range, Excel deletes not just the data within the range, but the range cells themselves. Excel shifts the remaining worksheet data to replace the deleted range. Note that the technique in this section deletes the actual cells from the selected range.

Use a Range Name in a Formula in Excel

Use a Range Name in a Formula in Excel

 

 

You can make your formulas easier to build, more accurate, and easier to read by using range names as operands. For example, the formula =SUM(B2:B10) is difficult to decipher on its own because you cannot tell at a glance what kind of data is in the range B2:B10. However, with the formula =SUM(Expenses), it is immediately obvious that the formula is adding a range of expense values. In a previous video we explain how to define range names.

Reference Another Worksheet Range in a Formula: You can add flexibility to your formulas by adding references to ranges that reside in other worksheets. This enables you to take advantage of work you have done in other worksheets so you do not have to waste time repeating your work on the current worksheet. You can even add references to ranges that reside in other workbooks.

 

&nbsp

Convert Ranges to a Table in Excel

Convert Ranges to a Table in Excel

 

 

 

You can apply Excel’s powerful table tools to any range by first converting that range to a table. In Excel, a table is a collection of related information with an organizational structure that makes it easy to add, edit, and sort data. A table is a type of database where the data is organized into rows and columns: Each column represents a database field, and each row represents a database record.

 

 

How to Assign a Name to a Range in Excel

 

 

 

 
You can make it easier to navigate Excel worksheets and build Excel formulas by applying names to your ranges. A range name is a text label that you apply to a single cell or to a range of cells. Once you have defined a name for a range, you can use that name in place of the range coordinates, which has several benefits.

For example, range names are more intuitive than range coordinates, particularly in formulas; range names are more accurate than range coordinates; range names are easier to remember than range coordinates; and range names make it easier to navigate a worksheet.

Define a Range Name: Before you can use a range name in your formulas or to navigate a worksheet, you must first define the range name. You can define as many names as you need, and you can even define multiple names for the same range. You can create range names by hand, or you can get Excel to create the names for you automatically based on the existing text labels in a worksheet.

Use Worksheet Text to Define a Range Name: If you have several ranges to name, you can speed up the process by getting Excel to create the names for you automatically based on the range’s text labels. Text labels make a worksheet easier to read and understand, but in this section you see that they also make it easier to define range names.

For example, if you have a column of sales data that has the label “Sales” on top, Excel can automatically apply the name “Sales” to that range. You can create range names from worksheet text when the labels are in the top, or bottom, row of the range, or the left, or right, column of the range. Select the range or ranges you want to name.

Navigate a Workbook Using Range Names: One of the big advantages of defining range names is that they make it easier to navigate a worksheet. You can choose a range name from a list and Excel automatically selects the associated range.

This is much faster than scrolling through a workbook by hand, or by entering a cell or range reference into Excel’s Go To command. Excel offers two methods for navigating a workbook using range names: the Name box and the Go To command.

Delete a Range Name: If you have a range name that you no longer need, you can use Excel’s Name Manager to delete it. This is a good practice, because you do not want to have unused range names in a workbook. Getting rid of unneeded range names reduces clutter in the Name Manager dialog box, and it also makes the Name box easier to navigate.

It is also a good idea to delete unused range names because you may need to reuse the names for different ranges later on.