Excel data cleanup with Flash Fill (2013) or Word (2010 or below)

Read the article, Answer the Quiz,
Win Prize (10 USD Amazon voucher)

Problem

One of the participants (Mr. Amjad) in a seminar I conducted asked me this question.
The scenario is common enough.

There is data in one column which contains name followed by amount. We want to split the amount.

flash fill

Doing it using a formula is tricky. Because in some cases there are two spaces, in some cases there is only one space. And may be if the data is large, there could be three spaces as well.

Easy solution: Excel 2013 – Flash Fill

This is an amazing new feature. I will cover it in detail in a separate article. However, here is the way it works.

In column E, specify what you want. Just type the value. NO formula.

flash fill

Then choose Data tab – Flash Fill

flash fill

Do the same thing for the name in column F. That’s it. Excel has an amazing pattern matching engine which does the job for you.

flash fill

What if you don’t have Excel 2013? Use Word!

Then you will have to resort to complex formulas or VBA. But not really. We often get too focused on the product and forget that Office has other tools as well.

Remember. Excel is great with Numbers and Dates. But not text.

Who is the text expert? Word Logo

In fact what Excel 2013 Flash Fill does is technically called pattern matching (Regular Experssions). But Word had this feature since many years.

Let us see how to solve complex Excel data handling problems in Word.

  1. Copy the column data and paste it in Word
  2. It will be pasted as a table. But the table borders will not be visible.
  3. Click inside the pasted data, you will see the Table Tools tab
  4. Click the Layout tab and choose View Gridlines
    Now you can clearly see the tableimage
  5. We want to select only the numbers
  6. Press Ctrl H to open the Find – Replace Dialog
  7. We don’t want to replace anything so click the Find tab
  8. In the Find textbox, type this exactly as shown here [0-9]{1,}
  9. Click the More>> button and choose Use Wildcards optionimage
  10. Now try Find Next multiple times. Notice that it is selecting all the numbers one by one
  11. But we want to select all numbers. So open the Find In dropdown and choose Main Document
  12. Now all the numbers will be selected. Close the Find dialog.image
  13. Press CTRL X to cut the numbers and put them in clipboard
  14. Only the names are left in the first column.
  15. Now add a new column.
  16. Select the second column
  17. Press CTRL V to paste the numbers
  18. Now select the whole table and paste it into Excel
  19. Job done!

For the sake of completeness, the names column will have an extra space at the end. Add a new column and use the TRIM function to remove the trailing spaces if required.

Sample Word file for practice Download

How did this work?

The magic happened in that complex looking search criteria

[0-9]{1,}

Wildcard option means do not search what is typed literally. Try to interpret it.

[0-9] Means search for any number between the given range. But that would select only one digit.

{1,} Means that Word should search for one or more instances of number. That is how it selects the entire number

Want to know more?

Click in the Find textbox in Word, enable Wildcards and click the Special button.

You will see the entire syntax of what is possible. This is infinitely powerful and easy to use once you understand the concept. Try it out.

Character in Range

Quiz and Prize

Efficiency365 Quiz

Download this Word file

When you open it, you will notice that it has many names.
Usually names should be shown as spelling errors with red wavy line below it.

Tell me why there are no spelling mistakes shown and win the prize.

Post your answer as comments. Make sure your email id is mentioned.
The first person to post the right answer gets the prize.

Arrange multiple pictures in PowerPoint in Picture Tools

LifeLog

Yesterday, I conducted sessions for 650+ professionals at a leading automotive manufacturing company… Very smart audience, amazing grasping power.

image

Here is one of the demos I used in the session…

Problem

I have multiple pictures. I want to put them into a SmartArt diagram quickly. But if I use Insert – SmartArt – Picture SmartArt, I have to insert each picture one by one.

That is repetitive. Repetition = Inefficiency Sad smile

Is there a faster, smarter way?

Solution

