PowerPoint: How to Copy paste slides with different design templates

Here is a simple but very useful feature for you.

The problem

We often copy slides from one presentation to another. Each presentation usually has a different design template.

When you paste a slide into the destination presentation, it takes on the appearance of the local template. Usually this spoils the original slide thoroughly.

Now you get very frustrated and try to repair the damage manually by trying to format each element of the slide.

Source

image

Destination

image

After copying to destination

image

The solution

It is much simpler than you think.

Look for that ever helpful icon (it is called SmartTag for a reason!).

Click on it and choose Keep Source Formatting.

Copy paste slides with different design templates with Keep Source Formatting

image

That’s it.

image

What really happened?

Your destination presentation already had a presentation Master (with multiple layouts). You pasted a slide from another presentation and chose Keep Source Formatting. Now the master slide and layouts from original presentation were copied into the destination presentation.

Open the Design tab – open the dropdown – now you can see both the masters.

image

Applying any design to any slide

Now that you have multiple designs (templates) available in the same presentation, you can use any design for any slide.

Just choose the slide(s), Right click on the desired design and choose Apply to selected slides

image

image

Be careful here. If you click on the design directly, it will apply to ALL slides!

Use PowerPivot instead of VLOOKUP

This time I wanted to try a new style of writing.

I created a presentation instead of the usual document like style.

PowerPivot vs Vlookup

Download presentation and view

Download the presentation (300kb) from here

Download sample file with data and Pivot from here

View on SlideShare

Tell me if you like this style…

Introducing PowerPivot – do you need it?

If you use Pivot Table, then you need PowerPivot! Read on to find out why.

If you have never used Pivot Table, you should not read this article. You must learn Pivot Table first.

PowerPivot is an add-in which works on Excel 2010 / 2013 (Professional Editions only). For 2010 you have to download and install it from PowerPivot.com

For 2013, it is a part of the base installation. However you still have to activate it. File – Options – Add-Ins – Open Dropdown – COM Add-ins – GO. Enable the checkboxes next to PowerPivot, PowerView and Inquire. We will discuss the other two items later. This time we focus on why PowerPivot.

Finally you should see a PowerPivot tab in Excel.

PowerPivot tab

What is PowerPivot

It is a new way of creating Pivot Tables for analyzing data. It eliminates all the shortcomings or limitations of traditional Pivot Tables… and adds more features.

Limitations of the good old Pivot Table

Large data = slow performance

Pivot Tables cannot handle very large amount of data. Even with few hundred thousand rows it starts to become slower and slower. Often you have to wait for a long time for a drag and drop of a column to finish all the calculations. Opening such large files is also extremely slow.

Row limit of Excel sheet

Maximum of  1,048,576 rows… just over 1 million. That is the limit. If your data is bigger than this, you must use some external database and then connect Excel to it. Of course, even if it works the usage will be extremely slow.

In addition, we have dependence on the external database – which makes sharing the files more difficult.

Pivot Table works on one tabular block of data

If your data is originating in multiple blocks of information, Pivot Table cannot handle it.

For example, you have transactional data of 100 rows containing the columns
Product ID, Qty, Price, Total Cost

You also have a master table containing Product Id and Product Name.

Pivot Table cannot be told to use data from the Transaction table along with the Product Master.

The workaround is to use VLOOKUP…

The VLOOKUP problem

That brings us to the next limitation of Pivot Table. Continuing with the example above, we will have to add a new column in the transaction table which uses VLOOKUP to lookup the product ID from the Product Master table and get the value of Product Name.

VLOOKUP function is now 10,000 times making the workbook even more slow to respond. Furthermore, there are only a few products. However the name of each product repeats thousands of times in the transaction table – leading to increased file size reducing the performance further.

It is not uncommon to have multiple VLOOKUP based columns in a single Pivot Table.

Yes, there is a workaround – Copy paste the VLOOKUP column as values immediately after the formula is pasted. This way we retain the desired output but eliminate the overhead of VLOOKUP. Although this works, when data changes (more data is added) you have to add more VLOOKUPs manually and then convert them again to values.

This is repetitive and cumbersome as well as error prone.

Either flat data or cube data

Many people have not explored Pivot Table as a front end to an SQL SERVER cube. Pivot Table integrates beautifully with SQL server cubes and exposes all features of the data warehouse elegantly. However, there is a limitation.

