Create a Table with a Keyboard ShortcutTo get the Excel copy and paste problems sample file, and see more tips and. After you press the OK button you see a nice looking Table and if you select a cell in your Table youll see that there is a new tab named Design on the Ribbon. How do I convert my range to a Table: If you select a cell in your normal range and press the Table button it will show you this dialog. In Excel 2007-2016 we have an option on the Ribbon on the Insert tab named Table.
![]() ![]() Video TutorialThroughout this post, I’ll be referring to various parts of a table, so it’s probably a good idea that we’re both talking about the same thing.This is the Column Header Row. This post will tell you about all the awesome features tables have and should convince you to start using them. Without a table, the only thing relating the data is proximity to each other.Ok, so what’s so great about Excel Tables other than being a container to organize data? A lot actually. Similarly, you might put all your customer data into one Excel table.Tables tell excel that all the data is related. If it’s enabled, it will be the last row of the table. By default, tables don’t include a total row but this feature can be enabled if desired. A table must contain at least one column.This is the Total Row of the table. The body of a table can contain one or more rows and if you try to delete all the rows in a table a single blank row will remain.This is a Column in the table. The body is where all the data and formulas live.This is a Row in the table. Column headings must be unique in the table, they cannot be blank and they cannot contain formulas.This is the Body of the table. You’ll be able to confirm this range later on. Select any cell inside your data and Excel will guess the range of your data when creating the table. Create a Table from the RibbonCreating an Excel Table is really easy. Each cell in the total row will have a drop down menu that allows selection of various summary formula. Contextual Table Tools Design TabWhenever you select a cell inside a table, you will notice a new tab appear in the ribbon labelled Table Tools Design. If this is unchecked Excel will create generic column headers for the table labelled Column 1, Column 2 etc…Press the Ok button when you’re satisfied with the data range and table headers check box.Congratulations! You now have an Excel table and your data should look something like the above depending on the default style of your tables. You can also adjust this range by manually typing over the range in the input field.Checking the My table has headers box will tell Excel the first row of data contains the column headers in your table. Excel guesses the range and you can adjust this range if needed using the range selector icon on the right hand side of the Where is the data for your table? input field. It’s easy to remember since T is for Table!There is actually another keyboard shortcut that you can use to create tables, Ctrl + L will also do the same thing. The process is the same as described above but instead of using the Table button in the ribbon you can press Ctrl + T on your keyboard. Create a Table with a Keyboard ShortcutYou can also create a table using a keyboard shortcut. This is where you’ll be able to name your table, find table related tools, enable or disable table elements and change your table’s style. When the active cell moves outside the table, the tab will disappear again.This is where all the commands and options related to tables will live. Each table must have a unique name within a workbook. There are a few rules for a table name. You should always rename your table with a descriptive and short name.Not all names are allowed. Name a TableAnytime you create a new table Excel will give it an initial generic name starting with Table1 and increasing sequentially. ![]() You can filter to show only the table objects using the Filter button in the upper right hand corner and selecting Table Names from the options.You can then edit any name by selecting the item and pressing the Edit button. The table objects will have a small table icon to the left of the name. You’ll be able to see all your named objects here. Instead, you can change any of your table names without going to each table using the Name Manager.Go to the Formula tab and press the Name Manager button in the Defined Names section. Easy, and the name is changed.Changing your table name this way requires navigating to your table and selecting a cell within it, so it can be tedious if you need to rename a lot of tables across different sheets in your workbook. If you think about it, when you first name a table you’re actually renaming it from the generic name of Table1 to a new name.So go back to the Table Tools Design tab and type your new name over the old one in the Table Name and press Enter. How do you convert it back into a regular range?If changing it to a table was the last thing you did, Ctrl + Z to undo your last action is probably the quickest way.If it wasn’t the last thing you did, then you’re going to need to use the Convert to Range command found in the Table Tools Design tab under the Tools section.You’ll be prompted to confirm that you really want to convert the table to a normal range. Convert a Table Back to a Normal RangeOk, you changed your mind and don’t want your data inside a table anymore. Click on any of the tables listed and you will be taken to that table. Click on the small arrow on the right side of the name box and you will see all table names in the workbook listed. Navigate Tables with the Name BoxYou can easily navigate to any table in your workbook using the name box the the left of the formula bar. Proshow producer for macSelect the Entire ColumnIf your data is not inside a table then selecting an entire column of the data can be difficult. Right click anywhere in the table and select Table from the menu and then Convert to Range. You can do this by going to the Home tab then pressing the Clear button found in the Editing section, then selecting Clear Formats.This can also be done from the right click menu. You’ll need to manually clear this from the range if you want to get rid it. Hover the mouse cursor over the column heading until it turns into a small arrow pointing down then left click and the entire column will be selected. If the column has blank cells, then you might need to press the Down arrow key a few times until you reach the end of the data.The other option is to select the first cell and then use the scroll bar to scroll to the end of your data then hold the Shift key while you select the last column.Both options can be tedious if you have a lot of data or there are a lot of blanks cells in the data.With a table, you can easily select the entire column regardless of blank cells.
0 Comments
Leave a Reply. |
AuthorPatricia ArchivesCategories |