Reporting Worksheet 2013-2021: Fill & Download for Free

Historically, Excel was limited in the amount of data it could store and analyze, inability to handle simultaneous access by two or more people, and inability to deal with relational data tables. Remember though that you are attacking a moving target with a question on Excel’s main limitations and weaknesses. With each succeeding version, Microsoft has been reducing or eliminating those limitations and weaknesses.Amount of data to store and analyzeExcel 2007 brought the big grid, with 16,384 columns and 1,048,476 rows. For most people, that was plenty. But database advocates continually pointed out that those were still limits, and that Excel got sluggish when you filled its worksheets up with data.Excel 2010 brought the PowerPivot add-in, which allowed Excel to analyze a hundred million rows of data. The allied Power Query add-in let you bring in and condition data from a wide variety of sources. Excel 2013 improved these add-ins, and Excel 2016 made their functionality part of the basic installation.Excel 2013 and 2016 became Large Address Aware in 2016, so 32-bit installs could use up to 4 GB of RAM, a significant improvement over the 2 GB limit for Excel 2007. Excel 2010 came in both 32 and 64-bit versions, and the 64-bit version let you use as much RAM as you could afford (up to 8 TB). While Microsoft initially recommended the 32-bit version for most people because of compatibility with certain VBA add-ins, most of them have since become 64-bit compatible. I switched over to 64-bit when I installed Excel 2016, and haven’t ever regretted that decision.Some people complain that Excel gets slow when you stuff a worksheet full of data. If the worksheet contains only data, you should see consistently snappy response. But if the worksheet rows are packed with formulas, you may start to notice some sluggishness for recalc each time you hit Enter. When that becomes a problem, change to Manual calculation mode and hit F9 to force a recalculation only when you think one is required.People also complain that Excel can’t do lookups very quickly when your lookup table has hundreds of thousands of rows. They say that databases are much more efficient because their data tables are indexed. Poppycock! Sort your Excel data by the lookup column and then use the enormously faster binary lookup form of VLOOKUP or INDEX & MATCH. You should see a 100X to 1000X improvement in speed using a formula like:=IF(VLOOKUP(A2,Sheet2!A:A,1)=A2,VLOOKUP(A2,Sheet1!A:Z,4),"Not found") Microsoft addressed slow lookup in 2018 by indexing the lookup tables used by functions like VLOOKUP, MATCH and XLOOKUP. This is the same technique used in databases, and allows the lookup formula to go directly to the right row. When you deal with million row lookup tables, the formulas recalculate over a thousand times faster—and no need to sort the data. This improvement applies to subscription copies of Excel and the upcoming Office 2021.Simultaneous accessMicrosoft’s early attempt at allowing simultaneous access to an Excel file (Shared Workbook) was somewhat buggy, and frequently resulted in file corruption. Excel experts everywhere recommended against its use for this reason, and suggested an Access database if simultaneous use was really a requirement.Excel Online allows you to work with an Excel file stored on OneDrive or Sharepoint using a web browser. One of the early features implemented in this was the ability for two or more people to work with a file simultaneously. This was implemented using locks on the parts of the file that each person was working on, so one person could make changes while the others could view.In 2017, Microsoft added automatic saves to files stored on OneDrive. Even if two people are working on the file at the same time, neither one needs to worry about losing their work.Relational data tablesExcel handled flat databases early on. But anytime you had some data in one table, and other data in a different table, there was difficulty. As a result, there are numerous forum questions for how to join tables together, merge their data, identify duplicates/unique entries, resolve conflicts, or populate a column using data from the other table. Each of those problems could be solved, but in many cases you needed to have advanced Excel skills to do it.Microsoft’s Power Query add-in in 2010 began to address these issues. You could write queries using icons on a toolbar that even included features like pivoting and unpivoting the raw data. You could also link two tables together by specifying the fields in each table that would map a relationship. Once the relationship was mapped, getting a property in one table that was specified by a different one became easy. You could edit those queries and mapping relationships using the M language.The Power Query features have been made an integral part of Excel 2016 in the Get & Transform Data group on the Data tab of the ribbon. They are shockingly powerful, and I regret having resisted learning about them for so long.Excel versus database appsExcel is not a database, but people frequently use if for database purposes. I find it insightful to wonder why that should be the case.Excel is available to everybody using Office. Database applications have always been restricted, with only a handful of people getting an app like Access. More powerful database software like SQL requires both IT Department permission and participation during installation.Access to corporate database software is restricted by the IT Department because of the potential damage that can be done. They protect corporate database integrity by exporting files in Excel or .pdf format.Database software is hard to use for anything but data entry, but everybody already knows how to use Excel. That’s why there are jobs called Database Architect, Database Developer and Database Analyst.Do you need a new report or more insightful report format? Get in line at the IT Department, and don’t plan on getting anything soon (or ever) unless you have strong backing from top management.Given the preceding database limitations, is it any surprise that people respond by using Excel as a database?