You cannot combine some data from the cube along with some data which is outside the cube. In practice, this type of requirement is common.

The solution: PowerPivot

PowerPivot is designed to solve all these problems and more – much more.

Here is the concept.

Stores data inside PowerPivot. Not Excel worksheets

Whether data originates in Excel or other places, PowerPivot stores it. Data is NOT stored in regular Excel sheets at all. This way, the storage mechanism can be made more elegant, fast and scalable.

Diverse data sources

Powerpivot Table Import Wizard

It can create a Pivot Table using data originating from different sources. For example, transactions data can originate in a database whereas the Product Master could be a simple Excel table.

Handles millions of rows of data without inflating the file size

PowerPivot can store millions of rows of data. Of course we are worried about file size bloating up. But don’t worry. It stores it in a highly compressed manner. You can actually try this out. Take a large Excel file with data contained in a worksheet. Note the file size. Now import the same data into PowerPivot, save the file and check out the difference. You will be very surprised. The file size can reduce anywhere between 4 to 100 times depending upon the pattern of the data (compressibility).

Eliminates VLOOKUPs by creating relationships

Excel is not a database. We were using VLOOKUP as a workaround to create relationships. PowerPivot – like a database – allows you to specify which field in the transaction file relates to (is same as) the field in the master file. This type of MANY-to-ONE relationship eliminates the need for VLOOKUP and associated performance penalty.

Powerpivot Create Relationship

More functions

As though this was not enough, PowerPivot adds more functionality which is completely missing in Excel.

    1. Ability to create hierarchies from flat data (Category, Group, Product, SKU)
    2. Ability to use Picture URLS (for use in PowerView)
    3. Creating Flat Pivot Tables
    4. 70+ new functions which extend the capability of Excel to something similar to sophisticated cube based formulas (without the need to learn complex MDX syntax)
    5. Time intelligence functions are very powerful and extremely useful
    6. Ability to merge cube and flat data
    7. Ability to refresh all data sources
    8. Calculated columns for additional customization
    9. Default field sets, and many more…

In the next article we will see how VLOOKUP can be eliminated using PowerPivot.

You are suffering from “Inattentional Blindness”

inattentional blindness

What is that???

You are sitting in a conference room attending an interesting presentation. Unfortunately, there is an irritating, extremely obnoxious black mark on the projection screen… created by some genius who used a permanent marker on a projection screen… (you will never know his/her name). That black mark is interfering with the presentation…. What happens next?

Very soon, your stop noticing the black mark and start focusing on the presentation.

Technically, your eye is viewing the unwanted black mark. But practically, it is simply not aware of it. That is called Inattentional Blindness.

This “blindness” or automatic masking is not limited to vision. Any input can be masked if you are not interested in it.

So what?

Every day we see Office tools. Each one of them has hundreds of buttons and options. Do we see all of them? Yes – the light reflected from them is reaching your retina.

But do we notice and understand them? Absolutely not. Because of Inattentional Blindness.

Everyone suffers from it. Nothing wrong with it if the items you ignored were irrelevant and useless.

What you know you see. Everything else is blurred.

Unfortunately, what you are ignoring is immensely useful to you.

There are three things which we don’t notice even if we are using the button associated with it.

Dropdowns and Split buttons

Here is the button to create a New Slide. All of us use it. But we miss the dropdown below it. This is a dual function button. If you want a slide with the previously used layout, click on the New button directly (upper part).

image

If you want to choose the type of layout for the next slide, then open the dropdown (click on the lower portion) and choose.

image

image

Dialog Launchers

These small little buttons at the corner of a group invoke related dialogs which provide more options. We never notice them.

image

Nearby buttons

Buttons near to the ones you are using are obviously related to the task at hand. So it is a good idea to notice them and ponder about what they do. Stay on them for just one second to view the tooltip which shows what they do.

SNAGHTML2d1f3744

Cure for inattentional blindness

Little bit of curiosity and exploration is all that is required to widen your sensory horizon and become more efficient!

Test your efficiency. Try any of these simple tests. Each test asks you how to do a particular activity. In ALL these cases, you will already know the answer. But is that the most efficient way? Check the most optimal method and learn from it.

***

How to edit a formula in Excel

