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!

Seitan Roast – Celebrate your Holidays with Seitan

Seitan Roast – Celebrate your Holidays with Seitan

I love this roast for holiday events. With Easter coming up, I wanted to share it with you. This is a great one for mixed company. Meat eaters completely enjoy it. Sadly, not for the Gluten Free guests though.

All Hail Seitan!

Ingredients
For the Seitan:
10 oz Firm tofu
1 cup water
3 Tbl oil
2 tsp salt
1 tsp pepper
2 tsp onion powder
1 tsp garlic gradual/powder
1 tsp sage
2 Tbl soy sauce
1 Tbl Braggs liquid amino (or ½ Tbl more soy sauce)
2 Tbl Nutritional yeast (big flake, not fine powder)
2 tsp Kitchen Bouquet
2 and 1/4 cup vital wheat gluten

For the Stuffing:
.5 cup mixed wild rice
1 cups water
1 “chicken” flavor bouillon cube
1 sprig fresh Rosemary, finely chopped
¼ cup pine nuts (raw or toasted – I usually use raw)
Salt and pepper to taste

You will also need:
A steamer tray or stove top steamer (available for super cheap at any Asian grocery store)
Tin foil
Spray cooking oil

Start the stuffing first – In a pan or rice cooker, add rice, water, bouillon cube and rosemary – Cook rice following standard cooking method – Once rice is done, add pine nuts, salt and pepper to taste. Set aside.

Get your steamer going.

In a medium bowl, add the vital wheat gluten. In a blender (blender is preferable, but food processor will also work), combine everything on the “For the Seitan” list except the vital wheat gluten.  Pour mixture into the bowl with the vital wheat gluten and stir. Once the mixture has combined a little, get your hands in there and knead it a bunch. You want to activate the gluten so that it pulls against itself a bit and creates thick strands (about 3-4 minutes of kneading).

Lay out 2 large pieces of tinfoil in a cross pattern and spray with cooking oil.

Create a flat layer of the gluten mixture in the center of the tinfoil cross. To do this, I usually pull off small pieces and lay them side-by-side and then flatten them all together (since gluten can be difficult to roll out or flatten like pizza dough) – the small pieces will form together as it is cooking.

In the center of your flattened gluten, make a pile of the rice mixture. Roll the gluten around the rice mixture like a burrito. Pinch the ends together so that the rice is entirely encased. Use the foil wrapping to hold it all together tightly.

Place in the steamer and steam for 1 hour. Check on the water occasionally to make sure your steamer doesn't boil dry. 15 minutes before the steaming is complete, turn the oven to 350 degrees. When the steaming is complete, put the foil wrapped goodness into the oven for 45 minutes.

Optional, you can roast vegetables in the oven with the roast (in a separate pan) so they are ready when the roast is!

When oven time is complete, serve sliced with your festive meal :D

Beet Burgers - Gluten Free and Vegan

I don’t think I can eat enough beets and cauliflower. They might be my favorite foods. Both vegetables are incredibly versatile.

For this meal, I went very “American bar food” – burgers and hot wing. Shockingly, we were not watching sports that night.

For the hot-wings, I love the PETA Vegan Spicy Buffalo Cauliflower Wing – I have made them with a few different types of flour and find that brown rice flour is my favorite – this makes them vegan AND gluten free AND paleo (in some people’s interpretation of paleo). While I am not gluten free nor paleo, I have some dear friends who are both. Finding gluten-free, paleo, vegan recipes is an exciting challenge. I love food challenge.

The Beet Burgers are hands down my favorite burger ever. The recipe is super quick and easy too!

Note: if you go gluten free on the flour and bun, this is a great gluten free and vegan meal!

Ingredients:

3 whole beets – medium sized:  raw, peeled and grated

1 small onion, diced

2 Tbl chia seeds

1 Tbl flour (whatever kind you like, I love brown rice flour)

1 Tbl olive oil

1 tsp garlic granuals/powder

1 tsp onion powder

2 tsp dry basil (if you have fresh, use that instead. Nummers)

Salt and Pepper to taste

Oil for frying

Condiments and Buns – I love sprouts, avocado and vegan mayo/aoili on this amazing burger.  

Instructions:

Combine all ingredients in a medium bowl, stir well. Let sit for about 30 minutes.

Once the beet mixture has had time to mingle, heat about 3 Tbls of oil In a pan. Form the beet mixture into patties and place carefully in the oil. Let cook on that side for 4-5 minutes, until a good crust has formed. Flip and cook that side as well.

Place pattie on bun and add condiments.

Enjoy!

Serves 2-3, depending on burger thickness.

With the leftover beet mixture, I made little slider burgers and fried them. I then packed them for lunch the next day with some adorable vegan (but not GF) buns that I found. This is the slider bar at my desk at work:

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.

Another New Years Resolution

I'm generally not one for New Year's Resolutions, but I've been thinking about this blog a lot lately. It's time to step it up and really start posting more. It also happens to be the start of the New Year.. so that works out well. Coming up soon, I will document the delicious Tofurkey Noodle Soup that I made last night.

Starting with this little teaser.

Delicious vegan soup. It's winter. Soup is where it's at. Also, new truffle color Le Creuset makes me want to post a million pictures of cooking. Stay tuned!