Slow Excel files

Question: Why is my Excel file so slow when opening?

Answer: I hear this problem occasionally and the typical problem is that there are many extra columns or rows included in the data range. Sometimes this is caused by typing something (even pressing the spacebar) in a cell far away from the data or from formatting cells outside the data range. The easiest way to tell is by pressing Ctrl End, which will take you to the last cell of the range. If this is the case, you can try several things

  1. Delete the extra rows and columns outside the range of data
  2. Clear any formatting outside the range of data
  3. Copy the range of data for each worksheet to a new file

Save the file with a different name (File > Save as) and close the file before reopening. You will see the size of the file has reduced considerably and will be much faster in opening.

Grouping by days of the week in a pivot table

Question: I am trying to create a report from Excel data that was exported from a web application that has date fields and values in which I can get an average of values for the various days of the week. How can I do that?

Answer:  If your data contains a date field, you can display the day of the week using one of the date formatting styles, but a pivot table does not allow you to group by days of the week. However, you can create a new column for your your data which creates a text field from the date field  -  i.e. =text(A2, “dddd”). Then copy that formula down the column (shortcut to copy is  double clicking on the dragging handle) and be sure to add a column heading. Then save your file before creating a pivot table. You can now add that field in the pivot table and you can change value field settings (in the values section) from sum to average.

Learn more about pivot tables and other time-saving Excel features by attending Kim’s Favorite Excel Tips session on 3/6/14 at 9 -10 a.m. in 2020 Bennett Hall. No registration is required.

View two Excel files side by side

Question: How can I have two Excel files open side by side? When I open another file it places that file in view and my other file disappears behind. I want to have two different instances of Excel open.

Answer: One way is to open both files after having Excel open and select View > Arrange All and select side-by-side. You can also open one or both files from Windows explorer or My Computer, or open one file by double clicking on it and then open Excel from the Start button and find a file from Excel browse window. This allows two instances of Excel running which you can manually arrange Windows or use Windows shortcuts (Windows Button plus right arrow to lock on right side of screen and Windows Button plus left arrow to lock on left side of screen)