## Microsoft® Excel 2013 Foundation

Certification Training
8809 Learners
5 Chapters +

# Create Cells and Ranges in Excel 2013 Tutorial

Welcome to the third lesson ‘Create Cells and Ranges’ tutorial, which is a part of the Microsoft Excel 2013 MOS Foundation Course. In this lesson, we will study about the cell and cell range in Excel along with how to format cell structures and content.

## Objectives

By the end of this lesson on ‘Create Cells and Ranges,’ you’ll be able to:

• Explain a cell and a cell range

• Create data using a fill series

• Format cell structure and content

• Interpret a data range using conditional format methods

• Create named ranges, data groups, and sparklines

## Cells and Ranges

Let us first understand what a cell is. A Cell is an intersection of a column and a row, which is identified as a rectangular box in Excel. A cell stores data.

There are more than seventeen billion cells in each worksheet. Each cell has a cell reference, which is a combination of a column letter and a row number.

A Cell  Range is a selection of multiple cells containing data. It can be as small as a list of dates, or as large as a multi-column table with thousands of rows of data.

Data range can be made up of adjacent or non-adjacent cells. An adjacent cell range is structured together, whereas, a non-adjacent cell range consists of two or more separate blocks of cells separated by rows or columns.

In the next section, we will learn how to create data using the fill series functionality.

## Create Data using FillSeries

In Excel 2013, data can be entered manually. However, at times you need to fill the cells with data. This could be automated by dragging or using the Fill Series option. The pattern or sequence of data that needs to be created can also be customized.

One or more series can be created based on the selected cells. The different types of series are:

Linear type

This type of Fill series calculates the value of the series by adding the value entered in the step value and stop value.

Growth type

It calculates the value of a series by multiplying each cell in the series by the step value.

Data type

This type of Fill series calculates the value of a series by increasing each date in the series.

AutoFill Type

This type of fill series creates a series that produces the same results by dragging the pill handle. A step value is a difference between two consecutive numbers. A stop value is the last number to be filled in a series.

A set of new employees have joined the company. John has a list of names of these employees, and he wants to assign serial numbers against each name in the excel file.

He wants to create this data using Fill series options offered by Excel 2013.

Employee names are displayed in the list shown here.

Steps to follow:

• Identify the column to add a serial number.

• Click a cell to begin the series.

• Type the first value of the series in that cell and press Enter.

• Click to select that cell

• Click Fill from the Editing section to view the options.

• Click to select Series… option from the drop-down menu.

• Select the relevant options in the Series pop up window.

• Enter the Step value, Stop value and click OK.

• The Series is generated.

## Create Data using AutoFill

The AutoFill function is useful in customizing lists of a specific design. It does not appear while copying data from adjacent cells.

AutoFill allows you to create data in the following ways.

The Copy Cells option in AutoFill allows you to copy data from one cell to another.

The Fill Series option in AutoFill allows you to fill series based on the first cell selected in the range on it, maybe numbers or date.

The Fill Formatting Only option in AutoFill allows you to copy the format of the first cell to the selected range.

The Fill Without Formatting option in AutoFill allows you to fill only first cell data in the range without formatting the first cell.

Five new employees have joined the Accounting department last month. John has a list of their names. He wants to use the AutoFill option in Excel 2013 to assign serial numbers and department against each of the names.

Steps to follow:

• Click the File Tab button.

• Click the Options menu item.

• Scroll down to the General tab.

• Click the Edit Custom Lists…

• Click the List entries textbox.

• Type your custom entries in the List entries textbox.

• Click OK.

• In the Excel Options pop-up, click OK.

• Type the first item of your series in the top-most cell in the required column and press Enter.

• Select the top-most cell. Move the mouse to the bottom-right corner of the cell.

• When the fill (+) icon appears, click, hold, and drag to auto-fill the cell range.

## Customize AutoFill Options

Excel 2013 allows customization of AutoFill option. If you want to list a set of data that does not match an available series type or unit, you can create a custom Fill series consisting of the specific list you want the series to conform to.

For example, a list can be created with names, regions and industry-specific reference points. The AutotFill functionality can also be used to copy text or numeric data within a column or a row. Creating custom lists using the auto fill function saves a lot of time.

John is updating data in the sales report. He notices that the serial number column and the product code column have identical numbers and this could be confusing.

So he decides to change the numbers in the columns to alphabets. He needs to customize the AutoFill data options to do this.

This is the employee data table.

Steps to follow:

• Click the File Tab button.

• Scroll down to the General tab.

• Under the advanced options, click the Edit Customer Lists…

• A pop-up window is displayed. Select the new list and click on the List entries textbox.

• Type your custom entries in the List entries textbox.

• Click Ok.