Yes I know you have been using formulas in Excel for decades. But just trust me… there is more. Here are some small but nice things you should know when it comes to editing a formula in Excel

Keep looking at the status bar

It shows you the current mode. First time you add a formula, it is in ENTER mode… next time you try to change it, the mode changes to EDIT.

SNAGHTML17534ab2

So what difference? In ENTER mode, moving the arrow keys selects cells outside the current cell. This way you can select the range for formula parameters.

In Edit mode, moving the arrow keys (right and left), moves the cursor WITHIN the formula cell – which helps you in editing the formula itself.

In some cases, you may want to change the mode. To do that press SNAGHTML175594c1

This is also very useful when you edit formulas in odd places – like Conditional Formatting formulas, Data validation formulas and so on… In these places, the default mode is Enter. You want to change something in the formula so you press arrow keys but it selects cells and disturbs the existing formula – which is very frustrating. In such cases, just press SNAGHTML175594c1[5]before editing and make sure that you are in EDIT mode before editing.

Syntax tooltip – why bold formatting?

All of us know that the formula syntax is shown whenever you are editing a function. But wait. Have you noticed some additional things there? Some of the parameters are in BOLD font. Do you know why?

image

Because your cursor is currently in that position. This is very useful in editing complex, nested formulas. It is sort of “situational awareness” while editing formulas.

Syntax tooltip – why Hyperlinks?

I am sure you have noticed it…

image

What is the use? Well, it helps you select the entire parameter by clicking the link Thumbs up

image

This can be very useful in selecting a particular parameter in complex, nested formulas…

how to edit a formula in Excel

Imagine doing this manually Ghost

Whatever is selected can be calculated with SNAGHTML17672b22

If you select part of the formula and press SNAGHTML17672b22[5], it calculates it and shows the results.

image

This is very useful for troubleshooting and quickly knowing the results of part of the formula.

REMEMBER NOT TO PRESS ENTER HERE. Else your formula will be overwritten with the result!

There is much more. But enough for one article already …

Office Efficiency: How to learn each time you UNDO

This is a short article. It is just a new thought which you have to apply in your actual work…

A good use of UNDO is to correct some mistake.

But often we perform some activity expecting some specific thing to happen. Unfortunately something else happens and then we get frustrated. To repair the damage, we press UNDO. That is called misuse of Undo.

undo shortcut

Undo mean you don’t know how to do!

So today is the day where you will start noticing each UNDO action and think… what went wrong? Can I perform the activity in a little different way so that I can get what I want?

In short we are going to convert every UNDO into a Learning Opportunity Smile

One good thing is to keep looking for and clicking on these icons which crop up when something apparently goes wrong.

Undo

These icons provide you with all the actions available in that situation – which should help you get what you wanted.

Just try it and let me know how much you learnt!

How to search scanned documents with OneNote OCR

The Problem

Legal documents are archived by scanning. Scanned documents just contain an image for each page. Therefor it is impossible to search for specific text.

That means you have to spend lot of time groping in the dark trying to find a small paragraph in long documents.

It is possible to make searchable PDFs using Adobe Acrobat as well as many other third party applications. However, most documents are usually scanned as just standard images.

Don’t worry. There is an solution available. In fact you may have had the solution on your PC for upto 10 years.. but you may not have known it.

The solution is OneNote OCR – a part of Microsoft Office since 2007.

How to check if you have OneNote?

Press Windows key with R key. Run dialog will open.

Type onenote and press Enter.

It it is installed it will open.

By the way, this method can be used to run other Office tools and other Windows applications as well. Here are the exact phrases winword, excel, powerpnt, outlook, mspub, paint, …

Using OneNote to search a scanned document

Open the PDF file containing the scan

image

Choose File – Print – Send to OneNote (The version number ).
Choose the desired notebook. For testing purpose just choose the current notebook.

It will print the pages to a new page in OneNote. Each page from the scanned document will be one image in the OneNote page.

Now press Ctrl F to go to Find on Page

search for text inside scanned documents with OneNote OCR

Type search text and see what happens…

OneNote highlights the words which start with the characters you are searching for. This is very helpful in finding various word forms. The up and down arrows allow you to navigate instances of search results quickly. Of course, you can also scroll manually and check visually.

Want the text?

