Excel - Combining text in multiple columns into one and Paste Value

It's a Excel Tech Tip kind of week!

I made this for one of my favorite humans. She uses these quite a bit, but sometimes the memory of them hides on her.

The first one, Concatenate, is delightful. There are a number of ways to do this task, so I listed our personal favorites. It's like mail merge for Excel! It will take the text in multiple cells and display them in a single cell. The two examples will display the same way.

=A1&" "&B1&" "&C1&""
or
=CONCATENATE(A1, B1, C1)

You can even get a little fancy and add punctuation or words in between each cell:

=A1&" & "&"B2 would display as Bob & Steve  (if A1=Bob and B2=Steve)

Similarly, you can use Concatenate:

=CONCATENATE(A1," & ",B2) would also show up at Bob & Steve

For the second part of the poster - Paste Value. There are a lot of "Paste special" options - I personally use Paste Value more often in what I'm working on. It is especially helpful if you want to paste the contents of a cell into another part of a spreadsheet, but want the hard coded number, not a link to the live data that might change tomorrow. Paste Value takes the formula away and just leaves you with the number, no frills, no database connections.

Please enjoy!

How to change #REF or #N/A to 0 (or whatever you want)

Ooohhh Excel tips. I love them.

I'm in the middle of making a report where data will be added weekly. Right now, I have no data (calls) for the 5pm timeslot, but there might be some in the future. So, for design sake, I'm making a place for 5pm and entering the formula that will work once there is data. Presently though, the formula returns #REF. Since that's just ugly, I want a way to change the #REF to display as zero. A quick search brought up this delightful formula:

=IF(ISERROR(yourformula),0,yourformula)

In my case, the full formula looks like this:

=IF(ISERROR(GETPIVOTDATA("Count",$S$12,"Time",17,"Day","Friday")/K7),0,(GETPIVOTDATA("Count",$S$12,"Time",17,"Day","Friday")/K7))

Currently, it displays as zero. Once there is data for Friday at 5pm, my formula will take that number and divide it by however many Fridays (K7) we have been tracking. Then give me an average of calls per hour, per Friday.

Sidenote - Since this is based off of a pivot table, you would think that you could just select "Average" in the values for this. However, due to what is included in the data, it is not dividing the total calls but the amount of actual Fridays but rather how many times "Friday" occurs. So, we take the long way around. If you are reading this and thinking "wow, why did she not try ___", please feel welcome to comment your suggestion. I often take the long way around when it's actually quite straight forward. This is my source data, which is then converted into a pivot table and grouped by hour, day, date, user (whatever is needed, you know how it is)

The garden grows

We got half of the garden planted last month and there is more to come this weekend. It looks like we're in for a great growing year!

The new potato bin is amazing. It's made out if a drawer insert that we found at the Rebuilding Center. Jason (the amazing husband) put casters on it, so it can be wheeled around the yard. Potatoes enjoy new views and travel, just like the rest of us. 

We have also planted beets, chard, romaine, strawberries, kholrabi and carrots. The carrots aren't sprouting for some reason... Very likely because Data Dog keeps jumping into the containers. I'll plant more in the planter bed this weekend. 

Next up is tomatillos, tomatoes, hops, more beets (never enough). Pickling cucumbers will follow close behind. 

I'm looking forward to a bounty this year!