skip to main |
skip to sidebar
you have ever faced some odd results from what appears to be routine data (and
who hasn’t…), it is wise to consider if you have any hidden blank cells. Let’s say that you have what Appears
to be a number of Blank Cells in the range on which you are performing a
calculation. If you are getting strange
results, you might ask yourself, “Are the blank cells actually blank?” The truth is that it is Not Always Easy to know
whether a cell or cells in Excel are Truly Blank!
reason it is difficult to know with certainty, is because of the fact there are
several ways of Hiding Data by:
any good database manager or analyst knows, this can cause Havoc with your
calculations. To detect this Invisible Data, there are at least a
couple of techniques. Assuming your cell
in question is A1, you can:
The use of identically-colored fonts
- Empty-string results of a formula
- Masking the data with the use of Custom Formatting (three semicolons: ;;; )
Simply insert this Function in an adjacent cell: =ISBLANK(A1)
If the cell is Blank, it will return True; if it is Not Blank, it will return False
- Copy the simple formula to include the rest of the range you are investigating
A second technique it the use an IF Statement as follows: =IF(A1<>"","Not
By determining if your cells are Truly Blank, you can help prevent Strange
and Unwanted Results on your worksheet.
So instead of Drawing a Blank, ask yourself, do
these cells actually contain data? Hmmmm?...
This IF Statement obviously returns Blank or Not Blank
- You can then take the appropriate action with the Not Blank cells
There is no rule in business that you
can’t have a little fun in business (at least there shouldn’t be…). Many highly effective Excel users often miss
is that they can Customize automatic corrections and save a great deal of time
and frustration. In this week’s post we’re going to look at some time-saving ways
you can leverage Autocorrect, as
well as have a little bit of mischievous fun with this underutilized tool.
This is particularly useful if you find
yourself typing long (or even moderate) Boilerplate phrases that are tedious
and time-consuming. For a brief example,
the name of my company is Continuing Education Group (CEG), and
we like to use the full name in correspondence. Typing the entire name every
time you do any kind of spreadsheet, email, or other document may not seem
terribly onerous, but it can be a small annoyance (and who needs any additional
irritations these days!).
The way you set up a phrase (company
name in this case) in Autocorrect may seem like a bit of a chore, but it is
really quite simple.
Here is How You Can Set this Up:
1. Go to FILE and select Options from the bottom of the column
2. Choose Proofing and click on the AutoCorrect Options button
3. In the Replace box, type CEG
4. In the With box, type Continuing Education Group (CEG)
5. Click Add and then OK. That’s really all there is to it!
Now for some Fun… There is an opportunity
for some adolescent amusement along the way (I am sure that many women reading
this will agree that most men can be adolescent at times…). Many of you know the classic gag of accessing
a fellow employee’s computer, and switching the mouse controls from (for
example) to left-handed from right-handed, and then watching the bewilderment
of the user. Autocorrect also offers a variety of chances for mischief as well.
Let’s say you have a friend named John
at work (or whomever). While he is away from his computer, go into AutoCorrect
and enter John in the Replace box and The Goof in
the With box. This can be done in Word or Outlook as well. He may think
he is losing it! (Just be sure you have him do this in your presence,
so he doesn’t unnecessarily embarrass himself.)
Useful for productivity and a bit of fun as well.
now, many, if not all of us are using The Cloud in some capacity for
housing our Excel work. If you are not, I urge you to give it a try.
is all about convenience and sharing. By placing your Excel
workbooks on OneDrive, for instance, they can be shared on
any of your devices. All you need to do
is save your Excel file to your OneDrive and it is there whenever
and wherever you need it. Since I
commonly switch from a conventional PC to a laptop to an iPad during the day, I
am finding the capacity to do this a Tremendous Advantage.
addition to easily saving your Excel masterworks to OneDrive and accessing it
on your other devices, you can also Share It with the World if you so
choose. Simply click Share on the left panel, save it to
OneDrive and then complete the Invite
People feature to provide them access to your work. What makes this Even Better is that the
other parties with whom you are sharing Do
Not even have to have Excel on their computer! (Now, I know this is nearly inconceivable to an
Excel Enthusiast, but Hey, some people just aren’t as cool
as we are…).
is, of course, not the only good solution to using the cloud for storing,
accessing, and working with your Excel files.
There are many other fine solutions, including Dropbox, iCloud,
and many others.
I’m a fan of using iPads, and I find myself doing more and more Real
Work on these tablets. This
being the case, I am quite sure that using a Microsoft Surface (or
other first-rate tablet) is also a great instrument for the New
we all continue to be more mobile in our professional, as well as personal
lives, it is good to keep in touch with the tools that enable us to get the
most out of what technology has to offer.
Whether its OneDrive, Dropbox, iCloud, Google, or the next big thing, The
Cloud has much to offer nearly all of us. Give it a try and see if you
users can be a serious lot. As I
addressed a couple of years ago, there is no disgrace in making your Excel worksheets
more visually interesting. In fact, adding
graphical elements to your spreadsheets can make them more eye-catching and
Good News is that Excel is loaded with an array of graphics tools that can make
your spreadsheets communicate better and make the users want to spend more time
viewing them. Channeling Martha Stewart for a moment, “Graphics are a Good Thing!”
look at some ways we can all do this:
Pizazz to Your Charts
some more Splashy graphics can add real Spark to an otherwise sufficient chart. You say you are plotting optimal Surfing
Days in Southern
California? Use Surfboards in your chart
(“Splashy”, get it?...)!
can find SmartArt in the Illustrations group on the Insert ribbon. SmartArt quickly enables you to create
diagrams of Org Charts, Processes, Cycles, and much more.
some eye-catching stylized text with Word Art objects. Just click on the WordArt dropdown button from the Text group and choose a style that
appeals to you. You can resize and drag
this text to any part of your worksheet.
forget the readymade shapes that add Functionality,
as well as engaging panache to your Excel workbooks. You can add Words and Hyperlinks to
these easily-created shapes, enabling the user to navigate to anywhere you wish
them to go.
are many, many creative ways to add graphics to your Excel masterpieces, of
course, and they can indeed add engaging elements to your work. Don’t get
caught in the mindset that graphics are merely frivolous. After all, our contemporary world is full of
media that depends on this form of communication. You should, at least occasionally, be using
in your Excel workbooks as well…