Software

Office challenge: How would you improve this Excel spreadsheet

This week, improve the example Excel spreadsheet and learn the solution to last week's transposing puzzle.

A reader recently sent me a worksheet that she's sure could be more efficient. Her solution works, but it does require a lot of setup. I agree, she's working harder than she needs to. For a bit of background:

  • Column A contains dates.
  • Column B is a descriptive text column. It could contain any kind of data really--it's a reference value and doesn't figure into the actual solution.
  • Column C uses the Month() function to return the year value, referencing the date values in column A.
  • Column D is another text column that contains a one-character code that also serves as the column heading in the table to the right.
The table summarizes the data by date and code. For instance, there are two January entries, one for A and one for B; one February entry for C; two March entries, one for A and one for C; and so on. Each month, this reader enters a CountIf() function that counts the individual codes for just that month. I agree with her, she's working harder than she needs to. This month's challenge is to generate the summary table without all the handholding. It doesn't have to be completely dynamic, but your solution should be easier than entering a unique CountIf() each month. You can use any feature you like—no restrictions. If you need more information, just ask!
TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

Last week we asked…

Can you create a link with transposed data? Hic (Howard) was the first to respond with the answer I expected. Use the Transpose() function. Find a spot in the sheet with plenty of room and enter the following function:

=Transpose(sourcerange)

Enter the function as an array by pressing [Ctrl]+[Alt]+[Enter]. As mentioned, this creates a link between the source and the transposed data. If you update the source data, Excel updates the respective value in the transposed range. I apologize for the confusion. Some of you read "… create a link between the source and transposed range, so that updating values in one range updates the respective values in the other?" as meaning a two-way link. I can see why you thought that and I'll try to be more careful in the future. While writing, it was perfectly clear to me that "one range" referred to source and so on. But, I see now that I was unclear. I was worse than unclear, I did not say what I meant, but instead of taking me to task, you generously tried to comply! You guys always surprise and impress me! To Ppn and Vineet369, thank you for trying to meet my unrealistic condition. I could not, however, get your methods to work. Perhaps we can continue this discussion because I don't know of any way to create a two-way link that wouldn't be more trouble than it could possibly be worth!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

48 comments
sparky75
sparky75

First, if column C contains the =month function, it would return the month, not year. You can use the returned value in a sum array formula. In F2 (or anywhere you put your summary table) enter =SUM((C1:C7=1)*(D1:D7="A")), then use Ctrl/Shf/Ent to create the array formula. (You can expand the range from 1 to xx). Copy this to all the other cells in the summary section, changing the D1:D7 reference to B, C, etc. under each respective heading. Once it's set up, it will automatically update the totals as additional data is entered.

mpenny
mpenny

Change column C (Year) and column E (Month) to constants. Place this array formula in the summary area. {=SUM(IF(YEAR(BookDate)=YEARn,IF(MONTH(BookDate)=MONTHn,IF(BookCode=CODEx,1))))} Where ???BookDate??? = Date column (A:A); ???BookCode??? = Code column (C:C); YEARn and MONTHn = year and month summary row headers and CODEx = the summary code column headers to count.

ytugcu
ytugcu

I entered the following formula in cell "F2" and copied it to the range F2:J13 =SUMPRODUCT((MONTH($A$2:$A$7)=ROW()-1)*($D$2:$D$7=F$1))

RU7
RU7

for the SUM array AND the SUMPRODUCT formulas. But they can be combined thusly to only depend on the Date and Code columns. =SUMPRODUCT((TEXT(Date,"mmmm")=$E2)*(Code=F$1))

bruno.hecquard
bruno.hecquard

With Excel 2007 and upward, it is possible to convert a range to a dynamic List. This range gets a name, by default "Table1". It is then possible to create a Pivot table which refers to this dynamic named range. My suggestion: 1/ Add a column for Month(Date) 2/ Convert the data range to an Excel table (Menu Insert Table) 3/ Summarize this table with a pivot Table (Menu Design, Summarize with pivot table) 4/The Pivot table will be placed on a separate tabsheet. It is possible to addd a small piece of VBA code attached to the given tabsheet, not in a module, in order to refresh it automatically when openning that tabsheet. Remark: The year and month column are optional: it is possible, in a pivot table, to summarize by year/month automatically. Exemple: Source table: Date Title Book 01/01/2011 XXX A 02/04/2012 yyy B 13/12/2010 zzz A 07/09/2010 www D Resulting Pivot Years Date A B D Grand Total 2010 Sep 1 1 Dec 1 1 2011 Jan 1 1 2012 Apr 1 1 Grand Total 2 1 1 4

