Summarizing Your Data

Oftentimes, the simplest solution is the best approach. Let’s say that you have a large Database of Company Clients and the State in which they reside. Since it would likely be interesting to all of the stakeholders in the company to see a Summary of how many Clients you have in Each State, you have decided to use a bit of Excel magic to produce this summation.

If you have read this blog for any length of time, you know that I am a great believer in Named Ranges, so first of all, Name the Range that contains the Clients. This can be done simply by selecting the range, (you can include blank cells not yet filled to allow for future growth), and type the Name of the Range in the Name Box in the upper-left-corner of your worksheet. In this example, we will assume you have named it “State”.

Then you can list the States in the database, and (assuming your first entry is in A2) put in the following formula in the first adjacent cell:

  =Countif(State, A2)

Then it is a simple matter of copying the formula down next to each city and, Presto!, You have your Summary of Clients!

As I like to say, oftentimes the Simple Ways are the Best…

2016 Top Ten Shortcuts

Keyboard Shortcuts are one of my favorite Excel features.  As I have told have told hundreds of Excel students of mine over the years, they can add speed, efficiency, relief from the stress of mouse fatigue. 
 
These shortcuts are very accessible, and you can believe me when I say they will set you apart from the masses of Mediocre Excel Users.

So, in Reverse Order, here is my Current Favorites List (and highly recommended) of Top Ten Excel Keyboard Shortcuts for 2016:

 
10. CTRL+H - Find and Replace (How could you work without it?!)
9.  ALT+F1 - Creates an Instant Chart of the selected data (Super crowd pleaser!)
8.  CTRL+SHIFT+% (Applies the Percentage Format with no decimal places – Cool!)
7.  CTRL+; (Enters the Current Date – I’m a time freak…)
6.  Ctrl + Home (Brings you to the Start of the Worksheet – Oldie, but a Goodie!)
5.  CTRL+9 - Hides the selected rows (Cleans up your worksheet, and it simply Cool!)
4.  SHIFT+TAB - Moves to the Previous Cell (Once you get used to it, you will use it!)
3.  F5 - Brings up the Go TO dialogue box (Navigating to a named range is a snap!)
2.  CTRL+K - Displays the Insert Hyperlink dialog box for new hyperlinks (Helps you mimic websites in your workbooks!)
 

And my Current #1 Favorite Keyboard Shortcut for 2016 is (Drumroll please…):

1.  Shift+F10 – Opens the right-click menu (Incredibly useful when your fingers aren’t on the mouse…)

Give these shortcuts a try sometime.  I am sure that you will be glad you did!

Add Some Pizazz!

Let’s face it, Excel workbooks and charts can be a bit dull.  That does not have to be the case, however!   Here are 4 Super-Easy Ways to bring some Pizazz (Everybody can use a bit of pizazz now and then…) to your masterworks, and help assure that you get the recognition you deserve. 

 1. Change the Color of Your Sheet Tabs

Right click on your worksheet and select “Tab color” option to change the worksheet tab color.  If your workbook has quite a few pages, you may even want to devise a color scheme that aids navigation.

 

2.  Insert a Quick Organization Chart

Not just for corporate settings, these quick-and-easy charts have several uses. Click on the Insert tab on the toolbar and go to SmartArt and choose the “Hierarchy”group.  Pick the Org Chart that best fits your needs.

 3.  Hide the Grid Lines on Your Worksheets

One of my favorites.  Do away with clutter by going to the View tab on the toolbar and Deselecting the box next to Gridlines.  Make your worksheets look clean, professional, and easy to read with this simple step.

 4.  Add a Rounded Border to Your Charts

This is so simple, you’ll laugh.  Just right-click on your chart, select Format Chart option, and choose “Rounded Borders”.  Purely aesthetics, of course, but a quick way to present your work in a way that is more pleasing to the eye.

The important thing to remember is that any of these techniques can be done in the blink of an eye, so why not take advantage of these simple tools and Add a Little Pizazz!

Text to Columns Revisited

A couple of years ago, I did a post on the Text to Columns tool in Excel.  Judging by the number of hits it has received, its popularity bears taking another look.

The Text to Columns tool is particularly worth discussing further, as it is highly useful and often overlooked by even longtime Excel users.

 
As any analyst knows very well, data isn’t always presented in the most ideal formats for use in Excel.  Data may come from a variety of robust data storage sources, or even from such unlikely places as Word documents! 
 
Here is an Example of How the Text to Columns Tool Works:

To illustrate, we will use the following string of numbers (intentionally rudimentary…) that you may find in a Word document or any other numerous sources:

14, 22, 36, 35, 64, 34, 28, 94

1. Select the string, copy it, and paste it into a cell in Excel (in this example, A2 was used)
2. Select the cell and click on the Text to Columns icon on the DATA ribbon
3. The Convert Text to Columns Wizard will appear giving you the following two major options:
• Delimited – Characters such as commas separate each field
• Fixed width – Fields are aligned in columns with spaces between each field
4. In our example, we choose Delimited since our numbers are separated by commas
5. Following the steps in the Text to Columns Wizard gives you the choice to pick your desired format:
• General
• Text
• Date

It’s as Simple as That!  It really is that easy to get your data into Excel in proper alignment and format. Have no doubt about it, Text to Columns is a very worthwhile gadget for any Excel user to have in their tool belt. Give it a Try!