First Names - Last Names

When importing data from sources outside of Excel, it is a common annoyance to find the imported information is not in the format you wish to have it in. For instance, as good database constructors, we know it is highly preferable to have discrete pieces of data in each field.

For example, it is better to have a field with the First Names and a second field with the Last Names of your employees rather than a single field with both. This is a common issue when importing data that is in the format, LastName, FirstName.

As we have explored previously in this blog, there are old favorites, as well as new ways of dissecting your data. For all versions of Excel you can, for instance, use the following formulas:

To extract just the First Name:
=LEFT(A1, FIND(" ", A1, 1)-1)

To extract just the Last Name:
=RIGHT(A1, LEN(A1)-FIND(" ", A1))

If you are using Excel 2013 however, you have the marvelous new Flash Fill gizmo that automatically fills your data when it senses a pattern.  This clever new tool works well with lists of names, numbers, as well as a variety other data.

A Third Way, however, (this works with all versions of Excel) is to use the familiar Find and Replace instruments. Here is how you can do this:

1.    Create/Drag two identical columns of data for the First Name and Last Name next to your imported data field as illustrated below:

 

 

2.    Then select the data in your First Name field, press Ctrl-H to pull up Find and Replace and type in asterisk-comma: ‘*,’ in the Find what textbox (see below):

 

 

3.    To find the Last Name field, simply type asterisk-space-comma: ‘, *’ in the Find what textbox

And there you have it; three excellent ways to Have it Your Way (it doesn’t only apply to hamburgers…)!

Copying a File Directory

Here’s a little timesaver that can save you a lot of unnecessary work.  Over time, the number of files in a segment of your directory can reach a volume that can make it challenging to remember what they each represent.  There are also times which you may wish to email or otherwise note what you have on your hard drive.

You could, of course, manually copy them into an Excel worksheet, but who wants to do that!  There are, happily, easy ways to copy a List of Files into a worksheet.  Here is the first one:

1.    Open the folder on your drive and select all the documents with CTRL-A

2.    To select the files, use the shift button and mouse: Shift+Right-Click and then

3.    Choose Copy as Path from the dropdown menu

4.    Return to your spreadsheet and Right-Click/Paste

5.    Using this approach, you will paste the full path, Including the filename, of each document

6.    Finish up with any Formatting you choose

A more sophisticated way, perhaps, is to use Power Query.  Power Query is a free add-in that you can download from the Microsoft website and automatically install in your 2010 or 2013 version of Excel.

With Power Query, you can:

·        Import data from multiple logs

·        Connect to data across a wide variety of sources

·        Create custom views of your data

·        Perform data cleansing operations

·        Do Online Search for data from a large collection of public data sources

·        Much more…

We will take a closer look at Power Query in a future blog post, but in the meantime, remember, there are Easy Ways to copy a file directory.  And, of course, “Easy” is always a good thing!

Slicing Pivot Tables

A little over a year ago, I discussed Slicers in this blog.  At that time, a review of how Slicers (which debuted in Excel 2010) could be applied to regular tables in Excel.  In a recent class on Pivot Tables, a surge of interest among my students in the topic of Slicers has prompted me to revisit this comparatively new, and very useful, tool.

A Pivot Table is, of course, a fabulous tool in itself.  Combining Slicers into it makes it even better.  The main reasons they are so cool, is because they:

1.   Enable filtering the contents of your pivot table on more than one field

2.   Automatically update as additional data is entered

3.   Are Additive – you can keep applying more filters as you develop the view you are seeking.

4.   Provide an engaging, customizable interface for the users of your workbook (see example below…)

Regarding the 4^th point addressed above, you have the option of reshaping and recoloring your Slicers.  Since they float, you can put them wherever you wish, giving your interactive report a crisp, professional look (how Cool is that!).

How to Add Slicers to Your Pivot Table:

1.   Select one of the cells in your pivot table, and click the Insert Slicer button in the Sort & Filter group of the PivotTable Options ribbon

2.   Select the Check Boxes for the fields that you want to use as filters in your pivot table and click OK

Badda-bing!  That’s all there is to it!  Need a way to spruce up your Pivot Table report?  Give Slicers a try!

Reducing File Size

You say your Excel files have gotten a bit “Fat” and you wish there was an easy way to reduce their size?  You’re in luck, as there are several ways of reducing Excel Bloat!

Decreasing the size of your Excel files can have several benefits.  It can make them easier to email, (and save the ire of your recipients for bogging down their inboxes…), make them open more quickly, and reduce the chance of curious error messages/problems.

Here are just a few ways of putting your Excel files on a diet:

1.   First of all, check to see if a bit of housecleaning is in order: Are there any unused worksheets, irrelevant charts, or redundant calculations in your workbook?  If so, removing them is an obvious quick fix.

2.   Delete any unnecessary formatting.  If you have rows of blank cells that are formatted (color, lines, etc.), doing away with this needless bling can be a help in your efforts to slim down.

3.   Compress any graphics in your file.  That beautiful company logo may look great in your workbook presentation, but it may be hogging unnecessary space.  Just double-click on image, go to Compress Pictures and choose the Email (96ppi) option.  This can make a surprisingly huge difference!

4.   Zip your files.  Although zipping Excel files does not have as much effect as in did years ago, the application WinZip can still help, especially if you are using (as is the case with some companies) Excel 2003.

These techniques are not all-encompassing, but they are a good start if you wish to put your files on a diet (speaking of which, maybe I’ll have a salad for lunch…).