RU7
RU7

In my admittedly limited knowledge of pivot tables, they are a snapshot of the data, not a dynamic representation. Create a pivot table then add a row of new data and the pivot table does not update. Tutorially corrective comments are welcome.

gluhas
gluhas

Eliminate column C and just create a PivotTable. Everything else is more work!

edwardtisdale
edwardtisdale

These two tables side by side looks messy. Before deciding pivoit table I was going to decide to at least move the table on the right down to below the one on the left, because at first it looks like only one table. With a pivot table you can have all of that data in one table and manipulate it into smaller ones.

RU7
RU7

Assuming column C has the formula YEAR(A2), insert new column C titled Month with formula =MONTH(A2) and fill it down. Format the old Month column (formerly E now F) as mmmm-yyyy and add 2011 to each. Put the following formula in cell G2 of the table: =COUNTIFS($E:$E,"=" & G$1,$C:$C,"=" & MONTH($F2),$D:$D,"=" & YEAR($F2)) Then fill it across the whole table. Then fill the top row of the table down the whole table. You could also do it this way to get rid of all the zeros: =IF(COUNTIFS($E:$E,"=" & G$1,$C:$C,"=" & MONTH($F2),$D:$D,"=" & YEAR($F2))=0," ",COUNTIFS($E:$E,"=" & G$1,$C:$C,"=" & MONTH($F2),$D:$D,"=" & YEAR($F2))) Either way you can now add data rows at will. And you can select cells F2:K14 and fill them down as many years as you wish. Note: the space between the two double quotes should be removed. It is there to appease the forum text goddess.

Richard Noel
Richard Noel

There was a typo. "Enter the function as an array by pressing [Ctrl]+[Alt]+[Enter]." Should be Enter the function as an array by pressing [Ctrl]+[Shift]+[Enter].

rberens
rberens

Presuming that the "Month" column should be something like "=MONTH(RC[-2])" and that one can get around using numbers rather than text for the months in the table (or kludge things with custom formatting or inserting a dummy text column), enter the formula "=COUNTIFS(C3,R1C13,C4,R2C13)" in cell R1C5 (overwriting the "month" label). Now, highlight R1C5:R13:C10, and click on Data/What-If_Analysis (Data Tools)/Data_Table; enter R2C13 for the "row input cell" and R1C13 for the "column input cell." The "table" magically fills itself out. This also gives the user essentially unlimited room (all of columns 1-4) for data entry. (Oh, yeah -- I have found that R1C1 reference style is far easier to use; I use a lot of OFFSET, ADDRESS, and similar referential functions)

basil.cinnamon
basil.cinnamon

Comment not about the discussion topic but about the new format: this is a good discussion and one would like to save all the comments in one place for later reference. The old format had a "view all" option that has disappeared. That means we'd have to save each of the comments individually! Please bring back the "view all" option!

dmnicolay
dmnicolay

I would create a pivot table. Insert a new column that calculates: =TEXT(A2,"MMM") to display the text month of the date field (call it tMonth). Then use this tmonth in the row labels, the code (column D) in the column labels, and the Count of tMonth in the values. Then, Right-click on the pivot table and select Pivot Table Options, select Totals & Filters and Un-click show grand totals for rows. Then select Layout 7 Format and check the "For Empty cells show:" and specify 0. There are a few other formatting options, but this will do.

gedwards
gedwards

You should just need Date and Code. Range all of columns A:B (assuming Date and Code are in these). Group by month on Rows, Codes in Columns, count of Date in Summary area. You can toggle the Blanks off to remove them, although I would probably look further into a dynamic named range.

bmonge
bmonge

I agree with robert@... perhaps the function could be =EOMONTH(A2,0) and do a custom format as mmm. ie this will gives me Jan for for dates 1/16/11 and 1/30/11. Then use the pivot to summarize the counts. (did it in 5 minutes)

gregrussell
gregrussell

this will count the number of occurances determined by the ____

Jim_in_Michigan
Jim_in_Michigan

I could do this with the original three source columns Date, Title, Code and no formulas or additional data. It would be done with a Pivot Table, the range would be the entire three columns from 1 to the 65,000th row. In the pivot table the rows would be the Dates. The Codes would in the be in the Column headers and the Titles would be in the field. A few customizations would be necessary to get the look she is after, use the MMMM format for the Dates in the first column of the Pivot Table, Add one row with "D" in the code column, and using the drop down arrow in Row Labels and Count of Titles you need to uncheck "blank". The only thing that would be 'missing' is the zeros but I am not sure how necessary they would be. My Pivot Table automatically did count of titles. Additional data could be added and redefining the range is not necessary. Enjoy.