Of course there is. Here are the steps.

  1. Choose Insert – Pictures
  2. Select all pictures and add them
  3. As soon as the pictures are inserted, they are already selected.
  4. DO NOT unselect them.image
  5. Notice that pictures are of different sizes and randomly arranged.
  6. While all pictures are selected, click the Picture Tools tab
  7. Open the Picture Layout dropdown
  8. Notice that all types of Picture SmartArt appear herePowerPoint Picture Tools
  9. Choose the one you want
  10. The size of the diagram will be determined by the size of the picture selection
  11. Adjust the diagram size as neededimage
  12. Open the text editor and add titles if required
  13. That’s it!

From Boring to Exciting – Use PowerPoint SmartArt

In an  earlier post, I explained how to convert a Word document to a PowerPoint presentation in one click (ok… few clicks!). Unfortunately, the resulting presentation is guaranteed to put your audience to sleep. The presentation contains only text. And lots of bullets. Everyone in the world knows that …

PowerPoint SmartArt turns boring presentation into exciting Most presentations are boring! Still we contribute to that boredom by creating more boring presentations. If you want to succeed in your presentations, you should strive to be in the 1% image There are thousands of ways to make your presentation less sleep inducing. But one method stands out. Which is quick and easy.

SmartArt.

Convert those boring bullets into interesting and easy-to-understand diagrams.

Boredom to excitement… in few clicks

  1. Right click in your bulleted text and choose Convert to SmartArt.
  2. PowerPoint shows you 20 diagrams to choose from.
  3. Move the mouse cursor over each diagram – it is temporarily drawn … move to next till you find the most suitable diagram to explain your concept.
  4. Remember that most people don’t do this. So you are already in the 5%
  5. But you don’t want to stop there. You want to be exclusive…
  6. So have the courage of clicking on More SmartArt Graphics… (nobody has that courage… so that is your competitive advantage)
  7. Now you will have a big problem

135 diagrams to choose from!

Don’t get disheartened. In a given business context, only one or two of these diagrams are most suited. The real skill is finding the right diagram for your need. Unfortunately, there is no easy way there. You have to put effort.

Homework: Learn 135 diagrams

Spend 30 minutes of your precious life to do this:

  1. Create a new blank presentation
  2. Create a new slide (usually it will be Title and Text type of slide)
  3. Open Insert tab and choose SmartArt
  4. Look at the categories on the left. These will tell you overall categories of diagrams from a business point of view.
  5. Click on each category. Then diagrams of that category are shown.
  6. Now click on each diagram.
  7. On the right side, a bigger version of that diagram is shown. Just imbibe that visually.
  8. Look below… Microsoft has taken the trouble to explain the business context in which the diagram should be used. Read that.
  9. Move to the next diagram.

Spend image minutes of your precious life and see the difference! To stimulate your thought… here are some nice diagrams.

Risk Management

Risk Management SmartArt

Pricing is determined by demand and supply

Supply and Demand SmartArt

Input and Output

image

Colors, Styles, Animation and Effect options

Try all options on the SmartArt Tabs – Design and Formatting to know more about what you can do. Also apply some simple animation and choose Effect options to see interesting ways in which you can control the animation.

How to get full control?

SmartArt is very nice. But you have one limitation. You cannot change one item independent of others, you cannot group items and so on. Don’t worry. Use SmartArt first to create a base diagram quickly. Then click Design tab and Choose Convert to Shapes. Now it is no longer a SmartArt diagram. It becomes a simple diagram with grouped objects and shapes. Now you can ungroup it and control each shape exactly the way you want. Now you get best of both worlds. Quick diagram and full control.

Homework 2

Take any of your existing presentations. Save it as a copy. Look at existing verbose slides and see if you can improve them using appropriate usage of SmartArt

Pending issue

Creating Picture SmartArt is not easy. You insert the SmartArt first and then you have to choose one picture at a time, repeatedly. And as you know, repetition means inefficiency. In the next post we will see how to create SmartArt containing many pictures (of dissimilar sizes) in two simple steps. Another miracle coming up…

9 benefits of using Excel Tables

If I have to teach ONE thing to the world, it would be Excel Tables.

Excel tables

