First a quick personal update: We (Jo, kids & I) are in beautiful Virginia Beach. Yesterday, we had a perfect, lazy beach day. We woke up late, went to beach where kids played in sand. Got back to our house (rented thru airbnb) when it started raining, had lunch and took a nap. We got up and went to beach again, rode a family bike, watched several street performances, bought souvenirs, ate Thai food and drove home at 11. Long story short, we are having way too much fun and I feel like cheating on you by not posting anything Excel. So,
I have a poll for you.
What do you use Excel Tables for?
I will go first, I use them for,
- Maintaining all my raw data, so that I can use structural references
- Maintaining various trackers, for example I am tracking all our expenses in USA in a table.
- For quick formatting of data (zebra lining, filters, sorting etc.)
What about you? What kinds of data are you holding in tables and how are you using them. Please share using comments. Go.
Learn: Introduction to Tables, Introduction to structural references.
PS for our US readers: Have an enjoyable 4th of July & weekend. We are driving to Washington DC to celebrate the day with our friends and watch fireworks at National mall.
66 Responses to “What do you use Tables for? [poll]”
Sounds like you're having a great trip! I'm sorry I missed you on your passes through Columbus.
I definitely use tables for the reasons in your list -- especially the first bullet. One of my other favorite uses is for data validation lookups -- put a list of values in a table on a hidden tab, and then use an INDIRECT reference to the table for data validation dropdowns.
Tables are an excellent executed front end and poorly back end. Just imagine if PowerPivot could run Tables in the background.
I use them for solving problems at Chandoo.org
I rarely use them outside that
I'm not sure if my answer is a good answer or not
I use tables for maintaining raw data too. Plus, it makes entering and auditing formulas a lot easier! Unfortunately, some of my larger data sets are bogging down Excel. Time to finally figure out Power Pivot!
Hmm, good question, I guess just the same as you, however also for validation and calculation.
I guess that I return the data into another sheet, which is then presented to the user.
I was managing the birth of a new college with 1,700 students enrolled. I used a table and sub tables to manage all student achievement records:
Mid term test
Big table ... formulas sorted out:
Sorting to answer queries was a breeze
Created class lists using filtering
We didn't have database software or capability!
I was managing the birth of a new college with 1,700 students enrolled. I used a table and sub tables to manage all results for 4 semesters.
? Maintaining various trackers, for example I am tracking all our budget in a table.
? For formatting of data sorting
I use tables for my exercise stats, then created a pivot table for yearly and monthly analysis.
I use tables for almost all my raw data as they are quick and easy!
My favorit use of tables is to import XML data using XMLMaps. It is the easiest way, import and format data from xml source thru the Developer - Import function manually or running VBA code. These tables are then used as a source for many pivot tables with capability to refresh data source and pivot tables from a new or updated xml file (just a click).
The second situation is to manipulate data columns (moving, copying) with VBA code without knowing the exact position of the desired column(s).
Easy way to filter, sort data. And then as the data source for a pivot table.
I use a table to track my daily blood sugar and my daily weight, then I use a vlookup to a chart fixed up with a slider to compare blood sugar to my daily weight over a rolling 30 day period.
I use them to create an expanding dataset within a worksheet, which, when linked to a set of SUMPRODUCT formulas, allows Excel to calculate the value of transactions completed by our clients. The clients get a preformatted workbook, they input their data for the work they've done into the table, the SUMPRODUCT formulas calculate the value of the transactions in the table, and we then pay them for the work they've undertaken as calculated by the SUMPRODUCTs once they send the completed workbook back to us.
I use the table for history of all Tickets closed for my dept since 2009, this helps me to find the trends during Month/QTR/Year end. From this table then I produce YTD/MTD statistics with Pivot charts and provides the user Pivot tables as well for more Drilldowns of their interest.
I use is as a database to feed other spreadsheets.
easy way to structure data, much more easier to filter, select and pivot the data and fill formulas +
reducing file size 🙂
This main reason I use tables is to "automate" reports. As data is added to the table the named range automatically updates. I also enjoy how formulas are automacially copied down.
Lately I've been using tables to keep checklists.
I use them for structured references, but only when the list size isn't substantial. I've found they can take up a lot of file storage when the list grows big, at least in versions 2010 and before.
Every month I use a report to extract data about how well we are doing. As long as I do not delete the table and only delete table rows every month I can paste new data. My table and all my formulas update as my data expands and contracts. If I pivot on the table all I do is refresh pivot data. This way I don't recreate or edit my formals I just update the data
I use tables for managing all types of data, for consolidation (pivots) and for graphs
I use tables in conjunction with structured references and data entry forms in data models and dashboards.
I'm currently re-modelling my project portfolio workbook to make it more usable but maintaining its constructional simplicity by not relying on VBA. That way, it can be maintained by other BI team members with the minimum of documented explanation.
I mainly use tables for raw data (it makes for easy reference), and I use them for data display, since I can format them quite easily.
Also, glad to hear you're enjoying our quaint, beach town. It's great fun for families and there are a number of wonderful museums and exhibits within 20 minutes of the oceanfront resort area.
Creating valid tables and naming them within Excel (a one-worksheet workbook) is the BEST way to import valid table data into Access.
use table for maintaining my office data (all) because it help me a lot
I use Tables in excel to take advantage of
5.Auto Named Ranges.
6.Recently to create relationships using power pivot. 🙂
And Finally to maintain data and formulas in a structured way.
***I Love Chandoooooo.org** *
I use tables to store all kinds data, qualitative or quantitative. As a researcher, educator, and trainer, I find the Tables immensely helpful as they lend themselves for a variety of manipulations.
Secondly Tables automatically forces the user to structure the data and makes the subsequent steps namely reports, analysis, etc., much easier.
Well excel tables are my bed and breakfast. My starting point putting the raw data into excel table.
I use tables for below:
- Track my daily activities
- Preparing small projects / initiatives plan
- Tracking my expenses
- Budget planning for my organization
- Tracking daily performance of my team
- Tracking weekly reports of my team
- Preparing responsibility matrix
I use tables for tracking cash receipts against sales invoices. So that I can look up the amount of cash received against each invoices.
I use tables to maintain all my expenses. So that I can retrieve annual expenses, budgeting and forecasting. Also use tables to make Student report as it includes their exam details, certification details and certification renewal details.
I use tables for structuring data from Incident Management tool and converting it in understandable graphs.
I use tables to:
* Store sales data;
* Sort & search;
* Use this data to produce monthly reports via sumproduct formulas
* Create dashboards
I use tables as a basis for pivot tables. When new information is added in the table, the pivot gets updated too.
Tables are also great when using structural references and they speed up the formula writing a lot. ;))
Tables are also used when using data validation (lists). If new data is added to the list, the table reference in the data validation makes it easy to add the new info in the drop down list.
Last but not least, I use to make mini tables to use them in dashboards and so I can combine them with other data. The combination of tables - pivot tables - dashboards works great to combine different information and to have the dashboard updated very easily...
I use table/s for :
> store raw data files from audit finding of my team.
> use it for data calculations as dynamic range - pivot tables
> use it for for data validation.
> use it to (try to)build dashboard.
> and create reports weekly, monthly report, and drill down data for the past months or years.
Definitely you will be force to organize and format your tables based on their data to get more accurate results..
I use tables to automatically download data from another system (using Data Connections). This data can then be easily sorted, filtered, you can pivot it etc.
I have a QUESTION though....
1) As described above I can pull e.g. our data for 2013 into a Table. This data is 'real/live'
2) I would then like our users to be able to 'enhance' this data, to create a 'budget' for next year. This could take 2 forms:
i) apply e.g. a percentage +/- to the 2013 items, to derive an estimate for the equivalent item(s) next year (in e.g. 2014)
ii) ability to add some completely new rows, for 'new' items for next year (budget figures only).
I would then like a final pivot, showing the 'actuals' for 2013, along with the projected/budget figures for 2014. Then - finally - if we 'Refresh' the Live data, to get the latest position, we don't want to lose the 'enhanced' data (either the linked percentages on each item, or the 'new' items). So we want the best of both worlds - the 'hard/real' data extracted from our systems, the 'projected/soft' budgets for next year, and a link between these 2!!! Is this just being greedy?? Any help/tips/comments would be much appreciated (as always!)
I use them to :
- store raw quality results (I know I know, should be in Access but no one where I work knows how to use it but me)
- track/schedule quality audits
- source for vlookups for pricing formulas
- track preformance results
- track a myrid of other client related information needed for pricing
I use Tables as:
1. Store raw data
2. As sources for pivot tables, especially after replacing raw data with a new set.
3. Source fields for drop down boxes in VBA forms.
4. Places to put recordsets returned via SQL queries and used in dynamic reports (auto grow).
5. Where every I am handling datasets in Excel.
6. As source for generation of a bunch of different reports, usually with help of pivot tables and charts!
A little bit of everything: but mostly I use them when I have a bunch of raw data that needs tweaking, lookup-ing, filtering, etc before it goes into a pivot table. I also suggest more casual users make heavy use of them because structural references are in plain english (whatever, you get the idea) and don't require awareness of absolute vs. relative references.
I use excel for
(A picture says more than words (tables) )
And Pivot ! The most powerful feature of Excel
This doesn't seem to work for me ...
I use Table, to eat, work and play...!!
All aspects of Facilities management in a Technology centre.
PPM Scheduling, Break/fix, Projects work, dashboards, resource management and cash flow levelling.
Trending and forecasting are also used with Excel along with break even analysis.
to impress my boss 😀
I am using table because we can maintain data in a good manner. We can apply formula easily in row and column level.
TABLES HELPS ME TO ANALYZE THE DATA EASILY
I have to admit that although I'm a heavy excel user, I don't see much meaningful benefit of tables. Excel is already set up as rows and columns, and I like the control that relative and absolute references afford. Also I think it's confusing to talk about two kinds of data tables: the first is the subject of this post and the Data Table that is now in the Data menu under What-if Analysis.
I use tables instead of dynamic ranges - brilliant for vlookups and pivot tables. Convert your lookup array to a table, then name that table as a range (very important). Use that named range and as you add to it, it expands automatically. The one thing you need to be careful about is that it means the array can sometimes be case-sensitive (but you can turn that off by using Options:Advanced: Transition Formula Evaluation (clear the tick box) (Thanks to Rorya at Experts Exchange for putting me onto that). Also if you convert your pivot table to a table - it means all you have to do is refresh (instead of reselecting the Data Source) to pull in new data.
Hello Chandoo, I am glad you and your family are enjoying your visit - hope the fireworks were a blast (pun intended)!
I am currently using tables in Excel 2010 to track labor daily labor for a project. I have a couple of lookups in the table, and I find that it takes about 23 seconds to cut a row and paste it somewhere else in the table. Inserting more rows also is rather time-consuming, at almost 10 seconds. I wonder, does anyone else have this problem in tables? I only have about 900 rows in the table, and 15 columns.
Never mind - I went out & found some optional updates to install and one of them seems to have cured the issue, hooray!
Mainly for formatting. Too many people still have Excel 2007 or don't know how to use tables effectively, so can't add/modify calculations based on my use of table structured references.
I use tables to replace the need for dynamic ranges made with offset or index:index.
Pivot Tables, Charts and Dashboards
I use them for everything, all the time. I love them.
The most useful things about them for me is the "auto-grow" and how you can link a pivot table to the named table range. This makes an easily update-able pivot table structure, which powers every large Excel report I do. I stick a new week's data on the bottom of the table and press "Refresh All" in the report/dashboard and watch all the graphs update together. Very satisfying 🙂
More and more, I feel like I'm only using tables to (more easily) import data into PowerPivot.
A bill of materials (BOM) is a list of electronic components that go onto a printed circuit board to make an electronic instrument or device.
A net list is an ASCII table that defines all the electrical connections to/from/between all the components (as listed in the BOM) on the circuit board.
I am trying to use Excel somewhat as a database to make a Master Table that can list (upon demand for report) any components that connect to a common node (electrical connection) as defined in the netlist and used as a query element.
The idea is to link the netlist and the BOM together in such a manner that I can query various items for reports.
For instance, I can query the maximum working voltage parameter of each electronic component in the BOM and test that the voltage is below the voltage that the Netlist tells me is present.
I have played with MS ACCESS and found it cumbersome and difficult to build AND/OR conditions into the query. In some ways it seems more natural to use Excel, but will need some sophisticated V/HLOOKUP and other indexing tools that I haven't learned enough about yet.
I can send samples of the Netlist and BOM if you have any questions or can help me decide between Access and Excel for this task. Eventually a Dashboard could make the queries/reports easier to generate.
thanks in advance for any help you can render. I greatly enjoy your site and have already learned a lot from you.
tables are good speciailly for megaformula.
I dont use table - when formula if columns and rows have different formula because of the forced-auto-complete-feature.
When using any formulas that involve ranges, it makes referencing the ranges intuitive and you get the added benefit of a dynamic range.
I use Excel in three different segments of my professional life.
1. My 9 to 5: Marketing Research data mining and create reports
2. My Side Business: Real Estate industry research and performance reporting.
3. My Side Project: Creating an automated Fantasy Football tool for draft day
1. For databases (which I shouldn't but still do cause everyone uses excel and not access. Need to share info)
2. For Engineering Bill Of Materials (pretty much same as point 1 but I thought it needed a special mention since I use this extensively)
3. For Data validation entries
- Test planing;
- Processing of test results;
- Calculating Measurement uncertainty;
- Giving Requrments and Specifications
- Purchasing and Project BOM
- To Do list
- Enjoying 🙂
need to learn attaching user form collect details of Employee by mail merger and collect response from outlook by Running VBA Queries
how to to rite Queries for this can you help in this
I am Data Analayist, Dealing with All Employee details
I use tables for:
a) Ensuring consistent formulas in columns
b) Avoiding the use cell references in formulas
c) Ease of data entry (adding new row, deleting rows etc.)
Enjoy your time in the USA!
Tables are great for storing the raw data that you are reporting. I pretty much extract data everyday into Excel and use it to dump data into an access database or I often use it simply to build pivot tables to summarize data and build dashboards for my peers and bosses.
I think you also hit the nail on the head with filtering. Often times we don't require a summary of the data and therefore don't need a pivot table. It's moment like these that are perfect for using regular tables to sort, filter and use conditional formatting to get the answer that you are looking for in business or in your personal endeavors!
Great question to get the wheels turning.. thanks Chandoo!
I am glad to read your posts.