alta
alta

Change the Year column to a Month column with a function getting the month from the Date in column A. Then in cell F2 enter =COUNTIFS($C$2:$C$7,MONTH($E2),$D$2:$D$7,F$1). Copy this to all the cells in the spreadsheet.

paul
paul

Use a dynamic named range, create a pivot table on that and then apply grouping on the pivot table date field to show months - could not be simpler

jkiernan
jkiernan

Pivot tables can be dynamic if using a named range. See my earlier post in this discussion.

daneke
daneke

I would use a table for my source and hide the first 12 rows that would have a date in each month, no text in column B, the Year is the Year() function to pull the year from the date, and a code that ranges from A-E. This will permit a blank pivot table when starting data entry on row 14. The Pivot table is placed in the worksheet starting on row 14 (to hide the first entries). The date is the row label grouped by MONTH, the Code is Column label, the Count of Title is Values field with a space in the field settings Custom Name. SInce all months and all codes are already displayed, set show Pivot Table Options, FOr empty cells show: 0. -- Check Refresh data when opening the file. --Totals for Row only.

djones
djones

Hi Pivot table seems easiest however every time a new row of data is added the pivot table range would need updating - not really an automated process

zwade.sandy
zwade.sandy

1. Change to 'Table' instead of range 2. Insert PivotTable based on 'Table' 3. Drag 'Code' to the Column Label, Pivot Field List 4. Drag 'Title' to Values. It should say 'Count of Title' 5. Drag 'Date' to Row Label 6. Right click first date in PivotTable and Select 'Group...', ensure only 'Months' is selected and select OK The summary\pivot table will automatically calculated based on information populated in the data 'Table' There isn't a need for the calculated year\month column

ReadThis
ReadThis

First off, I have no idea how using the Month() function returns a year value, I would use the Year() function instead. On to my solution (see screen snap): 1 - Add column L containing formula. =IF(ISBLANK(A2),"",MONTH(A2)&D2) 2 - In column M enter the number for each month found in column E. 3 - Use one formula for cells F2 through J13. =COUNTIF($L$2:$L$99,$M2&F$1)

art_walls
art_walls

You would probably want to ensure you only included one year's worth of data, in case the source data has multiple years. Also, I would use the row command for comparing the month: =SUMPRODUCT(N(YEAR($A$2:$A$500)=2011),N(MONTH($A$2:$A$500)=ROW()-1),N($D$2:$D$500=F$1)) There should probably be a place around the chart to plug the year in, rather than hard-coding it as I did here. Unfortunately, you can't just use $A:$A, or if you could it would be prohibitively slow. The Pivot table option would not include months that are not in the source data, at least not without adding some dummy data.

Uglyfishhead
Uglyfishhead

somthing is missing from the instruction

ppg
ppg

Just to clarify, the method that I (and Vineet369) gave also only creates a one way link. I'm not sure why you had a problem with it. It works fine for me under Excel 2007. If you wish to create a two way link you need a worksheet_change macro. As to whether it is more trouble that it is worth - it depends on why you want it. The following is a basic sample macro again for Excel 2007: Private Sub Worksheet_Change(ByVal Target As Range) Dim ColRange As Range Dim RowRange As Range Dim kr As Integer, kc As Integer, NumCell As Integer 'As currently set up only a single column/row is allowed 'The range and its transpose are hard coded here. ' you could change it to a named range or specify the range as ' text in cell on the worksheet. ' Disable events - otherwise you re-enter here when sync the cells Application.EnableEvents = False Set ColRange = Range("A2:A10") ' Set up the range and its transpose NumCell = ColRange.Rows.Count Set RowRange = Range(Range("B1"), Cells(1, 1 + NumCell)) 'Go through the range that has been changed For kc = Target.Column To Target.Column + Target.Columns.Count - 1 For kr = Target.Row To Target.Row + Target.Rows.Count - 1 ' see if the changed cell is in the column If ((kr >= ColRange.Row And kr < ColRange.Row + NumCell) And (kc = ColRange.Column)) Then ' Do the copy Cells(RowRange.Row, RowRange.Column + kr - ColRange.Row).Value = Cells(kr, kc).Value End If ' see if the changed cell is in the row If ((kr = RowRange.Row) And (kc >= RowRange.Column And kc < RowRange.Column + NumCell)) Then Cells(ColRange.Row + kc - RowRange.Column, ColRange.Column).Value = Cells(kr, kc).Value End If Next Next ' Restart event handling Application.EnableEvents = True End Sub

