Excel Data Analysis Pro Tips is a collection tutorials and pro tips for data analysis, data engineering using Microsoft Excel and Power Query, DAX and Python
In our world of data analytics and big data, there is no getting away from our beloved data crunching and data wrangling tool: Excel. However, with large data sets, Excel starts to huff and puff. These 10 tips will help crunch your data for the last minute meeting like a pro.
Tip #1 – Manual Recalculation
If you are using a lot of data with formulas, transformation, grouping and other visual tools like splitting views, you can use a lot of memory with Automatic Calculations as Excel to not only recalculate your data; it also needs to recalculate the screen layout. With this option enabled, you are essentially running the Excel calculation engine every time you enter or exit a cell which can impact performance and even either get a "resources unavailable ..." error or Excel may even crash and restart.
To improve Excel workbook performance, select the Manual Calculation option. This option will greatly improve performance as you will choose when you need to recalculate your data.
To enable this option, open the Options page (Excel 2010, 2013, 2016 and later) under the File tab, or from the Microsoft button in Excel 2007 (it is located at the bottom of the page).Next, click on Formulas and locate "Calculation options."Select "Manual" with or Without "Recalculate workbook before saving" option.
Oops! This image does not follow our content guidelines. To continue publishing, please remove it or upload a different image.
Manual Recalculation
Manually recalculating is easy and you have several options:Press F9 will manually recalculate all the formulas in all your open workbooks and all depended formulas that have changed.To only recalculate formulas that have changed since last calculation, use the Shift+F9 option. Best performance as you only calculate the immediate formulas that you are working on and not the whole workbook.Ctrl+Alt+F9 is another option. Using this key combination, you will recalculate all formulas and their dependencies regardless if they have changed or not. This can be expensive on memory and performance.Another option is to check the dependencies between formulas and recalculate them regardless if they have changed or not. To use this option, use the Ctrl+Shift+Alt+F9 key combination.
Oops! This image does not follow our content guidelines. To continue publishing, please remove it or upload a different image.
Tip #2 – Maximum Iteration and Precision
If you opt or need to keep the Automatic Calculations on, you can limit the number of times the Excel calculation engine will execute the formulas by setting the "Maximum Iterations" to a number you think if sufficient for your immediate needs.
