My collection of things.

jKafer on the Net

July 16th, 2019 at 5:28 pm

A tasty Excel tidbit today

This is one of those things that I wish (Upon a star) that would just be automatic.

There are so many things in Excel that are automatic and a nuisance at the same time, I think we take for granted the automatic things in Excel that don’t annoy us.  How’s that for your riddle of the day!

I was recently filling out an expense form and I thought – Hey, I got a minute, let’s fancy this puppy up a bit (I say this to myself with a southern drawl accent…when I say these things to myself – it spices up my day a bit).

So on the expense report I added formulas to total the row and then total the column. But when you add the formula it places a 0 or $0 in the column – it’s the default (yuck).

From this… …to this.

There are a couple ways to deal with this:

  • change the formula to something like this: if(sum(e19:m19)=0,””,sum(e19.m19))     
    If you do this, however, you will could get an error if you were adding the column up

  • Use Conditional Formatting: 
    The catch here is that sometimes when you set the font to white in conditional formatting, when you print the text will show up. You won’t see it on the screen, but when creating a PDF and/or printing it will show there.

Select “Use a formula…”

Choose white for the text color

It will show you what you have defined but in this case it is to display white text, so it won’t show here.

  • cell format: Number -> Custom ->   $0.00;-0;;@,
    seems straight forward and to the point, but not unless you understand the format keys. But this is my preferred way to do this.

I know this is pretty rudimentary, but lately I’ve noticed that those are the things being overlooked these days.


Tags: ,

Comments are closed.

  • Pages

  • Latest Pins on Pinterest

    • Follow Me on Pinterest
  • Quilting Projects I Want to Make

    • french braid quilt pattern | Thread: My First French Braid Quilt!

    • quilt kits black and gray - Google Search

    • How To Make A Quillow. One of my aunts made me one as a high school graduation gift. I wore it out and miss it!

    • Cute Cathedral Window by Pitter Putter Stitch. I'm really wanting to try the cathedral window quilt block and this one is so so pretty!

    • Follow Me on Pinterest
  • Categories

  • Latest Pins on Pinterest

    • Crystals Stones: #Crystal Care. More

    • Life path numbers and gemstones, #crystalhealing Believe in numbers

    • Love & Inspiration

    • Now that I am Married to the most incredible, caring, strong, respectful, sexiest guy in the whole world. I now understand my worth. Best Husband in the whole world!!

    • 20 Amazing Quotes About Life