When you open a new workbook, all cells in it are of the default size. You have the ability to modify cells, insert and delete columns, rows and cells if necessary.
In this tutorial, you'll learn how to change row heights and column widths, insert and delete rows and columns, wrap text in a cell, and merge cells.
Working with Rows, Columns, and Cells
By default, every row and every column of a new workbook has the same height And width. Excel allows you to change column widths and row heights different ways.
To change the column width:
If you see (#######) characters in a cell, the column is not wide enough to display the contents of the cell. Simply increase the column width to display the cell's contents.
To set an exact column width:
Select AutoFit Column Width from the Format command's drop-down list, and Excel will automatically adjust the width of each selected column to match the text you enter.
To change the line height:
To set the exact line height value:
Select AutoFit Row Height from the Format command's drop-down list, and Excel will automatically adjust the height of each selected row to match the text you enter.
To insert rows:
When you insert new rows, columns, or cells, you may see a button next to the inserted cells Paste options. This button allows you to choose how Excel should format them. By default, Excel formats inserted rows or columns the same as the cells in the row above the inserted one. To access more options, hover over the Paste Options button and click the drop-down arrow.
To insert columns:
By default, Excel formats inserted columns the same as the columns to the left of them. To access more options, hover over the Paste Options button and click the drop-down arrow.
When you insert rows or columns, be sure to select the row or column by clicking on the header, that is, all its cells are selected. If you select only a cell, then only one new cell will be inserted.
To remove rows:
To remove columns:
Wrapping text and merging cells
If a cell contains more text than can be displayed, you can select the Wrap text in cell option or merge the cell with its empty neighbors. Wrap text causes text to appear in a cell in multiple lines. Merge cells connects adjacent cells into one.
To install Text Wrap:
If you change your mind about wrapping text, simply click the Wrap Text command button again.
To merge cells using the Merge and Center command:
If you change your mind about merging cells, simply click on the Merge and Place in Center command again.
To access the merge options:
Click the drop-down arrow next to the “Merge and Center” command on the Home tab. A drop-down menu will appear.
- Merge and place in the center: Merges the selected cells and places the text in the center.
- Merge by lines: Merges each row of selected cells into larger cells. This command is useful when you combine the contents of many cells in rows but don't want to create one large cell.
- Merge cells: Merges selected cells into one.
- Unmerge cells: Undoes merging of selected cells.
Practice!
- Open existing Excel 2010 book.
- Resize the column.
- Change the line size to 46 pixels.
- Insert a column between columns A and B.
- Insert a line between lines 3 and 4.
- Delete a column or row.
- Try merging multiple cells.
Microsoft Excel is convenient for creating tables and making calculations. A workspace is a set of cells that can be filled with data. Subsequently – format, use for building graphs, charts, summary reports.
Working in Excel with tables for novice users may seem difficult at first glance. It differs significantly from the principles of creating tables in Word. But we'll start small: by creating and formatting a table. And at the end of the article you will already understand that the best tool for creating tables you can't beat Excel.
How to Create a Table in Excel for Dummies
Working with tables in Excel for dummies is not rushed. You can create a table in different ways, and for specific purposes, each method has its own advantages. Therefore, first let’s visually assess the situation.
Take a close look at the spreadsheet worksheet:
This is a set of cells in columns and rows. Essentially a table. Columns are indicated in Latin letters. Lines are numbers. If we print this sheet, we will get a blank page. Without any boundaries.
First let's learn how to work with cells, rows and columns.
How to select a column and row
To select the entire column, click on its name (Latin letter) with the left mouse button.
To select a line, use the line name (by number).
To select several columns or rows, left-click on the name, hold and drag.
To select a column using hot keys, place the cursor in any cell of the desired column - press Ctrl + spacebar. To select a line – Shift + spacebar.
How to change cell borders
If the information does not fit when filling out the table, you need to change the cell borders:
To change the width of columns and height of rows at once in a certain range, select an area, increase 1 column/row (move manually) - the size of all selected columns and rows will automatically change.
Note. To return to the previous size, you can click the “Cancel” button or the hotkey combination CTRL+Z. But it works when you do it right away. Later it won't help.
To return the lines to their original boundaries, open the tool menu: “Home” - “Format” and select “Auto-fit line height”
This method is not relevant for columns. Click “Format” - “Default Width”. Let's remember this number. Select any cell in the column whose borders need to be “returned”. Again “Format” - “Column Width” - enter the indicator specified by the program (usually 8.43 - the number of characters in the Calibri font with a size of 11 points). OK.
How to insert a column or row
Select the column/row to the right/below the place where you want to insert the new range. That is, the column will appear to the left of the selected cell. And the line is higher.
Click right click mouse – select “Insert” from the drop-down menu (or press the hotkey combination CTRL+SHIFT+"=").
Mark the “column” and click OK.
Advice. To quickly insert a column, select the column in the desired location and press CTRL+SHIFT+"=".
All these skills will be useful when compiling a table in Excel program. We will have to expand the boundaries, add rows/columns as we work.
Step-by-step creation of a table with formulas
Column and row borders will now be visible when printing.
Using the Font menu, you can format Excel table data as you would in Word.
Change, for example, the font size, make the header “bold”. You can center the text, assign hyphens, etc.
How to create a table in Excel: step-by-step instructions
The simplest way to create tables is already known. But Excel has a more convenient option (in terms of subsequent formatting and working with data).
Let's make a “smart” (dynamic) table:
Note. You can take a different path - first select a range of cells, and then click the “Table” button.
Now enter the necessary data into the finished frame. If you need an additional column, place the cursor in the cell designated for the name. Enter the name and press ENTER. The range will automatically expand.
If you need to increase the number of lines, hook it in the lower right corner to the autofill marker and drag it down.
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. When a smart table is formed on a sheet, it becomes accessible tool“Working with tables” - “Designer”.
Here we can give the table a name and change its size.
Available various styles, the ability to convert the table into a regular range or summary report.
Features of dynamic MS Excel spreadsheets huge. Let's start with basic data entry and autofill skills:
If we click on the arrow to the right of each header subheading, we will get access to additional tools for working with table data.
Sometimes the user has to work with huge tables. To see the results, you need to scroll through more than one thousand lines. Deleting rows is not an option (the data will be needed later). But you can hide it. For this purpose, use numerical filters (picture above). Uncheck the boxes next to the values that should be hidden.
Excel is not as simple as Word, so many people do not understand it and do not like to work in it. Take this example. You need to print the table, but the output is a page with such small data that you can only see it with a magnifying glass. At one time, our employees simply tormented me with this question. What they tried to do, they moved the columns apart manually, and changed the scale of the table, and changed the font size, but when the sheet came out of the printer, the same thing still came out.
In Microsoft Excel 2003, the print area is configured through the File menu
Open menu - File - Page settings .
In the settings window that opens Page settings , on the tab Page, in field Scale change the number % of natural size either upward or downward.
The same thing can be done differently.
Below, if you set the switch (dot) to post on no more than: and in the small window opposite this inscription set the number of pages, then by this you also set the scale for printing the document. If you set the number 1 , then your entire document will fit on one sheet, and whether it is readable or not does not matter to the program.
IN Microsoft Excel 2007/2010 The print area is configured on the tab Page layout . Select the ribbon on this tab Page settings - Print area.
Here you can even specify only one cell to print.
I hope this article will save more than one soul and nerves.
Unlike Microsoft Word, Excel has one feature regarding the placement of tables on a sheet. If you need your table to fit exactly on an A4 sheet, then you need to adjust it to size. Naturally, this can be done by changing the column width and row height.
Here I will consider a fairly common case when the table should occupy exactly one A4 sheet. I will show an example for adjusting the table by height, since this is the most common. I demonstrate this example quite often in Excel courses and I can definitely say that most people who study Excel have difficulty adjusting the table height. In the original course, the example is more complex, but here I have simplified it, because otherwise the article would take a long time to consider it.
There was an important part of the article, but without JavaScript it is not visible!
Let's say we have a table shown in the figure below. As you can see, it has 25 lines, not counting the header. Task: make sure that this table is in height equal to A4 sheet, and the number of lines must be exactly as indicated.
In the picture you can also see dotted lines, displaying the boundaries of the printed sheet. The position of the borders is affected by both the sheet size and the set print margins. I will not change the size of the fields; The sheet size of the printer is always A4. Therefore, it is self-evident that in this case the table should be stretched so that it becomes larger in height and occupies the entire sheet.
Since most of the table is occupied by rows with its contents, it is by changing their height that the table should be stretched. In the article about effective resizing of cells, posted earlier on website, I wrote about mass changes in row height. In this case, you need to select all 25 rows of the table, and at the same time the header, so that its size does not turn out to be smaller than the rest of the table, which is ugly. After selection, call context menu and select the item with it "Row Height".
Let me remind you that if there is no such item in the menu, then it was not called there. The video at the end of the article shows how to do this.
Then everything is simple - set a certain line height, confirm, look at the result. Of course, it’s unlikely to succeed the first time, but by repeating this operation several times, you can ensure that the table becomes exactly the size of a printed page established size, that is A4. The result is shown below.
To make the process go faster, after each line height change there is no need to reset the selection so you don't have to install it again and again. On Microsoft courses In Excel, I have noticed many times that too many users work incorrectly with the selection of areas: they reset the selection when it is needed, or leave it when, on the contrary, it is no longer useful. Work with selection correctly!
Video tutorial on fitting an Excel table to an A4 sheet
The video shows a simple example of how you can make an Excel table fit on an A4 sheet. This can be useful when creating documents that must occupy only one printed sheet when output to the printer. This, of course, does not mean that any document can be placed on A4!
Let's sum it up
You can place a table on an Excel sheet so that it occupies exactly the entire sheet in height, width, or both dimensions. This mainly applies to all forms, but there are other cases. Key phrases here - changing the line height and "change column width".
You can learn all this and much more on my remote courses. Excel courses which I conduct via the Internet via Skype. Skype lessons are very effective because they are conducted individually. You can read more about how this modern way of learning works.
In Excel, you can make a table smaller or larger. If Excel spreadsheet does not fit on monitor screen
and we have to move it to the side, then we can change its size by changing scale.
How to reduce table size in Excel.
Go to the “View” tab, select the “Scale” section (a magnifying glass is drawn) - here you can set the table size we need, for example 75% or 90%, etc.Nearby there is a button with the number 100%; by clicking on it, we get the scale 100%.
How to increase the size of a table in Excel.
To do this, set the scale to more than 100%. For example - 120%, 150%, etc.
Attention!
These scale changes do not affect the print area, only the appearance of the table on the screen changes.
How to resize a table in Excel for printing.
Printing on one sheet in Excel.
To Excel spreadsheet was one page when printed, you can use several methods.
First way.
Go through the "Office" button - "Print" -> "Preview".
Then we exit the preview and, in our worksheet, dotted markings appear - these are the printable areas.
Print area in ExcelYou can also configure it using the “Print Areas” function on the “Page Layout” tab in the “Page Settings” section. See "Bookmark" Excel sheet"Page layout"".
Here you can see which part of the table is included on one page, and which part is not included. Based on these markings, we adjust the table.We change the width of the columns, the height of the rows, moving the column border manually (more convenient, faster, more visual if you need to adjust the size of different columns/rows). How to quickly change column widths and row heights, read the article “How to change column widths and row heights in Excel.”
You can also change the font size. See the article " Excel text. Format".
Second way. Go to: “Print” -> “Preview” -> “Page Settings”.
In the window that appears, set the page parameters we need.
"Page" tab.
Here you can select the page orientation. “Portrait” – the sheet will be positioned vertically, “Landscape” – the sheet will be positioned horizontally.
Scale - required function: if the table is small, and we need to print it on the entire sheet, or print a large table on one page.
How to print a small Excel table on a large sheet.
You need to set the scale to more than 100%, based on the viewing page.
For example: set the size to 150% -> OK. We looked at it and changed the percentage figure, if necessary.
How to print a large Excel table on one page.
If the table is larger than the page, then in order for the table to fit on the page, we change the scale size down. For example, let’s set it to 80%, etc.
You can also reduce the margins so that the table fits on the page on the Margins page of the Page Setup dialog box. To do this, set your field sizes, or set 0 (zero) everywhere.
But it is convenient to work with fields in view mode. To do this, go to “Preview” and check the “Show fields” checkbox. On the viewing page, all fields of each column will be visible; by moving them with the mouse, you can change the size of each column.
This means that by clicking on the left mouse, the page will enlarge, you can see the details of the table, see if the text goes beyond the boundaries of the column, etc.
If we left-click on the sheet again, it will become smaller again - this way the overall view of the page is better visible.
For another option for setting up a table for printing, see the article “How to print in Excel».
In Exce, you can print not only the table, but also the notes that are located in the cells. See article "
Loading...