• In the Excel options pop up, click OK.

• Type the first item of your series in the top-most cell in the required column and press Enter.

• Select the top-most cell. Move the mouse to the bottom-right corner of the cell.

• When the fill(+) icon appears, click, hold, and drag to auto-fill the cell range.

## Paste Options

Excel 2013 allows you to copy data from another worksheet, webpage, or from another application like a Word processor, Presentation or a Notepad. It also allows data to be copied from a source file location to the MS Office clipboard and then pasting it on a worksheet.

When pasting data, there are several options to insert values, formulas, format or link the original source data into the new location. Paste data without borders and paste as a link or a picture.

In the next section, we will discuss how to use the paste special functionality.

## Paste Special

Paste special allows you to copy and paste data in the following ways:

All

This option allows you to copy and paste data with formulas and the data format

This option allows you to copy and paste only comments

Validation

This option allows you to copy and paste only validation rules without copying data

All using source theme

This option allows you to copy and paste data with cells styles

All except borders

This option allows you to copy and paste data except for the borders

Column Widths

This option allows you to copy and apply the column width of the selected cells to the newly pasted cells.

All merging conditional formats

This option allows you to paste conditional formats to a cell range. This preference is active only if the source data contains any conditional formats.

You can have other options to copy and paste such as:

• Formulas

• Values

• Format

• Formulas and number format

• Values and number formats

You can use basic mathematical operations such as add subtract, multiply, and divide while using the paste special function.

Skip blanks checkbox

This option allows you to skip and ignore blank cells while pasting data without overwriting the current cell entries.

Transpose checkbox

This option allows you to change the orientation of the data pasted such as rows to column and column to row

This option allows you to copy cell entries and establish a link between the pasted data and the original entries. The changes made in the original sells automatically get updated in the pasted cells.

In the next section, we will discuss how to format Cell structure.

## Format Cell Structure

Excel 2013 allows you to change the format of a cell, row, column, or an entire worksheet. Some typically applied formatting techniques are:

Text  wrapping

This option allows you to wrap text so that all the data which was previously scattered across multiple cells, will appear in a single cell. This technique assists better visibility of data in a cell.

Text alignment

This option allows you to change the alignment of data in a cell. By default, in a cell, numerical data, numbers, currency, percentages, alignment is from right to left.

In alphanumeric data, the first character starts with alphabets, so the alignment is from left to right. Sell text alignment can be changed to left, right, center, top, middle, and bottom.

Orientation

This option allows you to change the position of the cell entries. By default, entries are horizontal and read from left to right. We can rotate entries for special effects or display more information on the screen or a printed page.

This formatting technique is particularly usual when we have long column headings above columns with short entries.

John is working on an employee data table. The employee names are too long to fit within the cell width. So his manager asks him to ensure that the names fit within the cells, keeping the cell width same.

He also asks him to center align all the entries in the employee name column.

Steps to follow:

• Select the column or cell whose format you want to change

• To wrap text in a given cell, click the Wrap Text option from the Alignment section.

• To change the alignment of the text in a cell, click any of the three alignment options from the Alignment section, and select one option.

## Merge and Center

In Excel 2013, multiple cells can be merged into a single cell in three different ways. They are:

Merge and center

This option allows you to merge selected rows and columns and change cell alignment to center. If you use the merge and center option, by default, the row height is dynamic. It could be increased to fit all the data. However, specially-abled individuals cannot read the merged content.

Merge across

This option allows you to create a separate merge cell or each row in the selected area. It maintains a default alignment for the data of the first cell of each row and deletes the data in the selected cells of the first cell of each row.

Merge Cells

This option allows you to merge cells across the selected rows and columns. It maintains a default alignment for the data of the first cell of the merged selection.

Unmerge Cells

This option allows you to unmerge the selected cells and move all the data to the first cell.

John is creating a table with employee data. The table has four columns and John wants to place the title of the table in the center. He can achieve this by using the merge and center feature.

This is the employee data table.

Steps to follow:

• Select the cell range that you want to merge.

• Click the Merge & Center button.

Note: The Merge and Center drop-down offers options to merge and center, merge across, merge cells, and unmerge cells.

## Format Cell Content

Excel 2013 allows you to change the font size, font style, font color, and font effects. By default, the font style and size is Calibri 11.

Font size

This option allows you to change the font size of the text from a minimum size of 8 to a maximum size of 72

Font style

This option allows you to change the font style to bold, italic, or bold and italic. By default, the font style is set as regular.

Font color

This option allows you to change the font color. The default color set for a new workbook is black.

Underline

This option allows you to change individual attributes from the font group on the home tab or from the mini toolbar.

Effects

This option allows you to change the font effects to strikethrough, subscript, and superscript. By default, there is no font effect selected.

