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.
Question: I have a pie chart that is plotting correctly, but the labels of the legends are incorrect. My chart data is not in simple columns and rows, but rather selected cells within the data. I have tried changing many options but can’t seem to get the labels to display correctly. I would like to create the chart from this spreadsheet so I don’t have to re-enter my data in contiguous columns and rows.
Answer: In looking at the spreadsheet, I first looked at the chart data (Chart Design tab and Select Data) and tried changing the Legend Entries (series), by clicking on the cells in the data. When I changed it, the chart label changed on just one label. I tried switching Row/Column, which did not work either. So I created another table referencing the cells used in the chart so that it was in contiguous rows and columns. I added data labels (from the Layout tab) selecting best fit. Doing this, the chart labels were correct and the legend was correct. Even though this solved the problem, we were still puzzled how to do this with non-contiguous data. So in looking at the contiguous chart data that was plotted correctly, we discovered that we needed to delete the series 2, 3 and 4 from the Legend Entries, leaving only series 1, and edit the” horizontal category axis labels” section from the Select Data options for that remaining series, by selecting the non-contiguous cells (holding down the Ctrl key).
So many times we spend a lot of time struggling with a problem but could be more productive by taking a break from the problem by taking a short walk, or trying an easier (workaround), or getting another point of view. I often find solutions or clues by “googling” my questions.