I recently created this video for a Microsoft Webcast.
Excel Tables were introduced in Office 2007.
This video explains the importance of using Excel Tables.

Prevent Wrong Decisions using Excel Tables

Outdated formulas in Excel are a big operational risk

Tables eliminate that risk proactively.

Additional Benefits of Excel Tables

This in itself can become a series. But here is a short description of the benefits

Auto-update of formulas and references

All dependent formulas are auto-updated across sheets. If you have a linked file, the file must be open for the auto-update to work.

Automatic formatting of tabular data

If you have manual formatting applied, the Table formatting cannot override it. To override, right click on the desired format and choose Apply and Clear Formatting option.

Automatic copying of calculated column formulas

Any formula added in new columns in the table is auto-copied across the table. The formula is copied all the way to the end of the table even if the left column has empty cells.

This saves you the trouble of using time consuming DRAG or error prone DOUBLE CLICK for copying formulas.

If you don’t like this behavior then use the SmartTag to UNDO the auto-copy action.

Automatic copying of calculations when more data is added

This is very useful. If more data is added, calculated columns are auto-filled.

To take advantage of this feature, make sure that you add calculated columns at the end of the raw data. This way, when you get more raw data, you can simply paste it at the bottom of the Table.

Automatic display of headings

Headings are shown in place of regular column headings. It also offers Auto-Filter dropdown (2010 onwards). This eliminates the need for Split and Freeze Pane.

Automatic copying of formatting, conditional formatting and validations

All attributes of cells are auto-copied when you add more rows in a Table.

Ability to use Table and Column names in formulas

Ability to integrate with SharePoint and Visio

You can upload an Excel table to a SharePoint site. It automatically creates a list, creates relevant columns, uploads the data to SharePoint and finally creates a one way (SharePoint to Excel) link with the data.

In Visio you can create a visualization based upon a Visio based Pivot Table functionality.

Create a presentation in One Click with Send to PowerPoint

Send to PowerPoint is an amazing feature of Word.  It has been available for many years but nobody noticed it. So Microsoft removed it from the menus in Office 2007. You need to add this menu back into your Word ribbon first.

If you are using Office 2003 or older, just go to File – Send to – Send to PowerPoint.

How to enable Send to PowerPoint feature in Word

  1. Start Word
  2. Right click on the ribbon
  3. Choose Customize Quick Access Toolbar
  4. You will see two lists. From the left side, open the dropdown which is showing
    Popular Commands. Choose All Commands
  5. Click in the list below. This shows all Word commands (buttons). This is a very long list.
  6. Type character s
  7. This will take you to the list of commands starting with character s
  8. Now press Page Down multiple times till you locate the command Send to PowerPoint
  9. Click the Add button then click Ok

Now you will have the Send to PowerPoint button in your Quick Access Toolbar.

Word to PowerPoint in ONE click

Create or open any Word document which is using styles.

image

Click on the Send to PowerPoint button.
The button looks a little different depending upon your version of Office.

Send to PowerPoint

PowerPoint will automatically create a presentation for you. Every item with Heading 1 style becomes a new slide. All other heading levels below it become bullets.

Pictures are not transferred to PowerPoint even if they have Heading style applied.

image

Few more steps…

  1. As soon as this presentation is created, go to Slide Sorter view.
  2. Press CTRL A to select all slides.
  3. From Home tab, click the Reset button.
  4. Now you can apply any other design template and use the presentation OR
  5. Copy the slides into another presentation

image

This step is required to remove the unwanted formatting which was transferred from Word to PowerPoint. If you do not perform this formatting reset operation, applying other design templates will not work properly.

Be stylish – Use Word Styles

Thank you for responding the vote in the previous article on 3 Efficiency Best Practices. In this article, we explore Word Styles in greater detail. Read this even if you are using Word Styles.

word styles

Picture created using Fresh Paint on Surface Pro with finger painting.

Problem: We spend too much time on formatting and too little on the core content.

Solution: Use Word Styles

Styles are just ready to use formatting which is created by graphics experts. There are 9 styles – Heading 1 to Heading 9