After looking at the employee data table, John's manager has asked John to make changes to the title cell so that it looks bigger and bolder than the rest of the data.

John wants to format the cell content to change the default size of the data.

The employee data table is shown here.

Steps to Follow:

• Select the cell where the content has to be formatted.

• Click Format and select the Format Cells from the drop down. Alternatively, you can press Ctrl + 1 keys.

• The Format Cells pop up window is displayed. Click the Font tab.

• Select the bold menu item in the Font Style combo box.

• Select size twenty from the Size combo box and click OK.

## Cell Styles

A sale style is defined as a set of formatting characteristics, such as fonts and font sizes, number formats, cell borders, and cell shading. To prevent anyone from making changes to specific cells, we can use a cell style that locks the cells.

Several formats can be applied quickly by using built-in cell styles. Cell styles can also be modified or duplicated to create a custom style based on requirements.

Sales styles are based on documented themes that are applied through the workbook. When we switch from one document theme to another, cell styles are updated to match the theme of the new document.

If you need to use a specific, undefined cell style, you can manually format a cell and then save the formatting combination as a cell style so that it can be applied elsewhere.

John's manager assigned him a task to prepare a sales report for the previous quarter. He instructs him to include details like employee name, employee code, number of products sold, and Region.

He also tells him to apply various cell styles in the report the better presentation.

The data for the sales report is shown here.

Steps to follow:

• Click to select the cell which displays the title of the report.

• Click to select the Cells Styles option under the Styles section.

• The Cell Styles option panel displays the various style options grouped under different heads. Click to apply your preferred style for the title of the report.

• Similarly, select specific portions in your report and apply your preferred style from the Cell Styles option.

## Number Formats

By default, cells in a new worksheet are assigned a general format. You need to format the cells with the appropriate number format based on the type of information it needs to hold.

There are several types of popular number format as discussed below:

Number

This format is used for numbers, numbers with decimals, separating one thousand with a comma and displaying negative numbers format.

Currency

This format is used for displaying numbers with decimals and currency symbols.

Accounting

This format is used for displaying numbers with decimals and currency symbols. In this format, if any cell contains zero, it will be displayed with a hyphen (-)

Date

This format is used to display dates.

Time

This format is used to display time

Percentage

This format is used to display percentages with or without decimals.

Text

This format is used to display alphanumeric data and keep track of the trailing zeros, which can convert numbers into a text format

Special

This format is used to display zip code and SSN numbers.

Custom

This format is saved in the workbook in which it was created and is not available for other workbooks unless the workbook is saved as an excel template.

The other number formats are general, fraction, and scientific. If you want a number to be treated as text, you need to apply the text format. An inbuilt format from the type list cannot be deleted.

in the next section, we will understand how to use the format painter function.

## Format Painter

A format painter tool can be used to quickly copy cell formatting such as text, color, background color, and alignment to other cells or the fill function to copy the cell format to adjacent cells.

Former painter is very useful when you extend a worksheet to accommodate new data and need to format new cells to match the original data area.

John has collected employee performance data from the production department in an excel file. He recently added performance data for the sales team and wants to format it as per the earlier data, using the format painter option offered by excel 2013.

The performance score for the production department is shown here.

Steps to follow:

• Select the cell range with the formatting you like to apply elsewhere.

• Click the Format Painter button.

• Select the cell range for which you need to apply the formatting.

## Data Model with Advanced Conditional Formatting

Conditional formatting in Excel 2013 allows you to add visual keys to the data that provide the user with information on how each entry within a data range relates to those around it.

You can make the data easier to interpret by using conditional formatting to format cells based on their values. Conditional formatting can be set up by specifying the condition which is called a formatting rule.

The different types of formatting rules are:

Highlight Cells

This rule is used to apply a specific format to cells that contain data within a specified numeric range containing specific text or duplicate values.

Top/Bottom

This rule is used to apply a specific format to cells that contain the highest or lowest values in a range.

Data Bars

This rule is used to fill a portion of each cell corresponding to the relationship of the cell's data with the rest of the data within the selected range.

Color Scales

This rule is used to fill each cell with a color point from a two color or three color gradient that corresponds to the relationship of the cell’s data to the rest of the data within the selected range.

Icon Sets

This rule is used to insert an icon from a selected set that corresponds to the relationship of their cell’s data to the rest of the data within the selected range.

If a predefined formatting rule does not suit our requirements, you can define a custom rule based on standard rules or a formula. This provides unlimited opportunities to precisely define conditional formatting. Multiple conditions can be defined for the same range of cells or table.

In the next section, we will understand Sparklines.

## Sparklines

Sparklines are tiny charts that summarize worksheet data within a single cell to visually represent data trends. Data presented in a row or a column is useful, but patterns can be hard to spot at a glance.

