98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Creating Tables Automatically In Excel 2010
Fri 1st October 2010
Let's begin by creating a list in Excel with some basic values. In this example we have a sales spread sheet with a list of sales including the date, the person who made the sale and the sale value. Highlight the data and go to the Data tab on the ribbon. In the styles section is a 'Format as Table' option. Selecting this option presents a range of automatic tables in a variety of formats. In its simplest form, all the user needs to do is pick a style and the data is automatically put into a table. There is an option to choose if the table contains headers so they can be formatted slightly differently in order to distinguish them from the data.
Rather than having to completely reformat the table each time the user wants to try a different style, hover the mouse over a new style and the live data will show the preview style. The data will only take on the new format if you specifically click on the style. The automatic table that is created has an additional feature besides the formatting. Each column heading has a filter so the user can easily search for records or filter out records within a table. This does not delete any records, but merely removes them from the current view.
A table can be copied. If only the values need to be pasted then paste and select 'Paste Values' from the options list that appears. The data should be returned to list format. If a new column needs to be added or further rows included then simply dragging the columns will drag the table format with it. Simply adding text to a blank cell next to a column will automatically add that column to the table when you click out of the cell. If the additional text should not be part of the table format then there is an option available to remove the automatic formatting. Click the lightening symbol and choose 'Undo Table Auto Expansion.'
It may be that custom table styles are required. Rather than manually formatting each table the style can be added as a table template and used in the same way as any other table style. It will appear at the top of the list in the Custom section. Click the 'Format as Table' button and select 'New Table Style' which is located underneath all the template styles. Each time the user does this, a new table style will be added. Different sections of the table can be formatted individually such as the total row, alternate rows, the headers etc. This customisation is particularly useful if company branding is required. Specific company colours and styles can be set on the template and applied to all tables at the touch of a button. The exact colours can be used using the custom colour pickers on the fill tab. To delete the custom table styles simply right click on them in the list and choose 'Delete.' You can also modify these individual table designs.
The pivot table option is slightly different because a Pivot table will contain totals of the data when a particular section is chosen in the filter. Rather than simply removing the rows from the table view, instead it will sum up the totals automatically. In our example all sales relating to a particular sales person will appear in a total in one row whereas in an ordinary table all the rows will appear separately for that sales person.
Once a table has been created in this way, a separate table formatting tab on the ribbon called 'Table Tools' will appear. This gives the user the chance for more detailed formatting. This tab has some useful functionality worth exploring such as removing the duplicates automatically. A separate summary pivot table can be quickly created from the existing data table.
Author is a freelance copywriter. For more information on training microsoft excel, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-1166-creating-tables-automatically-in-excel-2010.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsThe Royal Society
Scheme Manager Vanessa Kudom Excel Advanced The trainer is an absolute delight and a breath of fresh air because he has a genuine enthusiasm for his subject area and is so passionate! He actually makes excel seem less scary. I would suggest that STL changes restaurants though - I think they are ripping your company off (or perhaps attend as mystery customers) Westminster City Council
Lisa Hodges Access Intermediate Course was useful in that it explains relationships very well and provides a basic understanding of tables and queries. Ulster University
People Development Partner Stephen Keery Excel Advanced - Formulas & Functions Jens his this down to an exact art. |
PUBLICATION GUIDELINES