This is a guest post by Daniel Ferry of Excelhero.com.
Have you ever wanted to fetch live stock quotes from excel? In this post we will learn about how to get stock quotes for specified symbols using macros.
One method that has worked well for my clients can be implemented with just a few lines of VBA code. I call it the ActiveRange.
An ActiveRange is an area on a worksheet that you define by simply entering the range address in a configuration sheet. Once enabled, that range becomes live in the sense that if you add or change a stock symbol in the first column of the range, the range will automatically (and almost instantly) update. You can specify any of 84 information attributes to include as columns in the ActiveRange. This includes things such as Last Trade Price, EBITDA, Ask, Bid, P/E Ratio, etc. Whenever you add or change one of these attributes in the first row of the ActiveRange, the range will automatically update as well.
Sound interesting, useful?
In this post, you can learn how to use excel macros to fetch live stock quotes from Yahoo! Finance website. It is also going to be a crash course in VBA for the express purpose of learning how the ActiveRange method works so that you can use it yourself.
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Background – Understanding The Stock Quotes Problem:
The stock information for the ActiveRange will come from Yahoo Finance. A number of years ago, Yahoo created a useful interface to their stock data that allows anyone at anytime to enter a URL into a web browser and receive a CSV file containing current data on the stocks specified in the URL. That’s neat and simple.
But it gets a little more complicated when you get down to specifying which attributes you want to retrieve [information here]. Remember there are 84 discreet attributes available. Under the Yahoo system, each attribute has a short string Tag Code. All we need to do is to concatenate the string codes for each attribute we want and add the resulting string to the URL. We then need to figure out what to do with the CSV file that comes back.
Our VBA will take care of that and manage the ActiveRange. Excel includes the QueryTable as one of its core objects, and it is fully addressable from VBA. We will utilize it to retrieve the data we want and to write those data to the ActiveRange.
Before we start the coding we need to include two support sheets for the ActiveRange. The first is called “YF_Attribs”, and as the name implies is a list of the 84 attributes available on Yahoo Finance along with their Yahoo Finance Tag Codes. The second sheet is called, “arConfig_xxxx” where xxxx is the name of our sheet where the ActiveRange will reside. It contains some configurable information about the ActiveRange which our VBA will use.
All of the VBA code for this project will reside inside of the worksheet module for the sheet where we want our ActiveRange to be. For this tutorial, I called the sheet, “DEMO”.
Writing the Macros to Fetch Stock Quotes:
Press ALT-F11 on your keyboard, which will open the VBE. Double click on the DEMO sheet in the left pane. We will enter out code on the right. To begin with, enter these lines:
Private rnAR_Dest As Range
Private rnAR_Table As Range
Private stAR_ConfigSheetName As String
Always start a module with Option Explicit. It forces you to define your variable types, and will save you untold grief at debugging time. In VBA each variable can be one of a number of variable types, such as a Long or a String or a Double or a Range, etc. For right now, don’t worry too much about this – just follow along.
Sidebar on Variable Naming Conventions
Variable names must begin with a letter. Everyone and their brother seems to have a different method for naming variables. I like to prefix mine with context. The first couple of letters are in lower case and represent the type of the variable. This allows me to look at the variable anywhere it’s used and immediately know its type. In this project I’ve also prefaced the variables with “AR_” so that I know the variable is related to the ActiveRange implementation. In larger projects this would be useful. After the underscore, I include a description of what the variable is used for. That’s my method.
In the above code we have defined three variables and their types. Since these are defined at the top of a worksheet module, they will be available to each procedure that we define in this module. This is known as scope. In VBA, variables can have scope restricted to a procedure, to a module (as we have done above), or they can be global in scope and hence available to the entire program, regardless of module. Again we are putting all of the code for this project in the code module of the DEMO worksheet. Every worksheet has a code module. Code modules can also be added to a workbook that are not associated with any worksheet. UserForms can be added and they have code modules as well. Finally, a special type of code module, called a class module, can also be added. Any global variables would be available to procedures in all of these. However, it is good practice to always limit the scope of your variables to the level where you need them.
In that vein, notice that the three variables above are defined with the word Private. This specifically restricts their scope to this module.
Every worksheet module has the built-in capability of firing off a bit of code in response to a change in any of the sheet’s cell values. This is called the Worksheet_Change event. If we select Worksheet from the combo box at the top and Change in the other combo box, the VBE will kindly define for us a new procedure in this module. It will look like this:
End Sub
Notice that by default this procedure is defined as Private. This is good and as a result the procedure will not show up as a macro. Notice the word Target near the end of the first line. This represents the range that has been changed. Place code between these two lines so that the entire procedure now looks like this:
The Heart of our Excel Stock Quotes Code – Worksheet_Change()
ActivateRange
If Worksheets(stAR_ConfigSheetName).[ar_enabled] Then
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
If Target.Column <> rnAR_Dest.Column And Target.Row <> rnAR_Dest.Row Then
PostProcessActiveRange
Exit Sub
End If
ActiveRangeResponse
End If
End Sub
That may look like a handful but it’s really rather simple. Let’s step through it. The first line is ActivateRange. This is the name of another sub-procedure that will be defined in a moment. This line just directs the program to run that sub, which provides values to the three variables we defined at the top. Again, since those variables were defined at the top of the module, their values will be available to all procedures in the module. The ActivateRange procedure gives them values.
Next we see this odd looking fellow:
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
All this does is check to see if the Target (the cell that was changed on the worksheet) is part of our ActiveRange. If it is the procedure continues. If it’s not, the procedure is exited.
The next line checks to see if the cell that was changed is in the first column or first row of the ActiveRange. If it is, the post processing is skipped. If the change is any other part of the ActiveRange, another sub-procedure (defined below) is run to do some post processing of the retrieved data, and then exits this procedure.
If the cell that changed was in the first column or the first row, the program runs another sub-procedure, called ActiveRangeResponse, which is also defined below. ActiveRangeResponse builds the URL for YF, deletes any previous QueryTables related to the ActiveRange, and creates a new QueryTable as specified in our configuration sheet.
That’s it. The heart of the whole program resides here in the Worksheet_Change event procedure. It relies on a number of other subprocedures, but this is the whole program. When a change is made in the ActiveRange’s first column (stock symbols) or its first row (stock attributes), ActiveRangeResponse runs and our ActiveRange is updated.
Understanding other sub-procedures that help us get the stock quotes:
So let’s look at those supporting subprocedures. The first is ActivateRange:
stAR_ConfigSheetName = “arConfig_” & Me.Name
Set rnAR_Dest = Me.Range(Worksheets(stAR_ConfigSheetName).[ar_range].Value)
Set rnAR_Table = rnAR_Dest.Resize(1, 1).Offset(1, 1)
Worksheets(stAR_ConfigSheetName).[ar_YFAttributes] = GetCurrentYahooFinancialAttributeTags
End Sub
Again, all this does is give values to our three module level variables. In addition it builds the concatenated string of YF Tag Codes required for the URL. It does this by calling a function that I’ve defined at the very bottom of the module, called GetCurrentYahooFinancialAttributeTags.
The next subprocedure is ActiveRangeResponse:
Dim vArr As Variant
Dim stCnx As String
Const YAHOO_FINANCE_URL = “http://finance.yahoo.com/d/quotes.csv?s=[SYMBOLS]&f=[ATTRIBUTES]”
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count – 1, 1).Offset(1))
stCnx = Replace(YAHOO_FINANCE_URL, “[SYMBOLS]”, Replace(WorksheetFunction.Trim(Join(vArr)), ” “, “+”))
stCnx = Replace(stCnx, “[ATTRIBUTES]”, Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
AddQueryTable rnAR_Table.Resize(UBound(vArr)), “URL;” & stCnx
End Sub
Notice that here we have variables defined at the top of this procedure and consequently their scope is limited to this procedure only. This means that we could have the same variable names defined in other procedures but those variables would not be related to these and would have completely different values.
Next notice that we have defined a constant. This is good practice, as it forces us to specify what the constant value is by naming the constant. I could have just used the value where I later use the constant, but then the question arises as to what is this value and where did it come from. Here I have named the value, YAHOO_FINANCE_URL, removing all doubt as to its purpose.
The next line is this:
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count - 1, 1).Offset(1))
and it deserves some explanation. Let me back up by saying that whenever we write or read multiple cells from a worksheet we should always try to do it in one go, rather than one cell at a time. The more cells involved the more important this is. Otherwise we pay a massive penalty in processing time. One of the best optimization techniques available is to replace code that loops through cell reads/writes and replace it with code that reads/writes all the cells at once. It can literally be hundreds to thousands of times faster.
Here we are interested in getting the list of all of the stock symbols in the first column of the ActiveRange. So how do we get them in one shot? We use something called a variant array. Notice that we defined vArr at the top of this procedure. A variant array is a special kind of variable that holds a list of values and it DOES NOT CARE what variable types those values are. This is important when retrieving data from a sheet because the data could be numbers, text, Boolean (True or False), etc. Variants are powerful, but they are much slower than other variable types, such as a Long for numeric data for example. However, in the case of retrieving or writing large chunks of data from/to a sheet the slight penalty of the variant is dwarfed by the massive increase in the speed of data transfer.
It’s very simple to retrieve range data (regardless of the size) into a variant array. All you do is:
v = range
where v is defined as a variant and range is any VBA reference to a worksheet range. And magically all of the values in that range are now in v. Note that v is not connected to the range. A change in any of v’s values does not propogate back to the range, and likewise a change to the range does not make it’s way to v all by itself. v will ALWAYS be a two-demensional array. The first dimension is the index of the rows, the second dimension is the index of the columns. So v(1,1) will refer to the value that came from the top left cell in the range. v(6,9) will hold the value that came from the cell in the range at row 6 and column 9.
For most circumstances this two-dimensional format is fine. But we are only retrieving one column of stock symbols. The procedure will still give us a two-dimensional array, with the column dimension being only 1 element wide. This is a shame because VBA has a wonderful function called Join that allows you in one step (no loop) to concatenate every element of an array into a string. You can even specify a custom string to delimit (go in-between) each element in the output string. The problem is that Join only works on single dimensioned arrays 🙁
But there’s always a way, right? We can use the Application.Transpose method on the 2-D array and presto we get a 1-D array. The rest of the line just specifies what range (the stock symbols) to grab.
The next two lines are:
stCnx = Replace(YAHOO_FINANCE_URL, "[SYMBOLS]", Replace(WorksheetFunction.Trim(Join(vArr)), " ", "+"))
stCnx = Replace(stCnx, "[ATTRIBUTES]", Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
Again a handful, but all we are doing here is replacing the monikers, [SYMBOLS] and [ATTRIBUTES] in the YAHOO_FINANCE_URL constant with the list of stock symbols (delimited by a plus sign) and the string of attributes.
In the final line of the procedure:
AddQueryTable rnAR_Table.Resize(UBound(vArr)), "URL;" & stCnx
we are running another subprocedure called, AddQueryTable and we are telling it where to place the new QueryTable and providing the connection string for the QueryTable, which in this case is the YF URL that we just built.
Nothing unusual happens in the AddQueryTable sub. It just deletes any existing AR related QueryTables and adds the new one according to the options in the configuration sheet.
The PostProcessActiveRange sub is interesting:
If rnAR_Dest.Columns.Count > 2 Then
Application.DisplayAlerts = False
rnAR_Table.Resize(rnAR_Dest.Rows.Count).TextToColumns Destination:=rnAR_Table, DataType:=xlDelimited, Comma:=True
Application.DisplayAlerts = True
Worksheets(stAR_ConfigSheetName).[ar_LocalTimeLastUpdate] = Now
End If
End Sub
Processing Yahoo Finance Output using Query Table & Text-Import Utility:
As mentioned before the data from YF comes back as a CSV file. The QueryTable dumps this into one column. If you were only retrieving one attribute for each stock this would be fine as is. However, two or more attributes is going to result in unwanted commas and multiple attribute values squished into the first column of the QueryTable output. Unfortunately this is poor design by Microsoft, especially when you consider that the QueryTable does not behave like this when it is retrieving SQL data or opening a Text file from disk. You can actually specify this operation to be a text file and it will properly spread the output over all of the columns. To do so, you specify the disk location as being the URL of the YF CSV file, but as Murphy would have it, it’s unbelievably slow and pops up a status dialog as it slowly retrieving the CSV. Using the URL instruction instead of the TEXT instruction at the beginning of the connection string is incredibly fast in comparison, but dumps all of the data into the first column.
So what to do? We’ll just employ Excel’s built-in TextToColumns capability and bam, our data is where we want it.
Our finalized stock quotes fetcher worksheet should look like this:
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Final Thoughts on Excel Stock Quotes
The ActiveRange technique is quite versatile. It can be implemented with other data sources such as SQL, or even lookups to other Excel files, or websites.
In this example it provides a nice way to easily track whatever stocks you may have interest in and up to 84 different attributes of those stocks. You can enable and disable the activeness of the ActiveRange on the fly. You can set the AR to AutoRefresh the data at periods that you set or to not refresh at all.
This is a basic implementation. For example, changing the AutoRefresh setting will have no effect until a new QueryTable is built. That won’t happen until you also add or change a stock symbol or add or change an attribute. An easy enhancement would be to add a little code to the arConfig_DEMO code module to respond to changes to the ar_AutoRefresh named range cell.
Another enhancement would be to eliminate the slight flicker of the update by moving the QueryTable destination to the arConfig_DEMO and then doing the TextToColumns with the destination set to the DEMO sheet. In an effort to simplify this tutorial I have left these easy enhancements as an exercise for you to implement.
Have a question or doubt? Please Ask
Do you have any questions or doubts on the above technique? Have you used ActiveRange or similar implementations earlier? What is your experience? Please share your thoughts / questions using comments.
I read Chandoo.org regularly and will be monitoring the post for questions. But you can also reach me at my blog:
Further References & Help on Excel Stock Quotes [Added by Chandoo]
- Fetching Stock Quotes using Research Pane
- Stock Portfolio Tracker using Google Docs
- QueryTable Object Model & Properties
- Using QueryTable to Generate Dynamic Reports
- Yahoo Finance API Documentation & Example
Excel Hero is dedicated to expanding your notion of what is possible in MS Excel and to inspiring you to become an Excel Hero at your workplace. It has many articles and sample workbooks on advanced Excel development and advanced Excel charting.
132 Responses to “Excel Tables Tutorial & 13 Tips for making you a Data Guru”
Chandoo, I have only been using data tables for a few weeks & have discovered that they can be used to have charts dynamically expand to take in new data.
Simply set up the chart data in a block with appropriate headings (headings must be text, not formula).
Convert it into a table, select the whole table and insert a chart.
Format the chart as required.
When new data is added the the table and chart will expand to show the data.
The only problem I have found is that the last column of data must show a number when inserting the chart- a blank, #N/A and perhaps text will cause wierd changes in the chart. However this only occurs when first inserting the chart once set up it accomodtes blanks & #N/A etc. This is easily over by adding a temporary number when required on setting up the chart then replacing it with the correct formula when the chart is complete.
This seems to be a lot easier than using offset formula for the series .
Nice post. I've been using Pivot Tables for years but haven't used the "table" button until now.
This is great! Never used data tables before but it helps a lot. One question though, if I try drag your SUMIF formula across a row, different columns are selected in the formula. Normally you can make the columns static in a formula by using "$" - any suggestions on doing the same here?
Convert the data to range. Enter your SUMIF formula, then convert it back to a table.
This doesn't work....well, it only worked one way when I tried it. I had the data as a table and had created some SUMIFS formulae on another sheet. I coverted the data table to range and the formulae all changed to use range references. But when I changed the data back to table, the formulae stayed the same.
Creating absolute structured references is a bit awkward but not difficult.
Some Column as a relative reference
=SUM(Table1[Some Column])
Some Column as an absolute reference -- repeat the column name
=SUM(Table1[[Some Column]:[Some Column]])
The Some Column cell of the current row as absolute -- repeat the column name -- with the Other Column cell of that same row as relative
=SUM(Table1[@[Some Column]:[Some Column]],[@Other Column])
The range of Some Column to Other Column as entirely relative -- repeat the table name
=SUM(Table1[Some Column]:Table1[Other Column])
Nice post. Nice to see such useful options in 2007.
hi Chandoo,
How do you "record" the screen captures of the screens that you need to show into the animated gifs? Do you use a special software for it?
Use the snipping tool on the start menu.
You're posting 4 years late... and I'm posting 5 years late.... 🙂
I use Camtasia to record and produce these animated gifs. You can get a trial version here: techsmith.com
Thank you. This is truly a gem that will save hours and hours of time.
Nice post! However, in a basic form this functionality already existed in Excel 2003 as a 'List' (ctrl-L). So it not needed to convert the table back to a normal range for excel 2003 users.
Chandoo,
nice post. Seems to be a useful feature. Does data tables exist in Excel2003 as well?
@Peter H: Very cool tip about the charts and data tables.
@Dan: Tables are very useful and simple. Pivot can be very powerful for data analysis, but tables are good for maintaining databases.
@Michelle: The sumif formula in the article is written outside the table in a cell. If you write formulas in and copy (ctrl+c) and paste them, then the references are not changed. But if you drag the cell (thus auto-fill), then the cell references to table columns are changing. Not sure why excel would behave like this.
Also, inside the table, you can use [#this row] operator to calculate values for that row alone.
@Frederick: I use camtasia studio to record the screen. It is a nice software. You can test it from techsmith website.
@Doug: You are welcome 🙂
@Robert: My mistake, I meant version earlier than excel 2003.
@Michael: yeah, they are called as Lists. Press, Ctrl+L to create one.
[...] since I have learned the tables feature in Excel 2007, I have fallen in love with that. They are so awesome and so user [...]
i kept saying OMG WOW THATS AMAZING over and over.
i'm quite new with excel so your blog helps a lot and this post, is truly great. thank you!
[...] Microsoft Excel Table Tips and Tricks – Learn Data Tables and Become a Data God | Pointy Haire...By chandoo.org October 16, 2009- ???????Excel???????????????……?X?…… [...]
[...] Microsoft Excel Table Tips and Tricks – Learn Data Tables and Become a Data God | Pointy Haire...By chandoo.org October 16, 2009- ???????Excel???????????????……?X?…… [...]
[...] Microsoft Excel Table Tips and Tricks – Learn Data Tables and Become a Data God | Pointy Haire...By chandoo.org October 16, 2009 [...]
4. Bye, bye cell references, welcome structured references
it does not beat absolute reference to cell i.e $A$1, because if named range is used, it will not copy correctly if u use cell dragging horizontally... it will move to the next name range
This is a huge negative for "structured" references. Excel created a nifty tool that requires me to accept a significant loss of functionality if I decide to use it. It also prohibits dragging cell references to change formulae, so if I drag to fill, I have to click into the cell to manually edit the reference cells to what they should be instead of being able to drag the cell reference box back to the correct column. A small step forward and a giant step back. Or, more succinctly, Microsoft - 'nuf said.
Thanks for the information. Disappointing answer but helps solve my problem.
Chandoo,
I have a question on this. In my sheet I have the "Total Row" added. In that I take an average of a column values and I need to reference this final Average value elsewhere. How Can I reference this specific "totalled average cell" such that when new rows are added the same cell is taken?
As of now, since the total is at the bottom of the table, when a new row is added the cell id of this "total average" row keeps changing. I tried to move it to the top of the table to keep it constant but I couldn't.
thank you
sb
@SB.. you can use the [#totals] tag in the structured reference to total row else where like this:
=Table1[[#Totals],[Column1]] This will work even when you add more rows to the table.
Hi Chandoo,
when i use this code it always shows a #Ref! error.Can you please help me to fix it?
Thanks!
Siddhant
Thanks @chandoo.
[...] will save precious amount of time when you are busy modeling. (100s of tips on keyboard shortcuts, excel tables, formulas, charting & [...]
[...] Select your list of products (or invoices or cats) and make it in to a table. (here is a helpful tutorial on excel tables). [...]
[...] Simplifying data analysis with Excel Tables [...]
Dear Chandoo (Excel Guru)
Thank you very much for sharing such useful tips... now I feel more confident in analyzing a data with Excel....
Regards
Chandoo,
Where referecing table columns as range input to formulas such as sumif(), is it possible to get Excel to treat the reference as static for "fill" purposes?
Cheers,
Steve
@DangerMouse.. you can use ctrl+c ctrl+v instead of drag fill technique to treat the table references as static.
How to make absolute reference on Table column?
I have Table1 and use such formula:
=SUMIFS(Table1[Pay],Table1[Month],G9)
I want to “freeze” columns “Table1[Pay]” and “Table1[Pay]” in formula (like $A:$A).
How to do it?
Looking at article like these make me sad 🙁 - still being laden with 2003 really goads when you see so many fantastic improvements just out of ones rech like this......
[...] Excel Tables, a newly introduced feature in Excel 2007 is a very powerful way to manage & work with tabular data. I really like tables feature and use it quite often. If you are new to tables, read up Introduction to Excel Tables. [...]
I want to learn some excel course from you.
If you have any training centre in Mumbai, Please let me know..
Excellent Work!
Thanks
Avinash Ahire
i want to learn some advance tally course from you
if you have a training center in mumbai any location, please let me know
Excellent work!
[...] 10 Tips on using Excel Tables [...]
Is there a better way than ctrl-c-ctrl-v to expand the ~ifs() formulae across a row while keeping the absolute references to the auto-named ranges in the data-table? I know this has been mentioned a few times in the comments but I'm hoping someone cleverer than me might re-look at this...
All I've got so far is transposing my new table I'm trying to create so I'm dragging down instead of across, which holds the named column references, but I would prefer a dragging across solution
This is my first post on your website Chandoo, but I've been reading for a couple of weeks. It's a fantastic site! Thanks for all your efforts
I love it, I love it, I love it. Don't know how many errors I run into because of bad cell references this should help mitigate this.
Ok REALLY stupid question, I created the table and I have been putting in formulas using the column names, how do I "without using my mouse" select the table name from the tool tip ( or drop down) that shows up. Currently I have to scroll my mouse down to the right column name and then click it add the bracket etc. This also applies for when I use formulas in general. Please help, I am trying to be "Mouse free" 🙂
I have been using Excel forever this is amazing.
Hi Chandoo,
I am using Excel 2007.
I am using an excel workbook to enter data for several different sites (each site has its own worksheet). I want to establish a summary worksheet showing the consolidated data accross all sites so as I enter data into the individual site worksheets the data is also replicated in the summary worksheet which is then sorted by site to show the consolidated view of all sites. Each of the site worksheets have the same data headings which will be replicated in the summary worksheet.
Can you please give me some advice as to how I can go about this?
I know how to use the basics of excel but know nothing about using databases like Access so I would prefer to continue to gather the information in excel if this can be done.
Hoping you can help me,
Jim Morley
[...] The Data Table function should not be confused with the Insert Table function. [...]
[...] Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural [...]
Chandoo,
I really missed you discount offer last week or so,That day i could not access the internet.Any chance of getting discount offer, i am really interested in joining your course.pl let me know.
Chandoo, thanks a lot for all your tips and tutorials. Your tutorials are really great and easy to follow!!!
Hi, Can i access Excel 2007 table via code - JavaScript?
WOW!
I've spent days organising a "table" of my own (without knowing it) writing formulas,creating helper columns and generally getting stuck and i just figured out that tables and pivot tables do all this in minutes. I dont know whether to feel downhearted or elated that i've learnt this! (all be it too late)
[...] Learn Conditional Formatting 3. Making Dashboards using Excel 4. Project Management with Excel 5. Working with Excel Tables Topics & Archives 1. Learn Excel - Topic-wise 2. Charting Tips, Tricks and Tutorials 3. Ask an [...]
Hi ,
I need a vba for excel training in delhi, kindly suggest regarding this..
How do I get the AutoFormat of Double-Bars in XL 2010? It was so easy in 2003, but I cannot find a way to do it, w/o VBA, in 2010! It can't be that hard.
In 2003, Format, AutoFormat, choose the List 2 style. It formatted rows in groups of twos, or pairs. Very handy in lots of cases.
Any ideas?
Thanks
[...] Learn more about Excel Tables. [...]
[...] How to Use Tables to Speed up Formatting of your Data [...]
I have become a fan of tables now. 🙂
Hi Chandoo,
I used structured reference but when I close and open again, they have all become cell references! Is there a way to fix this? I am using 2010. Thanks,
Prem
Even I have the same problem and worst is it when we add new rows the formulas now do not get changed as it is now having cell references 🙁
Regads,
Prasad DN
Make sure you're saving in the 2007 or 2010 format (instead of the 93/97 format).
Dear Sir,
i want tech the micro soft excel data table and function for opperating the micro soft excel. please give send the tip of micro soft excel.
with best regards and wishes,
Muhammad Bilal Yousaf
Dear
I went to some new learn work in excel
latest
best Regard
Farooq Asim
Absolute references:
As Chandoo says, these move if you use drop and drag. However, if you highlight the required cells, place the cursor in the cell to be copied and use Ctl Enter then the formula copies keeping the references "absolut"
Linda
Good work done.
[...] have set up this data in an Excel Table called as tblSales so that it is easier to write [...]
I've tried to name tables so that I can reference them in functions, etc... but I'm not having any luck getting the name to stick. I can click in the area to the left of the function button and type a new name for the table, but clicking outside of that causes the name to revert back to what it was (for instance, 386, or 3). Thoughts?
@Eric
Select any cell in the Table you want to rename
You should see a Table Tools, Design Ribbon
Select that Ribbon and on the Left there is a Table Name: dialog
Change the name and press Enter
[...] wrote a great post several years ago that explained the basics of Excel tables and provided a number of tips and tricks related to them. [...]
How to rename a Data Table name in Excel.
Sorry guys i got it.
i like you your website, but i quesestion i wil written to excel in accountant but evry time name party not incuding, so anything i find to party name is all ready this excel in ready name ,so you my question solve and reply to me my email id vimal_jariwala15@yahoo.ca
Nice Article
Hi,
How can one add zebra lines in table rows in Office 2010?
@Nitin K.
When you say Table I assume you have a Table setup using the Insert Table menu
If so goto the Table Tools, Design Tab
Select one of the Zebra styles or edit one to setup your own colors
If you don't have a Data Table, select your Data and goto Insert, table menu, then follow the instructions above
You may also want to have a read of: http://chandoo.org/wp/2011/12/08/formula-forensics-no-005/
I have learned so much in the last few weeks since joining in here. Well done.
Now I have a query that I need hlep with. The company I have started working for uses loads of data. Reports are exported to Excel then sorted etc etc...
The Key ref numbers are numeric, 12345.1 and 12345.10 etc etc. Excel will obviously sort the .10 above the .1 - Its an habbit of the reporting software to create a .1 and not .01 Is there anyway within Excel to block alter all the .1's to .01's? I'm talking thousands of them, not just a handful that could be formated as text manually.
[...] Use Excel Tables: Since Excel 2007, we can create tables from structured data and write formulas, create charts that refer to dynamic ranges with ease. Click here to know more about tables. [...]
[...] the dashboard on the Property Register converted to an Excel Table (a 2007+ feature that many are still unaware of) enables the use of Slicer selectors in Excel 2013 to quickly give a dashboard feel. It’s [...]
Great site, and your instructions are so easy to follow. Thanks so much!
On sheet 1, I have a table of names and demographic information for each.
On sheet 2, I am using the same table of names but different data across the columns.
When I add a New Name to the table on Sheet 1, I have to manually extend the table on Sheet 2, to expose the new name.
Is there a way to automatically expand the table on sheet 2, to show the new name added to the table on Sheet 1??
Thanks so much
Has anyone tried using named tables as data sources outside of Excel? For instance, referencing the table for a mail merge? If so, how have you got it to work? Thank you all!
Yes. I use tables in a mail merge. There's no trick (I can think of) to it different than using any other Excel data to mail merge. My own notes for mail merging mention that I open the Excel spreadsheet first and that I need to make sure that the active sheet is the sheet with the table I want to use on it. I do seem to have had problems if a different sheet is the active one.
There IS a trick to making sure your Excel formatting (like number formatting for $, etc.) comes through. But that is true whether or not you are using a table.
[...] of data in this dashboard is captured using Excel Tables. This has several [...]
I like to say, "If you're not using tables, you're doing it wrong." Or at least 90% of the time you should be using them!
I would like to say, "Tables are the best thing since sliced bread", but it then occurs to me that sliced bread is highly overrated. I can slice my own loaf much quicker than doing many of the things manually in Excel that tables make easier.
I LOVE tables! They have changed how quickly and awesomely I can analyze and present data.
Hi Chandoo, another awsome tutorial.
Can you expand the VBA so that i can leave some categories blank and the table will show ALL entries for that category? (eg all Customer Types or all Regions)?
Thanx, Gijs.
[...] ourSales[month] refers to the month column in the ourSales table. Works only in Excel 2007 or above. Know more about Excel Tables. [...]
So I created a data table and wrote forumlas using the data table and column names. It all calculated out beautifully. HOWEVER, now that I want to add lines to my data table, none of the formulas are seeing the added data. I've gone in to expand the range of the table, etc. But still no luck. So stinkin' frustrated :/ Any advice??
[...] best way to create a tracker is to use Excel tables. Set up one with 4 columns – Employee name, vacation type, start date & end date, like [...]
Hi,
I need to convert some data into columns can anyone help me?
[...] Visit link: Microsoft Excel Table Tips and Tricks – Learn Data Tables and … [...]
Dear Chandoo,
I have done much research and your Excel offering is certainly highly rated and I would like to subscribe. I am using Excel 2011 for Mac. Will the course content be compatible? I assume it will but perhaps you have been made aware of some idiosyncrasies?
I look forward to hear from you.
Louis
P.S. Are you aware that your reference to "GOD" will be offensive to many in the Biblical-based faith community (of which I am a practising one)? I suspect you are using it in a different context but it does detract from your brilliance.
[...] how your data for the projects/entities being tracked laid out. We will use the Excel data tables [structured references] to help us [...]
[...] how your data for the projects/entities being tracked laid out. We will use the Excel data tables [structured references] to help us [...]
[...] created an agenda table with below [...]
[...] Introduction to Tables in Excel [...]
[...] select any cell in range and press CTRL+T. Specify a name for your table from design tab. Read introduction to Excel tables to understand [...]
[...] Introduction to Tables, Introduction to structural [...]
Dear Chandoo,
Just one word "Amazing" I like this site 😉
Thanks for sharing all of these
Good Luck
[...] wrote a great post several years ago that explained the basics of Excel tables and provided a number of tips and tricks related to them. [...]
hi chandoo,
I m unable to get the point 6. plz help.
@Arindam
The Menu's Chandoo shows as Pt 6 vary from Excel version to Version
They are accessible by
Click in a PT anywhere
A menu Bar will appear with Pivot table Tools
It may have a Options Tab and a Design Tab
Select the Design Tab
You should see the options shown in Pt 6 here
[…] am sure you all are aware of a feature called as Excel Tables OR Structured References in Excel. Excel Tables is (in my opinion) the best way to store your raw […]
Hi, in point number 6 of http://chandoo.org/wp/2009/09/10/data-tables/
Total your Tables without writing one formula
The ability to summarize data with pivot tables is extended to excel tables as well. You can add total row to your table with just a click.
What more, you can easily change the summary type from “sum” to say “average”.
How do you change the summary type from sum to say average?
@Mona
These are pivot tables
So simply right click on the Total line and change the Calculation type from Sum to Average
Table Text Filtering - is it possible to have column 1 ( in this case named "WO") in the table filter automatically based on the date referenced in another worksheet cell within the same workbook?
Thank you 🙂
Excellent presentation...it would surely interest the most excel fearing people I knw
[…] LinkExcel data table is a series of rows and columns with related data that is managed independently. … Excel 2007 has some great pre-defined table formatting opt…chandoo.org […]
how can add last paid amount in vacation track.
[…] Tables: to keep the products & services data […]
Tried removing the filters but it was difficult. Any reason for this?
Valuable information. Fortunate me I discovered your website unintentionally,
and I'm surprised why this coincidence did not came about earlier!
I bookmarked it.
Going to drop this comment here, since I could not really find a suitable post otherwise. I am not sure if I am overthinking this per usual and missing a very simple remedy - but nonetheless I find myself at a dead end.
Anyone know if it is possible, and if so, how to have multiple headers in a table? I know it sounds counterintuitive but I would like a way to pivot a bulk set of data (set in an Excel Data Table) but have the option to choose 1 of 2 Options when choosing my data header for the Pivot.
Let me try and explain a scenario. So each row has an Account, Part #, a specific week ending date units sold, and a specific week ending date unit dollars. For each account, we sell maybe 5-7 products. So Retailer A has 5 unique rows, Retailer B has 7 unique rows, yadda yadda.
I have 4 tabs, very similar of one another, the only variable is the year (specified by the tab) - or - table name ... and the removal and inclusion of obsolete and new products.
Our Sales weeks are Sunday-Saturday. So, I have 2 column headers that read 11/15 Units and 11/15 Dollars; for this past week.
However, If I wanted to compare this past week to last year or 2012, using Power Pivots, I would not be able to - since "week endings" do not lineup. That is to say in 2013 Week 46 (this past week-end week) correlates to 11/16.
So I would not be able to do a 1:1 comparison easily within a Pivot due to the variation in dates. So logically, I would need some sort of common denominator. I would imagine this would be Week "X." So essentially two separate (or hierarchy?) column headers. I would either need "Week 46" act as a parent - above both "11/15 Units" and "11/15 Dollars" ... or alternatively two separate "co-headers" right above them: "Week 46 Units" and "Week 46 Dollars"
This way, I could compare multiple Week Ending sales data, across multiple tables, using a Power Pivots, based on a common property.
Does anything like this exist? I feel like I would have come across it by now if it does.
And to note, I cannot simply just change the way we handle data to reflect a Week # and omit the XX/XX date methodology. I wish I could, cause that would be abundantly easier.
@Mark
Can you post the question at the Forums and include a sample file?
http://chandoo.org/forum/
Not sure how 11/15 or 11/12 can reference a week as it refers to a month!
If you use the function WEEKNUM(date) with date being the date of the Saturday or Sunday, it will refer to the same week whatever year you are looking at.
So your "co-headers" could be =WEEKNUM(date)&" Units and =WEEKNUM(date)&" Dollars".
[…] Excel Data Tables – An Excel table consists of a series of rows and columns with related data that can be managed independently. Most work in Excel happens inside a table. A table allows users to easily create formulas to make calculations related to one or more rows and columns. Some examples of formulas include the sum of a column, an average, a maximum or a minimum value. For more on tables, visit this tutorial. […]
very useful informations. billion thanks.
Why using tablet format on a file makes it so big?
I had to change all my formulas to references because the size of my file was to big to work.
Any advice ?
Hello Brother,
Your website is awesome. Thank you for all your work. I cannot be as your ordain in your 3rd paragraph as I believe in the One, True, Almighty God. But I can try to be the best I can.
Thank you sir for all your work and sharing.
Chandoo,
As usual, this article was very well done and I thank you for sharing. I'm posting this comment about some trouble I had with Tables, just as a warning for others who might stumble into it, too.
So readers know how much time this cost me, I'll admit this issue took me down a 5 day journey, including an Office 2010 Repair, then an Office Uninstall both of which threw similar errors, so I found an MS Fixit Tool that seemed to describe my situation and ran that but it, too, threw a similar error, and then found a 3rd party utility that claimed to clean up the residue of Office Uninstalls that went wrong, but, it, too, threw the same similar error, but it did at least remove Office from Programs and Features, so I proceeded to re-install Office but that re-install also erred out with the same similar error. After scouring the Internet for this error as well as the original issue (below) that made me think something was wrong (and unbeknownst to me that the cause was a Table issue), I concluded that I must have a virus on the system (despite being current on all my Updates and running 24/7 Virus and MBAM anti-malware) and proceeded to do a clean re-install of Windows 7, Office 2010 (as well as all associated Windows Updates, all my browser Add-ons and preferences, and all my programs, etc. but I took the time to also do something I should have done a long time ago and set up a cloned image of my hard drive when done so if I have any further issues, a re-install of Windows and everything else will be a 20 minute walk in the park instead of the nightmare this has been.
Anyway, back to the Table issue: While working on a large project that has some 10 Tables in it, one of which had Table Headers that were formula driven (these were incremental Date Formulas), that somehow were subsequently getting Pasted as Values. I was building these Tables with Code, and therefore wasn't seeing the warning that Converting a Range to a Table would convert Formulas to Values (and since I hadn't ever done this before, didn't know that message presented if done manually). In my subsequent endeavors to go back to earlier versions where the formulas were still dynamic, I found I could Copy the formulas from the older files, but no matter what I did, I couldn't Paste them as Formulas. This capability is an essential part of Excel, of course, but it wasn't until I had re-installed Windows, Office (et al), and found out that the problem was STILL there, that I finally figured out that it couldn't have been a virus, and finally moved the file to another PC, where I confirmed that the problem was in THE FILE. So, amongst many lessons I learned in this journey was that if you have to have Formulas in your Table Headers, then build into your code (or manual process) a routine that places a copy of your header row under your 'nominal' Header Row (and hide it so you don't have duplicates), but reference the hidden row as your Table Headers.
Hope this helps someone as there is a LOT of other forum posts out there that have to do with Excel Copy/Paste issues but I did not find this as a cause).
In excel Table a copy paste is not working the way it should. Within a Table I am trying to copy a cell's content into another cell it copies totally different value. Need assistance on how to deal with it. Thanks.
I found that I can not paste values in cells within a Data Table- whether they are copied from within the table or outside it (although the cells in the Data Table have NO foumulas!).
I hope someone has an answer to this (i've been searching for a long time).
Hi Chandoo
I'm trying to get my head around Excel Tables and Structured Referencing
and struggling to see how something like the following could be accomplished using this methodology: IF "A1" AND "B1" ARE BLANK AND "C1" AND "D1" ARE NOT BLANK AND "E:E,E1>1", "DUPLICATE","UNIQUE". is it possible?
When the header positions change my formula take the original header positions' data. How do I address the issue?
pls l need you to teach how to use excel from the beginning l have lost touch about some of the functions
IF I HAVE DATE AND PLACE IN ONE COLUMN HOW CAN I MOVE IT INTI TWO COLUMNS
27/05/1956 OVL-DELHI
10/09/1956 AHMEDABAD
30/01/1961 DELHI
Hi. Thanks for having this amazing web site. I wonder if excel tables have the capability to add more than one total row.
Thanks,
[…] The vertices of the regular octahedron with its centre at origin will be as below. let us enter these in excel worksheet as Dynamic tables […]
Once a table is created how is it updated? Say that the table needs to be updated with changes once a month besides VBA and doing it manually how is data refreshed in a table? Is there an Excel formula or module to make changes?
This is an amazing post. Have been using Excel for about 8 years now but never came across this.
Thanks a ton, Chandoo 🙂
Tables make it easy to create dynamic charts, but how can you use the filtered data to create a dyanamic title - eg checking that there is a unique value in one column and then using that?
[…] Excel Data Tables – An Excel table consists of a series of rows and columns with related data that can be managed independently. Most work in Excel happens inside a table. A table allows users to easily create formulas to make calculations related to one or more rows and columns. Some examples of formulas include the sum of a column, an average, a maximum or a minimum value. For more on tables, visit this tutorial. […]
Gr8 article; something expected of you.
thanks for very nice and informative post.
[…] next article is a review of another important feature: Data Tables. If you deal with data in Excel, you have probably come across this feature or you should. Tables […]
Sir,
I am using excell in iPad.
And I would like to know if the shortcut tips you have explained are usable in iPad keyboard also.
I am a beginner in excell usage and hence this clarification.
hi
despite my efforts I could not resolve
Sheets("rwshow2").Range("TC[#ALL]").advancefilter Action: xlFilterCopy , criteriarange:=Sheets("rwshow2").Range("S1:U2"), copytorange:=Sheets("fltr").Range("B10"), unique:=True
please help what I am doing incorectly
thanks and regards
Arun