Keywords: excel tips, excel make a table, excel pivot table tutorial, advanced excel training, excel training, excel help, excel tutorial
Description
Working with Excel – creating, viewing, editing and moving records – and understanding details thereof and deriving conclusion from it become easier with the advanced tools integrated with the Excel tables. For instance, if you want a row or column to come with total value, it would do so. Formatting and sorting data can be done in a quick and easy way without having much competence. And, eventually you can create a professional Excel tables that would help you manage information in a better manner.
Recommended for: Excel 2007, Excel 2010, Excel 2013
Solution:
Here is how-to:
Description
Working with Excel – creating, viewing, editing and moving records – and understanding details thereof and deriving conclusion from it become easier with the advanced tools integrated with the Excel tables. For instance, if you want a row or column to come with total value, it would do so. Formatting and sorting data can be done in a quick and easy way without having much competence. And, eventually you can create a professional Excel tables that would help you manage information in a better manner.
Recommended for: Excel 2007, Excel 2010, Excel 2013
Solution:
Here is how-to:
1. Create a table in multiple ways
Either start with the Home tab or the Insert tab, and choose a design of your choice.
Creating table is the first step in the Excel tutorial. A list with column heading and row is required. Next, select the data, and click “Insert>Table.” Confirm that your selection is correct, and then checkmark the box against “My table has headers,” and click “OK.” Now, excel will come with a formatted table. Select any particular format or style from the Table Styles gallery.
Either start with the Home tab or the Insert tab, and choose a design of your choice.
2. Remove the Filter Arrows
Click on “Filter” option to toggle the display of the filter arrows on or off.
3. Take the Format but Ditch the Table
The act brings uniformity to your table. It can be done as: Click inside the table and then click “Table Tools > Design > Convert to Range.” Click “Yes” when Excel prompts you with “Do you want to convert the table to a normal range?” and the table will revert to being a regular range--but with its attractive formatting intact.
Click on “Filter” option to toggle the display of the filter arrows on or off.
In case some features are desired in the Excel table, but you are not interested in filtering or sorting the content, you can hide the filter arrows as: Click somewhere inside the table and then click “Data > Sort & Filter > Filter.” You can toggle between hiding and revealing factors with a single click. Alternatively, you can use the combination “Shift-Ctrl-L.”
The act brings uniformity to your table. It can be done as: Click inside the table and then click “Table Tools > Design > Convert to Range.” Click “Yes” when Excel prompts you with “Do you want to convert the table to a normal range?” and the table will revert to being a regular range--but with its attractive formatting intact.
4. Fix Ugly Column Headings
Column heading can be aligned to left or right within the cell or row with just a click on the Alignment section under Home tab. You can also reduce or increase indent, i.e. margin between cell outline and the text.
Column heading can be aligned to left or right within the cell or row with just a click on the Alignment section under Home tab. You can also reduce or increase indent, i.e. margin between cell outline and the text.
Select the cells containing the headings, and follow as:
(a) To increase indent: Click “Home > Increase Indent.” If the cell contents respond by jumping to the left edge of the cell, click “Home > Align Right” to return them to right justification. Click “Increase Indent” more than once as necessary to position the heading text well clear of the filter arrows.
5. Add New Rows to a Table
The behavior of Rows is different in a table and in regular Worksheet. For the addition of a new row in a table, or to sort out issues with the visibility of row, click in the bottom right cell in the table and press the “Tab” key. The step adds a new row, just as it would if you were working with a Word table.
Addition of rows to the end of a table is quite simple. Just dragging the small indicator available at the bottom right corner of the table can help you add rows and columns adjacently, if desired. To add a row inside a table, click in a cell either above or below where the row should be inserted and click either “Home > Insert > Insert Table Row Above” or “Home > Insert > Insert Table Row Below,” depending on where you want the new row to appear. Interestingly the created rows and columns will have the same formatting as their neighbors.
The behavior of Rows is different in a table and in regular Worksheet. For the addition of a new row in a table, or to sort out issues with the visibility of row, click in the bottom right cell in the table and press the “Tab” key. The step adds a new row, just as it would if you were working with a Word table.
Addition of rows to the end of a table is quite simple. Just dragging the small indicator available at the bottom right corner of the table can help you add rows and columns adjacently, if desired. To add a row inside a table, click in a cell either above or below where the row should be inserted and click either “Home > Insert > Insert Table Row Above” or “Home > Insert > Insert Table Row Below,” depending on where you want the new row to appear. Interestingly the created rows and columns will have the same formatting as their neighbors.
Calculate Accurate Totals
To make calculation easier, Excel follows your command with functions like SUBTOTAL, Min, Max, Count, and Average or others.
Remember, the SUM function as shown above, also considers the values in cell which are not visible, and that is problematic to some extent. So it’s better to use the SUBTOTAL function. Excel will do this automatically when you use its Total row feature for your table.
In case you need to add a total row to the table, click inside the table, right-click, and choose “Table > Totals Row” or click inside the table and click “Table Tools > Design> Total Row.” In either case, a total row will appear at the foot of the table. If the last column contains numerical values, Excel will automatically use a SUBTOTAL function to sum them.
Interested to add a total to any other column? If yes, follow as: Click in the appropriate cell in the Total row, and in the drop-down menu click “SUM.” And you will find the SUBTOTAL formula in action. Likewise, other functions can also be incorporated with the Excel column.
To make calculation easier, Excel follows your command with functions like SUBTOTAL, Min, Max, Count, and Average or others.
In case you need to add a total row to the table, click inside the table, right-click, and choose “Table > Totals Row” or click inside the table and click “Table Tools > Design> Total Row.” In either case, a total row will appear at the foot of the table. If the last column contains numerical values, Excel will automatically use a SUBTOTAL function to sum them.
Interested to add a total to any other column? If yes, follow as: Click in the appropriate cell in the Total row, and in the drop-down menu click “SUM.” And you will find the SUBTOTAL formula in action. Likewise, other functions can also be incorporated with the Excel column.
7. Create a Chart From Table Data
Behavior of Chart is directly proportional to the content in the Excel Table. Thus, it would contract or expand as you add to or remove data from the table.
8. Enter Data Using a Simple Form
Make use of the hidden Form to enter data into the Excel table.
And to easily access the Form, you can add it to the Excel's Quick Access Toolbar: Click “File > Options > Quick Access Toolbar.” In the Choose Commands From list, click “All Commands” and then scroll down and click “Form,” click “Add” to add the tool to the Quick Access Toolbar, and then click “OK.”
To use the form, click anywhere inside your table, navigate to the Quick Access Toolbar, and then click the “Form” button. It will open a dialog box with separate sections and buttons. The form heading is the sheet name, and the form contains boxes where you can preview the current form data and add new data. To add new data, click “New” and type the data into the relevant text boxes. To view the form data, click “Find Prev” or “Find Next” to move through the data one row (record) at a time. To exit the form, click “Close.”
Behavior of Chart is directly proportional to the content in the Excel Table. Thus, it would contract or expand as you add to or remove data from the table.
8. Enter Data Using a Simple Form
Make use of the hidden Form to enter data into the Excel table.
And to easily access the Form, you can add it to the Excel's Quick Access Toolbar: Click “File > Options > Quick Access Toolbar.” In the Choose Commands From list, click “All Commands” and then scroll down and click “Form,” click “Add” to add the tool to the Quick Access Toolbar, and then click “OK.”
To use the form, click anywhere inside your table, navigate to the Quick Access Toolbar, and then click the “Form” button. It will open a dialog box with separate sections and buttons. The form heading is the sheet name, and the form contains boxes where you can preview the current form data and add new data. To add new data, click “New” and type the data into the relevant text boxes. To view the form data, click “Find Prev” or “Find Next” to move through the data one row (record) at a time. To exit the form, click “Close.”
9. Sort and Filter Table Data
This is what you need the most to derive information from the table in a quick and easy manner. Click the down arrow of any table column, and select the desired option, viz., Sort or Filter. The former option allows you to arrange the data in ‘ascending’ or ‘descending’ order. The Filter options, also termed as AutoFilters feature (pre-defined) offers you a range of options to filter the table content as per demand. However, if that doesn’t serve the purpose, you can create Custom Filter, and create your own. . Alternatively you can create complex filters such as AND and OR filters.
This is what you need the most to derive information from the table in a quick and easy manner. Click the down arrow of any table column, and select the desired option, viz., Sort or Filter. The former option allows you to arrange the data in ‘ascending’ or ‘descending’ order. The Filter options, also termed as AutoFilters feature (pre-defined) offers you a range of options to filter the table content as per demand. However, if that doesn’t serve the purpose, you can create Custom Filter, and create your own. . Alternatively you can create complex filters such as AND and OR filters.
Congrats!
You have successfully completed the advanced Excel tutorial to create
professional Excel tables. For further help, contact Techvedic’s computer
helpdesk, as per below contact-detail:
U.S.
+855-859-0057 http://www.techvedic.com/
U.K.
+800-635-0716 http://www.techvedic.co.uk/
CA
1-855-749-5861 http://www.techvedic.ca/
AU
1-800-197-298 http://www.techvedic.com.au/
We would be
more than happy to help you. We are available 24/7.
0 comments:
Post a Comment