Deleting Old Records

There are often times in the real world when you want to update your databases or tables by deleting data that is no longer pertinent to your operation. Let’s say that you work in the HR department of your company and you want to tidy up your records to show only your Present Employees.  How can you do this without a great deal of effort?

Here is Fabulous way to select rows (or columns, cells, etc) in a database that contain a Key Word, and Delete Them All at Once. In our example the records you wish to delete all have the word Grohowski (who found greener pastures in another company) included in one of the cells.

Here is What You Do:

 

1)  Choose any cell in the database, and press Ctrl+F on your keyboard

2)  Type the Key Word (“Grohowski” in our example) in textbox and click the Find All button

3)  Now Select an item in the list, and press Ctrl+A to select the entire list

4)  Go to the Home tab, click the down-arrow on Delete (in the Cells group on the ribbon), and then click Delete Sheet Rows

5)  Voila!  Your unwanted records are history!

This remarkably simple trick can save you a great deal of work and help you maintain up-to-date records in your database. Try it out some time!

A Dashboard Parable

There once was an Excel professional, (you may have known him or her), who would create wonderful, detailed reports that went on and on and on for many pages in the professional’s workbook. The professional’s work was impeccable. All the information was there, every bit of it! So why (Oh, Alas, Alas…) did it Not get Used and Appreciated like it should? 

The problem with this fine professional’s work was it was not Concise, Convenient, or Controllable. 

The solution is to create what has become popularly known as a Dashboard. A key part of the definition of a Dashboard is that they can fit on a single computer screen so it can be monitored at a glance. 

Dashboards can be linked to complex (or even relatively simple) formulas that work in the background gathering information from a centralized database. The formulas may be comprised of Database functions, Boolean arrays, IF functions, or several others. 

Here are 7 Key Features you should strive for in your Dashboard: 
1. The data is the Star, but keep in mind the other elements 
2. Make it Interactive with dropdowns, spinners, scrollbars, etc. 
3. Keep your audience in mind (it’s Their Dashboard…) 
4. Don’t clutter the screen with unnecessary components 
5. Make it attractive enough to hold your audience’s interest, but… 
6. Don’t make it so stylized it muddles the message 
7. Avoid 3-D charts (fancy, but 2-D tend to be more functional) 

A truly fine Excel professional will always make a Dashboard that is Concise, Convenient, and Controllable. It's all about Information, not just the data. A dashboard is only as valuable as the Information that can be obtained from it!

Subtotals Rock!

As I often say, there are so many great tools in Excel that get surprisingly little notice. The built-in Subtotal feature can save you an enormous amount of time and give you a remarkably easy way to Organize Your Data.  The really great thing is that it takes practically no effort. Here are 3 Reasons Why you should put Subtotals in your Excel tool belt:

1. Flexibility: As you would expect, Subtotals are able to Summarize your data by SUM. But that’s not all! Your data can also be reviewed by Count, Average, Max, Standard Deviation, as well as other functions.

2. Energetic: On the fly accessibility whenever you need to control your data and fields at your fingertips, you have the ability to slice and dice your information in real time!

3. Sharable: Often what an analyst or manager wants is a “Quick and Dirty” way of reviewing their data in a logical manner. Moreover, however, it is typically desirable to share the information (“Information” as I always say is clearly an analyst’s goal, rather than presenting mere “Data”) with others. Subtotals are a sharp, concise way of doing exactly that!

Subtotals are often misunderstood, ignored, or simply overlooked. Important Tip: Since you need to initially sort your data according to what you wish to summarize, it might be a good idea to save it to a Practice File before first trying this out. It is always good to have a backup.

Subtotals. Another example of a Fine Excel Tool that you should test out soon!

Leading Zeros

By default, when keying data with the first digit being a zero, Excel will always drop the leading zero (or series of zeros).  But suppose that you have a range of zip codes, account numbers, phone numbers, or other type of data – What do you do then?

Here are Three Different Ways to Handle this Type of Situation:

1.   Place an apostrophe (AKA a tick mark) initially, and then add the leading zero. 

2.   Format Cells by selecting Custom/Type:  Choose 10 zeros (if you have, for example,10-digit account numbers) and hit Okay.

3.   The third method (I recommend this for most cases…) is to format the cell range as Text.  This automatically corrects this potentially annoying problem.

That’s all there is to it!  The next time you want to keep your Leading Zeros, remember these simple tricks.

Drawing a Blank

If 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!

The reason it is difficult to know with certainty, is because of the fact there are several ways of Hiding Data by:

• The use of identically-colored fonts
• Empty-string results of a formula
• Masking the data with the use of Custom Formatting (three semicolons: ;;; )

As 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:

1.         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

 2.         A second technique it the use an IF Statement as follows:  =IF(A1<>"","Not Blank", "Blank")

• This IF Statement obviously returns Blank or Not Blank
• You can then take the appropriate action with the Not Blank cells

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?...

Autocorrect: Fun & Function

 

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.)

Autocorrect: Useful for productivity and a bit of fun as well.

OneDrive

By 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.

Why The Cloud

It 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. 

In 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…).

Other Cloud Services

OneDrive 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, Google, and many others.

Now, 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 Business World. 

As 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 don’t agree…