Appendix D – Optimizing Your PC for Excel
Defragment Your Hard Drive (N/A if you are using Windows 10. Windows 10 automatically defragments the drive)
You should defragment your PC every few months. If you’re a heavy PC user, consider defragging once a month.
You should defragment your PC every few months. If you’re a heavy PC user, consider defragging once a month.
To defragment your hard drive, select the Windows Start button (FIGURE 1) in the lower-left corner of your monitor screen. Select the search icon (magnifying glass icon) which is usually located next to the start button. When the search box appears, enter “defragment” into it. On the pop-up menu that appears, select “Defragment and Optimize Drives” choice that appears (FIGURE 2).
 Windows Start Button
FIGURE 1
|
 Search Box
FIGURE 2
|
|
Select the Optimize button (FIGURE 3). Depending on the amount of fragmentation and hard drive size, defragmenting your hard drive could take from 20 minutes to 2-3 hours.
NOTE: If you are a Windows 10 user, the hard drive is automatically defragmented for you so defragmentation is less of an issue. |
 FIGURE 3
|
PC Processors and Hyperthreading
Most PCs within the last several years have multiple processors. What that means for you in terms of Excel performance, one processor can be number crunching one thread while another processor can be number crunching another thread, both at the same time.
A number of PCs also have a feature known as hyperthreading, which means each processor can number crunch multiple threads at the same time.This has a multiplying effect on a multiple processor PC. For example, in the example shown below in FIGURE 4 with a PC using 2 processors that has hyperthreading capability, it can perform four operations at the same time (2 processors x 2 threads per processor = 4 threads). This effect would be even more advantageous if your PC has more than 2 processors.
Most PCs within the last several years have multiple processors. What that means for you in terms of Excel performance, one processor can be number crunching one thread while another processor can be number crunching another thread, both at the same time.
A number of PCs also have a feature known as hyperthreading, which means each processor can number crunch multiple threads at the same time.This has a multiplying effect on a multiple processor PC. For example, in the example shown below in FIGURE 4 with a PC using 2 processors that has hyperthreading capability, it can perform four operations at the same time (2 processors x 2 threads per processor = 4 threads). This effect would be even more advantageous if your PC has more than 2 processors.
FIGURE 4
|
Check Excel Options for Multi-threading Operation
With Excel open, select the File tab on the Ribbon which will take you to the Backstage view. Select the Options category on the left. That will open the Excel Options dialog box. Select the Advanced category and then scroll down to the Formulas section, shown in FIGURE 5. Ensure the box is checked for “Enable multi-threaded calculation” and for “Number of calculation threads”, ensure the choice “Use all processors on this computer” is selected. Do not use the “Manual” choice. |
 FIGURE 5
|
Add RAM (memory) for Improved Performance
Excel uses a lot of RAM (memory). Compared to other applications, Excel is a memory hog. Excel loads the entire file into memory. If you are working with a large pivot table or are working with several pivot tables at once, you will be consuming a lot of memory. If you’re using your own PC, adding RAM, even an extra 4GB, will help performance. If you can add more than 4GB, do so in accordance with your budget. Check your PC documentation to ensure it will hold all of the added RAM.
Excel uses a lot of RAM (memory). Compared to other applications, Excel is a memory hog. Excel loads the entire file into memory. If you are working with a large pivot table or are working with several pivot tables at once, you will be consuming a lot of memory. If you’re using your own PC, adding RAM, even an extra 4GB, will help performance. If you can add more than 4GB, do so in accordance with your budget. Check your PC documentation to ensure it will hold all of the added RAM.
Share the Pivot Table Cache for Improved Performance
The pivot table cache consumes additional memory. If possible, share the pivot table cache amongst your pivot tables you are working with, which will minimize the memory drain.
Close Unneeded Applications
One final suggestion: close all unneeded applications, which will free up more RAM for Excel.