pgit
pgit

Perhaps we can continue this discussion because I don???t know of any way to create a two-way link that wouldn???t be more trouble than it could possibly be worth! I once had the need to to be able to input on 'both sides' of a transposition, using Tandy Deskmate spread sheet software, I can't recall what that component was called. This was on an 80286 laptop, btw, to entirely date myself... Anyway, not seeing any direct function for this, I set up the two functions separately, using 4 cells. There was an input and output in each direction, and if I needed to 'go the other way' I had a function that simply applied the output of one of the directions to the input of the other. I had to be careful as to whether I needed to preserve the output of one calculation before inputting another number, for instance to do comparison. I "solved" this by printing any results I needed to remain permanent, though I continued tinkering with the thing trying to get the spreadsheet itself to save an array of answers. That would have been immensely useful, I would have had a table that spit out very accurate cost comparisons between two available but quite different services. To be honest, even in that primitive era the weak link was my math abilities and not the Tandy software. =\

philippe.henrion
philippe.henrion

The smallest solution is probably to use an array function. Simply type =SUM(IF(TEXT($A$2:$A$7;"Mmmm")&$D$2:$D$7=$E2&F$1;1;0)) in cell F2, press Ctrl-Alt-Enter, and copy it down and right into the table. Now if you want to make it a bit more readable, define first two names ranges: ranDates = $A$2:$A$7 and ranCods = $D$2:$D$7. Then, the formula becomes {=SUM(IF(TEXT(ranDates;"Mmmm")&ranCodes=$E4&F$1;1;0))} In both cases this gives purely dynamic results, which PivotTables do not really do.

dipesh.patel
dipesh.patel

Use a Pivot table to automatically update the summary table as and when the raw info is updated.

GazSkeltz
GazSkeltz