While creating a document, you know which of the paragraphs are Headings or Topics or Titles. As soon as you type the paragraph, choose the style.

Word styles

Main topics get H1, sub topics get H2 and so on.

How to select styles quickly

  1. Click the style in the Home tab – Styles drop down (it is called a Gallery).
  2. Or use keyboard shortcuts CTRL ALT 1 for H1 CTRL ALT 2 for H2 and
    CTRL ALT 3 for H3
  3. Or use the shortcut Shift Alt Up arrow to apply Higher styles, Shift Alt Down arrow to apply lower styles

Default styles in Word 2013

This how the default styles look. If you want to change the appearance to match your corporate branding guidelines, Right Click on each style, Choose Modify and change the look and feel.

Default styles in Word 2013

Customized styles affect only the current document by default. If you want all further documents to have the customized styles, choose the following option in the Modify dialog.

Customized styles in Word

Change the look and feel instantly using Themes

Choose Page Layout tab (2007,2010) or Design tab (2013) and open the Themes dropdown. Just move the mouse cursor over each theme and see how the document reformats instantly with different color and font combinations.

If you find some combination which resembles your brand colors, it is the easiest way of customizing document look and feel for corporate branding. Remember to use the same theme in Excel and PowerPoint as well.

What are the benefits of using Word Styles?

The main benefit is that Word knows what are the main and sub-topics in your document. Word uses this information intelligently to provide you with many useful features.

Navigation Pane

Choose View tab – Navigation Pane. Now a Separate window opens on the left side and shows you the document hierarchy. This never gets printed. This is your on-screen navigator.

Navigation Pane in Word

  1. Just click on the item to navigate to that area. No more scrolling and searching.
  2. Reorganize the document (and your thoughts) by just using drag-drop.
    (Amazing! is it not?)
  3. Right click on any heading and you get many useful options. Explore and find out.
    The Show Heading levels option is very useful for reading complex documents. Just show level 1 first and then drill down into the desired area quickly.

image

Sample file for practice

This file contains styles and TOC Download Style Document

Automatic Table of Contents

Now that word knows your topics and it anyway knows the page numbers, it can put these together and create a TOC automatically.

Go to the place in the beginning of the document where you want to create the TOC and Choose References tab and choose Automatic Table of Contents

Automatic table of contents

By default three levels of headings are added to the TOC along with page numbers. You can change the settings from the Custom Table of Contents option.

Remember to update the table before printing because the page numbers are not automatically updated when you edit the document. Right click on the TOC and choose Update Field or click inside the TOC and choose Update Table option.

Choose the second option.

Update table of contents

For very large and complex documents you can choose the first option (which is faster) when you are sure that you have not added any new headings. In most cases using the second option is safer.

Retrofitting styles into non-style documents

You will love Styles once you start using them. But then you will feel very sad that your older documents are not getting all these benefits of styles. But don’t lose heart. Microsoft has thought of that as well.

Open a document which is using manual formatting. You need to find all areas where manual formatting indicates various levels of headings and then apply the correct Heading Style. But this is going to be very time consuming if you want to do it one by one. Repetition means inefficiency.

Solution: Select text with similar formatting

  1. Click in one of the manually formatting heading. Choose Home tab – Select dropdown – Select text with similar formatting

Select text with similar formatting
Don’t worry if it shows (No Data). It works anyway.

  • Now Word finds and selects similar headings. You just have to apply Heading 1 once
  • If you have three levels of headings, you will have to do this thrice. Absolutely worth it because now you get all the benefits of Styles

Next article

Styles have many more benefits. One very useful feature is the ability to convert a Word document to a presentation. I will cover that in the next article.

Top 3 Efficiency Habits: Use Word Styles, Excel Tables and PowerPoint SmartArt

Office has thousands of features. And contrary to popular belief, all of them are useful to everyone under certain circumstances. Learning these things is not complex but time consuming.

My approach is to help you learn these things in a simple and intuitive manner. In future articles I will show you some easy methods of learning Office while you work.

But let us start with three very important things you must know.

