[Modern Excel] Master the "table" function that separates the structure and format of table data: Tech TIPS

This article is for members only. If you register as a member (free), you can see everything.

Table of Contents

Target: Excel 2013/2016/2019/365

　When creating a table with "Microsoft Excel", every time you add a "row" or "column", you can correct the formatting of the cells or correct the coloring of the rows and columns. Isn't it troublesome to do so?

　Excel has a function called "table" for handling tabular data, and by using this, it is possible to automate cell formatting and color coding. If you want to create a general "table", you can use other useful functions by converting a range of cells to a table.

Therefore, let's introduce how to create a "table" and the benefits of converting a cell range (table) into a table.

Method for converting/deleting a cell range to/from a table

　The functions that are enabled in a table are as shown in the table below. Each function is already in Excel and can be done manually. However, when you convert a cell range to a table, the settings are automatically made, so you don't have to manually set and operate them individually. All of these functions are used most of the time when creating tables. It performs many functions automatically and comes with formatting. I can't think of any good reason to do this manually.

　Here are the default "table style" and the "table style" created by the user (how to create it will be described later). You can change the format as many times as you like later, so just choose the appropriate one here.

After that, the [Create Table] dialog will be displayed, so set "Use the first row as table header" to "On" and create a "table". It is also possible to change the cell range to be a "table" here.

Once you get used to it, use the keyboard shortcut [Ctrl] + [T] to convert the selection to a table. At this time, the "table style" will be the default one, and the "Create Table" dialog will appear immediately.

If you want to return the "table" to a cell range, right-click [Table] - [Convert to range], or select the [Table Design] tab (displayed if there is an active cell in the table) Click [Tools] - [Convert to range].

At this time, the format set as a table remains as it is. In addition, if you want to cancel immediately after making a table by mistake with [Ctrl] + [T] keys, you can use [Ctrl] + [Z] keys (cancel).

Creating your own table style

The user can create their own "table" format (table style) and use it as a standard table format. The standard table format is used when you press [Ctrl] + [T] to convert a range of cells into a table, so setting your own format will make your work more efficient.

"Table style" is done with [Format as table] - [New table style] in the [Style] group on the [Home] tab. The [New Table Style] dialog is displayed. Select the target of formatting from [Table element], and click the [Format] button and [Stripe size] (how many rows and columns to color code), etc. set.

In "Table Style", two patterns of formatting (stripes) can be alternately applied to rows and columns. This allows the table to be striped in a pattern such as two rows of the same color and one row of a different color.

For example, when expressing the values ​​of "first half year", "second half year", and "full year" in rows, it is possible to change the color for the half year and the whole year. At this time, set the "stripe size" to "2" for the "first row of stripes" corresponding to the "first half" and "second half of the year", and set the "stripe size" to "2" for the "second row of stripes" corresponding to the whole year. Set to "1". To add stripes to the columns, specify the "stripe size" for the "first column stripe" and "second column stripe".

　The format is specified in the [Font], [Bordered lines], and [Fill] tabs of the [Format Cells] dialog. Attributes can be specified for fonts, but the typeface cannot be changed. In the table, the font set for the worksheet is applied. Other "Format" and "Alignment" in the [Format Cells] dialog need to be manually specified by the user, just like normal cells.

By checking "On" for "Set as default table style for this document" at the bottom of the [New Table Style] dialog, the created table style will become the default and [Ctrl] + [T ] key is automatically applied when converting to a table. It's also a good idea to give it a descriptive name so that you can easily distinguish it later.

　In a "table" with a table style set, it is applied according to the [Table Style Options] group on the [Table Design] tab. The checkboxes here let you turn on/off formatting for some of the table styles.

　In addition, the priority is determined by the order in which the table elements are arranged where multiple table items overlap. For example, a format set to "last column" takes precedence over "row stripe". In cells with duplicate "table-style" table elements like this, the ones later in the dialog's list take precedence. In addition, the ruled lines set in "Table Style" cannot be edited with the ruled line function on the [Home] tab. Therefore, if you want to specify the ruled lines in detail, it is better to use the ruled line function instead of setting the ruled lines in the "Table Style".

　The "table style" created by the user is saved in the book. For this reason, if you want to use it in other books, it would be better to register it in a "template" etc. Eliminate wasteful work”). When copying a "table style" created in another workbook or template, copy the worksheet containing the "table" to which the "table style" is applied.

　When a worksheet that uses a "table style" is copied to another workbook or template, the "table style" is also copied at the same time. After that, even if the copied worksheet is deleted, the copied "table style" is maintained.

　If you want to delete a "table style", open the [Format as table] menu in the [Style] group on the [Home] tab, right-click on the "table style" you want to delete, and select " select Delete.

Five Reasons to Use Tables

There are various advantages to using a "table" as a cell range, but here are the five main reasons.

Reason 1: Adding formulas to tables becomes easier

　Creating a table in Excel makes it easier to aggregate and calculate using formulas. If the table doesn't have tallies or calculations, you can create the table in Microsoft Word or something else, and depending on the final form, that might be easier.

Copyright© Digital Advantage Corp. All Rights Reserved.