Hi, Assuming that you meant to put the month number in column C rather than the year and then have month numbers rather than text in column E (I could convert the numbers in column C to text, but let's be brief... Create named ranges for columns A to D based on the text in row 1. In Cell F2, insert the following Array Formula (Ctrl Shift Enter): =SUM((Month=$E2)*(Code=F$1)) Copy across and down to E13. Job done Regards Gary

dogknees
dogknees

Assuming your description is wrong and you have month numbers in column C as written in the text rather then Years as in your worksheet. Array enter the following in cell F2, then copy down and across all cells in the output table. =SUM(IF($D:$D=MONTH("1-"&$G3&"-2000"),1,0)*IF($E:$E=H$2,1,0)) This doesn't need to be updated when more data is entered as it looks at the whole columns in the source table.

tmchristomos
tmchristomos

In column E (Labeled "Month") insert the formula =TEXT(MONTH(A2)*29,"mmmm") in cell E2 If she is going to use the year to filter on in the future, she might want to use the formula =VALUE(TEXT(A2,"yyyy")) The Text function extracts the year as text and the Value function converts it back to a 4 digit integer. If you are OK working with numbers as text, you can eliminate the Value portion of the formula I also add a few blank rows and highlight the last row and insert a comment that says "Insert Rows above here". I name the range from A1 to the lower right highlighted cell (in my case E18) with something simple like "All" or "Data". I copied the formulas I just created to each row through the last row of the range for each column My pivot Table has Year as the Report Filter; Month as the Row Label; Code as the Column Label:, and, Titles as the Values Field (count). Under the Pivot Table Options, I removed the check from the "Autofit Column Widths" option and checked the "Preserve Formatting" option. Once the pivot table has been created (I used the same sheet as the data), filter the Count of titles to and uncheck "blank" On my last row, I added a "D" in the Code column, since it had not been used and this allowed me to have a completely formatted pivot table that will only add new months as they are added This yielded a Pivot table that looked like this. Count of Titles by Code and Month Year (All) Count of Titles Month A B C D E January 1 1 February 1 March 1 1 April 1 Totals 2 1 2 1 Now she will just have to enter new data in columns A - C, insert new rows as need (copying formulas and refresh the pivot table to get updated information

jim_mannella
jim_mannella

=sumproduct(--(month($a$2:$a$1000)=row()-1),--($D$2:$D$1000=F$1))

hic
hic

A pivot table works quite nicely, and though it doesn't produce EXACTLY the result you specified, it does scale very nicely as data is added to the database. I worked up a quick example at: http://templates.modelsheetsoft.com/public/TechRepub.xlsx There was a conflict between the text (talks about the MONTH() function to get a year) and the example data, which shows a year. I went with the example data since the year is useful if the database extends over more than one year. I used the grouping ability in the Pivot Table to group the date by month so as to not have to add a computed column (in Excel 2010, click right on any date label to access grouping.) Othewise, I could have computed a column that's the first of the month and used that, along with formatting options, to group by and show the month names. --Howard

johnlwalp
johnlwalp

Using =COUNTIFS($C$2:$C$27,"=1",$D$2:$D$27,"=A") for January, A and changing 1 to 2, ???, 12 and A to B, ???, E as appropriate for the summary table cell. Make sure last row of data is same in both ranges for amount of entered data or make large enough to include full year of data.

robert
robert

Add the month value / description column to the data set... could be derived with a function evaluating the date column... then apply a pivot table that will summarize the counts of each code (column) for each month (row).

Joaquim Amado Lopes
Joaquim Amado Lopes

1. Cell C2 = MONTH(A2)&D2 ' creates a string that identifies the month in column A and the code in column D 2. Copy C2 down 3. Cells E2 to E13 = 1 Jan 2011, 1 Feb 2011, ..., 1 Dec 2011 4. Format cells E2 to E13 as Mmmm => appears "January", "February", ..., "December" 5. Cell F2 = COUNTIF($C$2:$C$14;MONTH($E2)&F$1) 6. Copy F2 right and down

RU7
RU7

I don't see how this will update as new data is entered. It would also be better to reference the row heading (as months 1 - 12) and column heading instead of 1 and "A". If you use mixed refs with fixed column for the row headings and fixed row for the column headings, the formula would copy without modifying for B, C, etc.

RU7
RU7

From the "Highly expandable" post, I think you could cut the second table, cells F1:K14, paste them onto a new sheet, and all the references would update to include the original sheet name.

djones
djones

My thanks to you for this (I missed your previous post, which I have now just looked at and tried) I didnt know you could use dynamic ranges. We constantly update our pivot tables by reselecting the range. This method will make it much easier in future Thanks again D

RU7
RU7

That way adding data rows updates the table. =SUM(IF(TEXT($A:$A,"Mmmm")&$D:$D=$E2&F$1,1,0)) I also had to use commas instead of semicolons. I suppose that could be a locality setting though. I did notice that updating the table took a little longer when I added a data row. So I'm thinking that array formulas might take longer to re-calculate.

JimmacNOV
JimmacNOV

Nice thing about this solution is that it does not require redundant month or year columns. Allows the data entry table to be condensed and automatically updates if you use dynamic ranges.

jkiernan
jkiernan

A pivot table can be dynamic if using a named range. To create one, go to Insert>Name>Define. Give the range a name (for instance, "Book List"), and in "Refers to", use this formula: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) Use this named range with creating the pivot table. A simple refresh is all that's needed when rows are added to the list.

tmchristomos
tmchristomos

When my comment was posted, it compressed the pivot table sample section, but my pivot table looks just like her original table.

SuperBoy
SuperBoy

A variation is to have two columns (lets say in column O and P) with the month long format and integer. Then, in the countif function, have $O2&F$1.

sparky75
sparky75

As you copy the formula down, you also have to change the month reference (Col C) in the formula. If you set your ranges large enough to more than what you need (say C1:C100 and D1:D100) it will automatically update the totals as new data is entered. For example, here's what the formulas in F5 and G5 (A&B totals for month 4=April) could look like: {=SUM(($C$1:$C$100=4)*($D$1:$D$100="A"))} and {=SUM(($C$1:$C$100=4)*($D$1:$D$100="B"))} I did not get into range names or referencing the row heading, and stayed with the example's layout.

RU7
RU7

Until you enter the 100th row of data (the first row is headings, really no need to include that in the range). You could just use whole columns, $C:$C and $D:$D. But then each change in the data requires a re-calculation of the entire range for every cell in the summary table. Another way would be to have one spacer row at the end of the range, possible with instructions in it that would never be evaluated as data. Entering new data would involve highlighting that spacer row, Inserting a new row, and entering the new data in the new row. After which you again have the spacer row at the end and ranges that have been automatically expanded to include the new row and the spacer row.

Editor's Picks