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

    • Ombre Mountains Quilt Pattern by is a modern design that creates an ombre effect. The pattern includes all the sizes from Baby to King. #busyhandspatterns #ombremountainsquilt #ombrequilt #modernquilting

    • Leading Edge Quilt Pattern PDF Download - Etsy

    • Homecoming Quilt- My Peppered Cotton version & tester quilts! – Lo & Behold Stitchery

    • Homecoming Quilt- My Peppered Cotton version & tester quilts! – Lo & Behold Stitchery

    • On the Edge is a quick and fun modern quilt pattern with a lot of custom design possibilities! This PDF pattern includes directions for making a twin-size quilt (72 x 96), a throw-size quilt (56 x 64), a baby size (38.5 x 44), and a pillow/mini quilt (18 x 18). See the Instagram hashtag

    • Follow Me on Pinterest