Of course you will want it sooner or later. So don’t worry. That is also available.

Right click on any page image and choose if you want it from that page or all pages (thoughtful feature… is it not? That is called User Focus).

Now you can paste it anywhere.

Of course the recognition depends upon scan quality.

Practical considerations

  • The page must be in vertical layout
  • Slanted text is difficult to recognize so rotate the image as required
  • OCR is available in multiple languages, depending upon which Office language packs are installed.
  • In case of multi lingual text, you can choose the OCR language by right clicking on the image
  • Handwriting can also be recognized if it is clear and legible. Like text, handwriting also needs to be horizontal. Tilted handwriting is more difficult to recognize

Working with Excel data? Learn Power BI

The need

Office is a funny product. Everyone thinks they know it. What is the definition of “Knowing” Office? = “I know how to get my work done”

Unfortunately, even if you get the work done, it may not be in the most efficient way. Data handling in Excel is probably the single most important drain on human effort everyday.

Billions of people who have the false sense of knowledge use the same old, bad, slow, contorted methods of working with text files, exported reports and spend their precious life cleaning up data, removing unwanted headers, unpivoting data manually, copying and pasting… it is a global pity.

The primary problem? Once you found ONE method which works, you never attempted to find out if there is another, better method available.

Over time Microsoft has been adding many new ways to simplify things and eliminate manual work.

But now, there is a revolution happening. Power BI tools from Microsoft.

All that I am talking about here requires Excel 2013 Professional edition. But don’t worry. You can actually sign up for a trial version for free. See below.

Why do we need it?

The way we work with data has not changed at the core.

image

Ideally we should be spending maximum time in the analyze phase – because that is where we learn from what happened. That knowledge will help us in improving the future.

Unfortunately, we spend too much time in getting data, cleaning it up and summarizing it.

Now Microsoft has added a set of tools to Excel to simplify and automate many of these useless tasks.

Getting data from various sources is now done with Power Query. It also helps you in cleaning up the data, merging / splitting, adding calculated columns, creating relationships and so on. Data can be retrieved (and periodically refreshed) from a variety of sources.

Power BI - Power Query

Don’t worry. The good old ODBC / OLEDB, Text and Excel also works!

Excel 2013 also adds a great feature called Flash Fill to help us clean data instantly – without formulas or manual work. Although this is not a part of PowerQuery, it is a very powerful feature. I would have named it Power Data Cleanser Smile

Once data is available and clean, it is NOT stored in Excel sheets – because there is a limit on number of rows. Furthermore, large data in Excel inflates the file size and makes it excruciatingly slow to work on it.

Therefore, the clean data is stored in a new powerful friend – PowerPivot. It can store millions of rows of data and still have very small file size.

SNAGHTMLc9e9125

Now what we have is called a Data Model. It is time to summarize data. This can be done in four ways.

  1. Traditional Pivot Table and Pivot Chart
  2. PowerPivot Pivot Table and Pivot Chart
  3. Power View
  4. Power Map

Traditional pivot table can still be created with this data and used to summarize the data. We are already familiar with it so no re-learning required. You still benefit from the ability to created relationships (eliminating the VLOOKUP trap) and millions of rows of data handling.

PowerPivot also creates Pivot Tables in Excel. These behave like the good old Pivot Tables but these provide new functions and features which are simply not available in traditional Excel. Many complex calculations which would otherwise require you to approach IT to create a data warehouse (Cube using a BI Tool) can now be done with these powerful Excel formulas. Technical name for these new formulas is DAX.

image

Power View is an entirely new way of summarizing and visualizing data. This gives you s blank page to start with. You just drag drop the fields you want and choose how they are represented (tabular, chart, map, etc). In the process you create a dashboard quickly. Every visual element acts like a filter giving you unimaginable level of analytical capability.

image

Power View also supports maps. Using Bing Maps, your data can be shown on a map for better understanding. This works right within Excel – no special software required.

Power Map is a more evolved version of mapping functionality – a free add-in for Excel 2013. This allows multiple layers, different visualizations and even creation of an animated step by step video to show your plan of action and to present data in a serial manner.

image

Ask queries in English language

As though all this was not enough, you have a miracle feature. You can actually type a query in English, like “Show sales qty for each region as column chart” and it actually does the job. You have to see it to believe it.