The context with these numbers can be provided by inserting sparklines next to the data. Taking up a small amount of room, a sparkline can display a trend based on an adjacent data with a clear and compact graphical representation.

In Excel 2013, there are three types of sparklines. They are:

Line sparkline

This option represents the relative value of the selected worksheet data.

Column Sparkline

This option represents the data in an individual's column, as opposed to the line view.

Win/Loss sparkline

This option represents or indicates each data point to be positive, zero, or negative. Styles and another formatting can be applied to sparklines in the same way that we do other graphic elements.

The sales manager wants to see the sales trend of each of the employees in his department. John has the data of six employees and the products sold by them in the last three quarters.

For a quick visual representation of the sales trend of each employee, John wants to use the sparklines feature in Excel 2013.

The employee's sales data for three quarters is displayed here.

Steps to follow:

• Select the cell where you want the Sparkline chart to be inserted.

• Click the Insert tab.

• In the sparkline group, click to select any of the three types of Sparklines: Line, Chart, or Win/Loss.

• In the Create sparkline pop up window, select the cell range whose data is to be represented as a chart.

• Click OK.

• You can select the check boxes in the Show group to choose what points to display on the chart.

## Create Named Ranges

Excel 2013 allows you to define a name or a specific range of cells. Named ranges simplify the process of creating formulas, referring to a specific range of data.

Formulas using named ranges are simpler to understand than its standard equivalent. The scope of a named range can span the entire workbook or a specific worksheet. This allows you to use the same name on multiple worksheets.

You can include a comment with each name to provide more information about the range. After defining a named range, the range of name or the cells included in the named range can be changed.

You can also delete a range name definition from the Name Manager. Deleting a cell from a worksheet does not delete any associated range name. Invalid range names are indicated in the name manager by “#ref!” in the value column.

The manager wants John to work on employee sales data so that it can be used to perform other operations. John needs to create a named range for each employee, which can be used later.

Employee's sales data is displayed here.

Steps to follow:

• Select the cell range for which the named range has to be created. Include the range heading.

• Click the Formulas tab.

• In the Define Names group, click to select the Create from Selection menu item.

• In the Create Names from Selection pop up window, select the appropriate cell containing the heading of the cell range.

• Click Ok. Click the Name Manager item in the Formulas tab to view the name range.

• The named range can be updated by clicking the Edit… button in the Name Manager pop up window.

• In the Edit Name pop up window, replace the entry in the Refers to the field with the updated cell range.

## Data Group and Ungroup

Excel 2013 allows you to group specific rows or columns of data within a data range to make working with huge data easy. By the use of groups, Excel inserts a control to the left of the row headings or above the column headings, with which the data group can be contracted and expanded.

You can have column groups and row groups on the same worksheet. However, you cannot have two consecutive groups of rows or columns, they must be separated by one row.

If the data range contains multiple groups that are summarized or subtitled, Excel can group the data into a maximum of eight levels. In fact, Excel outlines the data, making it possible to hide or display as much detail as required.

After grouping or outlining data, you can expand and collapse groups or levels.

To outline by rows, each column must have a heading in the first row.

To outline by columns, each row must have a heading in the first column. In either case, no row or column should be blank.

In the next section, we will understand how to use subtotals.

## Subtotals

In Excel 2013, the Subtotal function allows you to use SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT NUMBERS, STDDEV, STDDEVP, VAR, OR VARP functions to summarize the data of each subset of cells.

The data range must include headers that identify data subsets and must be sorted by at least one column that should be used in the summary.

You must specify the way the data should be summarized in the Subtotal dialog box. After creating subtotals, you can use the controls that appear in the bar to the left of the row.

Subtitles can't be added to tables to achieve this, we need to convert the table to arrange the data and add subtotals.

## Summary

Let us summarize what we've learned till now.

• Fill series allows to customize the pattern or sequence of the data by the use of different types of series.

• The AutoFill function is used to copy and fill cells with data. It can also be used to customize lists of a specific design.

• Cell structure is formatted using three techniques – text wrap, text alignment, and text orientation.

• Merge and Center, Merge Across, and Merge Cells are three ways to merge cells.

• Excel 2013 allows formatting cell content by changing the font size, font style, font color, and font effects.

• Data groups are used to effectively manage large data.

• Subtotals cannot be added to tables; to achieve this, we need to convert the table to a range of data and add subtotals.

## Conclusion

With this, we have come to an end of this lesson on Create Cells and Ranges in Excel 2013. The next lesson focuses on Create Tables using Excel 2013.

Related Courses
Learner Reviews
Related Articles
• Disclaimer
• PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

Name*
Email*
Phone Number*