by Allen Wyatt
(last updated March 9, 2019)
Steve notes that some Excel functions can access data in closed workbooks, and some cannot. Every several months Steve needs to pull data from closed workbooks, and as a result he spends considerable time and effort performing trial and error testing until he comes up with a method that works. Since he doesn't do this very often, Steve is continually rediscovering the same limitations of some functions regarding referencing data in closed workbooks. He wonders if there is a definitive list of those functions that will (or won't) allow the referencing of data in closed workbooks.
As far as I can tell, Steve, there is no such definitive list. That is, perhaps, because there are so many different functions that one can use in Excel—well over 300 of them in the latest versions of the program. (And, unfortunately, Microsoft hasn't made any such list available that I can locate.)
That being said, testing has shown that the following functions cannot be used in the way you describe—they won't retrieve information from a closed workbook.
AVERAGEIF, AVERAGEIFS, CELL, COUNTBLANK, COUNTIF, COUNTIFS, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DSUM, GETPIVOTDATA, INDIRECT, OFFSET, SUMIF, SUMIFS
This list is not exhaustive, as not all of Excel's functions have been tested. (The above list was generated after testing about half of Excel's functions.) The flip side of the above list is that the following functions have been tested and will work just fine in external references to closed workbooks:
ABS, ADDRESS, AND, AREAS, ASC, AVERAGE, AVERAGEA, CEILING, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COLUMNS, COMBIN, CONCATENATE, COUNT, COUNTA, DATE, DATEVALUE, DAY, DAYS360, DB, DDB, DOLLAR, EDATE, EOMONTH, ERROR.TYPE, EVEN, EXACT, EXP, FACT, FALSE, FIND, FIXED, FLOOR, FV, HLOOKUP, HOUR, HYPERLINK, IF, INDEX, INFO, INT, INTRATE, IRR, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MEDIAN, MID, MIN, MINA, MINUTE, MOD, MONTH, MROUND, N, NA, NETWORKDAYS, NOT, NOW, NPER, NPV, ODD, OR, PERCENTILE, PERCENTRANK, PERMUT, PI, PMT, POWER, PRODUCT, PROPER, PV, QUOTIENT, RADIANS, RAND, RANDBETWEEN, RANK, RATE, REPLACE, REPT, RIGHT, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, ROW, ROWS, SEARCH, SECOND, SIGN, SLN, SMALL, SQRT, SUBSTITUTE, SUBTOTAL, SUM, SUMPRODUCT, SYD, T, TEXT, TIME, TIMEVALUE, TODAY, TRANSPOSE, TRIM, TRUE, TRUNC, TYPE, UPPER, VALUE, VLOOKUP, WEEKDAY, WEEKNUM, WORKDAY, YEAR, YEARFRAC
Again, these lists are not exhaustive, even though they probably represent the most often used functions in Excel. You could use these lists as a starting point for developing your own lists, adding to them as you determine which ones you need to use in your workbooks.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6035) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...Discover More
Hate to take your hands off the keyboard while working on a worksheet? Here's one way to activate the Formula Bar without ...Discover More
The primary way you signify that you are entering a formula is to start a cell entry with an equal sign. The equal sign ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.