How to Create or Undo a Table in Excel

Microsoft Excel is convenient for creating tables and doing calculations. Its working area is a set of cells to be filled with data. Consequently, the data can be formatted, used for building graphs, charts, summary reports.

For a beginner, working with tables in Excel may seem complicated at the first glance. It is differs considerably from the principles of table construction in Word. However, let us start from the very basics: creating and formatting tables. By the time you reach the end of this article, you will understand there is no better tool for creating tables than Excel.



Creating a table in Excel: a dummy's guide

Working with Excel tables for dummies does not tolerate haste. There are different ways to create a table for a specific purpose, and each of them has its advantages. Therefore, let us start with assessing the situation visually.

Look carefully at the work sheet of the table processor:

table processor.

It is a set of cells in columns and rows. Essentially, it's a table. The columns are marked with letters. The rows are designated with numbers. There are no borders.

First of all, let's learn to work with cells, rows and columns.

How to select a column and a row

To select the entire column, left-click on the letter that marks it.

select the entire column.

To select a row, click on the number it's designated with.

select a row.

To select several columns or rows, left-click on the name, hold down the button and drag the pointer.

To select a column with the help of hot keys, place the cursor in any cell of the column and press Ctrl + Space. The key combination Shift + Space is used to select a row.

How to resize cells

If your information does not fit in the table, you need to resize the cells.

  1. You can move them manually by grabbing the cell boundary with the left mouse button.
    2. grabbing.
  2. If the cell contains a long word, you can double-click on the boundary of the column/row. The program will expand its boundary automatically.
    3. boundary automatically.
  3. If you need to increase the height of a row preserving the column width, use the button «Wrap Text» in the tool bar.
Wrap Text.

To change the column width and the row height in a certain range, resize 1 column/row (by dragging its boundaries manually) – and all the selected columns and rows will be resized automatically.

selected columns.

Important note. To go back to the previous size, you can press the «Undo Typing» button or the hot-key combination CTRL+Z. However, it works only if used immediately. Later on, it will not help.

To bring the rows to their initial boundaries, open the tool menu: «HOME»-«Format» and choose «AutoFit Row Height».

AutoFit Row Height.

This method does not work for columns. Click «Format» - « AutoFit Row Width» Memorize this number. Select any cell in the column that needs to go back to the initial size. Click «Format» - «Column Width» again and enter the value suggested by the program (as a rule, it's 8.43 – the number of characters in the Calibri font, size 11 pt). OK.

How to insert a column or row

Select the column/row to the right of/below the place where the insertion needs to be made. That is, the new column will appear to the left of the selected cell. The new row will be pasted above it.

Right-click on the cell and select «Insert» in the drop-down menu (or hit the hot-key combination CTRL+SHIFT+"=").

drop-down menu.

Select «Entire column» and press OK.

Hint. To insert a new column quickly, select a column in the desired position and hit CTRL+SHIFT+PLUS.

All these skills will come handy when building a table in Excel. You will need to resize the cells and insert rows/columns in the process.

Creating a table with formulas step by step

  1. Fill in the header manually by entering the column headings. Fill in the rows by entering your data. Apply the acquired knowledge in practice: expand the column boundaries, adjust the row height.
    2. expand the column boundaries.
  2. To fill in the «Sum» column, place the cursor in its first cell. Enter «=». In such a way, we inform Excel: a formula will be here. Select the cell B2 (with the first price). Enter the multiplication symbol (*). Select the cell C2 (with the quantity). Press ENTER.
    3. formula.
  3. When you hover the pointer over the cell containing the formula, a small cross will appear in its bottom right corner. It points out the autocomplete marker. Grab it with the left mouse button and drag it to the end of the column. The formula will be copied into every cell.
    4. autocomplete marker.
  4. Designate the boundaries of your table. Select the range containing your data. Click the button «HOME»-«Border» (on the main page in the «Font» menu). And click «All Borders».
All Borders.

Now the column and row borders will be visible when you print the table.

The «Font» menu allows you to format the data in your Excel table the way you would do it in Word.

data in your Excel.

For example, change the font size and highlight the header in bold. You may also apply center alignment, word wrap, etc.



Creating a table in Excel: a step-by-step instruction

You already know the simplest way to create tables. However, Excel can offer a more convenient variant (in terms of the subsequent formatting and work with the data).

Let us construct a smart (dynamic) table:

  1. Go to the «INSERT» tab – the «Table» tool (or press the hot-key combination CTRL+T).
    2. CTRL+T.
  2. This will open a dialog window, in which you need to enter the data range. Check the box for a table with headings. Click OK. It's no big deal if you don't enter the proper range on the first try. The smart table is flexible, dynamic.
smart table.

Important note. You can also take an alternative route: start with selecting the range of cells, then press the «Table» button.

pricelist.

Now enter your data into the ready framework. If you need an additional column, place the cursor in the heading cell. Make the entry and press ENTER. The range will expand automatically.

expand automatically.

If you need additional rows, grab the autocomplete marker in the bottom right corner and drag it downward.

How to work with a table in Excel

With the release of new versions of the program, working with tables in Excel has become more interesting and dynamic. After a smart table has been formed on the spreadsheet, the tool «TABLE TOOLS» - «DESIGN» becomes available.

TABLE TOOLS.

Here you can name the table or resize it.

Various styles are available to you, as well as the opportunity to transform the table into a regular range or a consolidated sheet.

MS Excel dynamic electronic tables offer immense opportunities. Let us begin with the basic skills of data entry and autocompletion:

  1. Select a cell by clicking on it with the left mouse button. Enter the text/numeric value. Press ENTER. If you need to change the value, place the cursor in the cell again and enter the new data.
  2. When you enter a value repetitively, Excel will recognize it. You will only need to enter several symbols and press enter.
    3. enter several symbols.
  3. In a smart table, to apply a formula to the entire column, enter it in the first cell. The program will copy the formula in other cells automatically.
    4. SUM.
  4. To calculate the totals, select the column containing the values plus an empty cell for the future total and click the «Sum» button (the «Editing» tool group on the «HOME» tab, or press the hot-key combination ALT+"=").

When you click on the little arrow to the right of every subheading in the header, you obtain access to the additional tools for working with the data in the table.

working with the data in the table.

Sometimes the user has to work with huge tables, in which you need to scroll several thousand rows to see the totals. Deleting the rows is not an option (you will still need the data later). However, you can hide them. To this end, use number filters (depicted in the image above). Uncheck the values that need to be hidden.


en ru