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
- Delete the extra rows and columns outside the range of data
- Clear any formatting outside the range of data
- 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.
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.
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)
Question: I have a spreadsheet which has 14 point font, but when it prints out the text is much smaller? Also, I am trying to change page headings. I need to change the heading on subsequent pages, but not the first. Someone else set up this worksheet. How can I do this?
Answer: If you click on the Page Layout tab, you will probably find that the worksheet is scaled less than 100%. The width or height may say automatic or 1 page, meaning that the originator of this file wanted to have the spreadsheet print on one page only. If you change it to 100%, the font will stay at 14 point. However, your worksheet may print over more than one page.
You’ll also notice if you click the slanted arrow in the lower right of the Page Setup grouping, you’ll see more options. Clicking on the Sheet tab will indicate if a row is repeated at the top. If so, delete the item and this will remove the rows to be repeated at the top of each page, and you can add headings manually where you want them.
Question: I have an Excel spreadsheet with data I would like to merge to Outlook. I need to include a survey as an attachment with their specified information and have them return it to me. I only want it to go to names that are highlighted in my spreadsheet. There will be over 300 names.
Answer: Oh, I love these challenges. First, in order to be able to identify the names to select from Excel, there has to be something consistent about how they are entered, such as a field with specific criteria. However you are not able to filter based on highlight or color. So in looking at the data, the records had a 1 in one column or another column. This is going to work!! If you don’t have any consistent criteria, you may want to add a field/column with an X in the cells you wish to send (or not send to).
Next, we did a “test” run with sample names/email addresses that we can check to verify it works. You want to do this because it is rather embarrassing if something doesn’t work, and you accidentally send 300 incorrect emails out.
Then we went through the mail merge steps. If you have your document already created, open that file. Then to create the merge, from the ribbon select Mailings > Start Mail Merge > Step by Step Mail Merge Wizard. A task pane appears on the right of your Word screen. I prefer the wizard because it takes you step by step so that you don’t forget anything. Follow the steps on each screen and click next:
- First, select “Email” as your document type and Next, select “Use the current document.”
- Next, select recipients by browsing for your Excel spreadsheet as your data source (You should close the Excel file if it is open). You may edit the names individually, if there are just a few, or click Filter to identify the fields and values you are looking for and use the and/or options appropriately by adding additional rows to your filter criteria.
- Next, add the fields from the excel file you want to include in your email/letter to personalize it by clicking on More Items (or Insert Merge Field from the Ribbon).
- Next, preview the emails (use the arrows next to recipient to verify the information looks correct). Again, initially I would test just a couple of sample names from the file that you can verify they were sent/received.
- Once you feel confident the information is merged correctly, you can click Next, Complete the Merge and click Electronic Mail.
- Add the field with the email address to the To section, include a subject line, and select Attachment from Mail Format (You may find people read emails more than they would open attachments and if you’re sending out 300, attachments will take more space, adding to your email size). In that case, I would suggest using HTML, as it seems easier and faster. Once you click OK, the emails will go and you cannot stop the process.
- Check your sent folder to see if the mail was sent. Then check your test recipient to see if it was received. If you have about 300 recipients in your file, then you have 300 messages in your sent folder.
- Rather than deleting all of these emails immediately from your Sent folder, you may want to use Adobe Acrobat to convert these messages to a PDF file and save onto your computer if you need to refer to these later as proof they were sent. Then you can delete those messages from the sent folder to free up space in your mail database.
If you have additional questions on Merges to Outlook, please feel free to contact me at email@example.com.
Question: I have started using Office 2013 and noticed many differences between Office 2013 and Office 2010, particularly with Outlook and Calendar. Are you aware of any summaries describing the differences?
Answer: The differences don’t seem to be extensive, but there are some changes. In particular, you now have the ability to save directly to the cloud (although Office 365 has limited features compared to 2013). There are some really nice new features with Excel such as flash fill and quick analysis too. This handout provides some excellent video tutorials on the new features and “how tos” of Office 2013. The videos range from very short 1-2 minute clips to longer clips. We are also scheduling some video tutorial sessions in November and December on the new features. See the schedule of sessions here.
Question: I have an Excel file I filtered and then copied “visible cells only” to a new worksheet, so that I could maintain formulas. However, the smaller file size increases to an enormous file size, making it very slow for working with and impossible to email. What is causing this?
Answer: It appears that when you copy visible cells to a new worksheet that it pasted in the blank columns and rows also. You can tell this by noticing the scroll box along the vertical scroll bar remains at the top while scrolling down. You can also click in cell A1 and press Ctrl+Shift+End to go to the last cell of the active worksheet.
Select and delete the columns to the right of your data, and select and delete the rows below your data, and then save your file and close. You should notice the file size decreases to about what it was before. I still am at a loss as to why this happens, but have run into similar questions from users recently about data ranges including blank rows and columns. Here is a link which also includes an add-in you can download to remove excess rows and columns also. http://support.microsoft.com/kb/244435