If you have been in IT for long enough, you will remember that there was English Query in earlier versions of SQL Server. Later Microsoft dropped the feature. Now it is back with a bang. It works even with simple Excel data with no vocabulary or synonym configuration!

Reporting and Dashboards

Creating reports is one thing. Next step is to share it with others. This is best done using SharePoint. It offers a special app for PowerBI reports. Reports can be seen and interacted with on any browser (HTML 5 version coming soon. Currently requires Silverlight) and any device or tablet (Microsoft, Android, iPAD, iPhone).

image

Try it yourself

GO to the Power BI Site and sign up for a trial subscription. It gives you everything you need… Full version of Office 2013, SharePoint and PowerBI app for SharePoint (full Office 365).

Worried that your existing Office installation of older version will be affected by the new version? No worries. Even that has changed now. The installation of Office 2013 can work in parallel with older versions of Office. Both work independently without affecting each other. This is called appV (application virtualization). The deployment method is called Click To Run.

We will cover more of Power BI in upcoming articles.

Update (Aug 2016): I’ve just released my first Power BI MOOC on Udemy. See details here.  Use discount code AUTUMN40 to get the course at 20% discount. 

How to turn off Compatibility Mode

Whenever you open a file in Office, check if you see “Compatibility Mode” in the title bar…

Compatibility Mode

In most cases you may not even see the XLS extension.

Compatibility mode is your enemy

Do you know the meaning of Compatibility Mode?

It is bad news! It means…

You are using a new version of Office but you have opened an older version file. Therefore, Office is downgrading itself and behaving as though it was Office 2003!

Did you know that?

In short, you are not going to get most of the new features of the new version. For example, even if you have Excel 2013, you will get only 65000 rows, not 1 million rows. Like this, hundreds of new features will either be disabled or work partially in compatibility mode.

You or your company spent money to buy the latest version… but you are not going to get any returns from that investment Sad smile

Solution is simple

Step 1: Notice the Compatibility Mode problem

This is easy. As soon as you open a file – check if it is showing compatibility mode. Even if file extension is not shown, compatibility mode will be shown for older files.

Step 2: Convert the file to new format

Think a little and confirm that the file is safe to convert. (Read below)

File – Save As – Save in the new format.

Old formats are (XLS, DOC, PPT), new formats end with X like XLSX, DOCX, PPTX. If the file has macros, it must be saved with the M extension like XLSM, DOCM, PPTM

For Word and Excel, the Compatibility Mode goes away as soon as you save the file in the new format. Problem solved.

In case of Excel, one more step is required. Close the file and reopen it. Now the problem is solved.

My customers are demanding old version files

Absolutely genuine problem. But that does not mean you should not get benefits of the new version you already have. So the idea is simple. While you are working on the file, use it in new format. Just before sending it to the customer, save it as a copy in old format.

Warning: Editing older file by mistake

What did you just do? You opened an XLS file, Saved it as XLSX and reopened it. Now in your File Open list, both files are seen – which is very dangerous.

While opening the file, you may open the older file and edit it without realizing it. Worse still, you may edit both files at different points of time and then get completely confused in life. So what is the solution?

The list of recently opened files gets flushed out as and when more files are opened. But keeping both (old and new) files visible there is undesirable.

Don’t worry – even this problem has been thought of by Office team. Just right click on the older file entry in the Recent Files list and choose Remove

Compatibility Mode in Excel

Precautions to be taken

Changing the file to new version is definitely a good decision. But it has one disadvantage – it changes the file name.

Therefore, if this file is being used by another file in a link, that link will still point to the older version file. This can lead to dangerous situation of you viewing outdated data in the linked file.

That is why you have to check if the file you are about to convert is linked to any other file. Unfortunately if another file is linked to the current file, the linkage information is in the linked file. So there is no technically sure way of finding out linkages. You will have to rely on your (or your team’s memory) for this.

If you find files which are linked to the current file, you must change the linkages. This is done by going to File – Edit Links – Select the linked file – Change Source – Choose the new format file.

IT Notes

Users can convert files to new format one by one – as and when they open old files. But for corporate repositories, you need a heavy duty bulk conversion tool.

This tool is called OMPM (now superseded by Office Telemetry). Go to TechNet Office Resource Kit for details.