No negotiation. No option. Absolutely Mandatory. Trust me on this!

Word Styles, Excel Tables and PowerPoint SmartArt.

I would like to know if you already use these features. Please vote. Just three clicks!

Word Styles

Problem

We spend too much time on formatting and too little on the core content.

Solution: Let Word handle the formatting

Don’t do formatting manually. Word is better than you. That is why it is called a word-processor. Let Word handle the formatting.

Most common formatting is to show titles and subtitles in a document. Word has already created formatting for up to 9 levels of topics (headings). Just use it.

How? By choosing the appropriate style.

Styles are just ready to use formatting which is created by graphics experts. There are 9 styles – Heading 1 to Heading 9

While creating a document, you know which of the paragraphs are Headings or Topics or Titles. As soon as you type the paragraph, choose the style.

word styles, excel tables and powerpoint smartart

Main topics get H1, sub topics get H2 and so on.

How to select styles quickly

  1. Click the style in the Home tab – Styles drop down (it is called a Gallery).
  2. Or use keyboard shortcuts CTRL ALT 1 for H1 CTRL ALT 2 for H2 and
    CTRL ALT 3 for H3
  3. Or use the shortcut Shift Alt Up arrow to apply Higher styles, Shift Alt Down arrow to apply lower styles

Benefits

  1. Automatic table of contents
  2. Live navigation pane
  3. Ability to create PowerPoint presentation automatically
  4. Automatic numbering
  5. Ability to rearrange the document by just drag drop of headings
  6. Expand / Collapse headings to make long documents easier to manage (2013 only)
  7. and many more

We will cover these benefits in the upcoming articles.

Excel Tables

Problem

Excel is a very dangerous piece of software. We use complex Excel files to take critical decisions – personally as well as at organizational level.

All of us know that Excel contains two primary things. Data and Formulas (Charts are visual formulas, Pivot Tables are automated formulas)

When you add a formula Excel calculates the results. Usually we do not cross check the results for every formula. Which is very dangerous.

Why? Because, when you add more data, you must update all the related formulas. Often we forget to do that. Due to this, the formulas may be outdated but you or others may not notice that. If you trust a formula which is showing outdated results, your decisions will be wrong.

Solution? Excel Tables

I will explain it in the upcoming articles. But to cut a long story short, Excel tables automate the process of updating formulas when you add more data.

If there is an outdated result, Excel shows an error marker (Green mark on top left corner). Unfortunately, most people in the world have never bothered to find out the meaning of that poor little green mark. We simply ignore it and take the risk of looking at wrong information.

Excel tables

Benefits

  1. The primary benefit is auto-update of formulas and references
  2. Automatic formatting of tabular data
  3. Automatic copying of calculated column formulas
  4. Automatic copying of calculations when more data is added
  5. Automatic display of headings
  6. Automatic copying of formatting, conditional formatting and validations
  7. Ability to use Table and Column names in formulas
  8. Ability to integrate with SharePoint and Visio

PowerPoint SmartArt

The purpose of PowerPoint is to present your case to the audience to achieve the desired impact. The impact could be sales, training, convincing, debating, explaining, and so on.

Problem

Most presentations are boring and uninteresting. If your audience loses interest in what you are showing or if they cannot understand it clearly, your objective will not be achieved.

There are thousands of ways of making better presentations. But one of them stands out.

Solution: SmartArt

That is called SmartArt. It is a very quick way of converting text (bullets) to appealing and high-impact visuals (diagrams). PowerPoint offers around 130 diagrams – intelligently created to help us depict various type of information (Lists, Processes, Hierarchy, Relationships and so on).

Creating a SmartArt Diagram is very easy. But choosing the right one in the right place does take some amount of practice.

SmartArt

Benefits

  1. Easy to create visually appealing diagrams
  2. Automatic formatting and resizing
  3. Eliminates the need for manual alignment, formatting and animation
  4. Automatic diagram creation from multiple pictures of different sizes

PowerPoint SmartArts

We will cover SmartArt in detail in upcoming articles.