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.

Shut down iPad to fix synching

Question:  My PC stopped updating my iPad. Do you know if there is a setting I should check to correct this?

Answer: I don’t have an iPad, but before I had a chance to research the issue, he responded back and said he simply rebooted (shut off and on) his iPad and it corrected the syncing issue. I know I have had to shut down my iPhone occasionally if it didn’t seem to be acting correctly. When in doubt, shut it down and restart. Works a lot with computer desktops too!