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)

Excel Formula Tips

In a world where art meets Excel, lives these pretty little Excel Tips posters.

I had gathered some formulas for calculating workdays and displaying weekdays and workdays in a report. I use them often and wanted a nice way to remind myself of them.

This one hangs at my desk:

This is one that I made for a coworker who wanted a refresher on Excel, basic Sum and Average functions, as well a keyboard shortcuts for copying formulas and cells across rows or down columns. I added the fill series down menu option, since it is used often in our office. The background of this one is Opal Creek, OR. So pretty. I look forward to going there this summer.