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
Question during a “drop in” session: I have an Excel Workbook with several large worksheets. Three worksheets contain over 100,000 rows, one contains over 80,000 and another 23,000 rows. I need to match up the IDs and place the information from two massive worksheets into the one with 23,000 rows and I can’t figure out how to do it without having to search for each record manually.
Answer: I was possible to use a VLookup formula, however by the time I got to the third column to match and run the formula with such massive data, Excel “choked” and I was not able to save the results or even complete the task. So I went to my favorite application – Access. I imported the three tables into Access, then I created a query joining the common ID field, and selected the desired files, and got results in a matter of seconds, Then I exported the results to Excel. This was SO much faster and easier in Access than using Excel, in my opinion.
This was a question from a medical student during one of our drop in help sessions. I love challenging questions (and simple ones too) and helping people with their projects. These drop in sessions are a good opportunity to get one-on-one help on a variety of projects. See our schedule here.
Question: I need some help analyzing some data from two different spreadsheets and was wondering if you would guide me on what the best way to do it is. I have two spreadsheets and one contains costs from 2013 and one for 2014 and I need to match the two codes on each spreadsheet so we can do a comparison of the costs from 2013 to 2014.
Answer: It is possible, but in looking a your spreadsheets, you need to do a few things first. I basically brought all the information together into one sheet and created a pivot table. But because the sheet you need was a result of a pivot table which showed years group and departments grouped, I was not able to work with that to modify the columns, so I copied the Pivot table values to a different worksheet and could then add new columns for the appropriate year and copied it down the column, then I copied down the departments. Then I made sure the headings were the same in both 2013 and 2014 sheets in the correct placement. Then I copied one worksheet data to the bottom of the other. I named that worksheet “combined data”. Then I created a pivot table from that called PIVOTALL. I’m not sure exactly how you want to compare the data, but I placed the Years in columns and the ID number in the row and you can place the various columns of codes you wish to compare in the table filter section. You can rearrange pivot tables in a number of ways, so just start playing!
Question: I there a way to have a header appear on each worksheet of a multi worksheet workbook, so that I don’t have to create headers for each worksheet?
Answer: Yes. You can select all worksheets by clicking on the worksheet tab and then hold either the shift key to select a contiguous number of worksheets or ctrl key for noncontiguous worksheets and click those worksheet tabs. While those sheets are selected, use the Insert > Header option (or you can go to the Page Layout tab and click the diagonal arrow in the Page setup grouping and create your header/footer there). Having worksheets selected will place the same header on all sheets. Just remember to click on the active sheet tab before entering data intended for just one sheet. Doing that will “unselect” the other worksheets.
If you’ve ever attended my Excel mini-sessions, you’ll learn how obsessed I am with Excel Tables feature. This article from Tech Republic is a great summary of the many reasons why I love Excel Tables.
I came across this article recently from Tech Republic. It shows how using SumIf(), helper columns, Countifs() Excel functions may be simpler than using complex formulas. Click this link for some useful Excel tips.