Monday, December 30, 2019

Home Unlabelled Some examples of techniques that are better handled in Word than in Excel

Some examples of techniques that are better handled in Word than in Excel

December 30, 2019

Excel Word for Excellers. Photo Credit: Alexandra at Unsplash.com

Note

Katie Sullivan is a project manager on the Microsoft Word team. For this tip, I turn the podium over to Katie.

While Excel fans sometimes tease that Word and PowerPoint are freeware apps that come with Excel, there are times when Microsoft Word offers a feature that Excel does not. In those cases, it makes sense to copy your data from Excel, paste to Word, do the command, then copy back to Excel. Here are some examples of techniques that are better handled in Word than in Excel.

Technique 1: Convert to Upper, Lower, Proper

If you have to convert from uppercase to lowercase or proper case, Word has a keystroke shortcut. Copy the data to Word and toggle the case using Shift+F3.

Technique 2: Add Bullets

If you want to add bullets to Excel cells, it is far easier in Word than in Excel. Copy the cells to Word and apply a bullet style. Copy from Word and paste back to Excel. You might have to use the Reduce Indent icon a few times.

Tip

If you have a range of cells that contain text, select the range and press Ctrl+1 to open the Format Cells dialog. Then, on the Number tab, choose Custom from the list on the left. Click in the Type box and clear out whatever is there. Hold down the Alt key while pressing the 7 on the numeric keypad. A bullet should appear. Type a space and then an @.

Hold down Alt and press the 7 on the numeric keypad to insert a bullet. If you create a custom number format with a bullet, a space, and the @ sign, you will add bullets before the text in the cells.

Technique 3: Visualize and Color Formulas

If you have a massively long formula, say one with 10 nested IF statements, you can paste to Word and use colors and Shift+Enter to space the formula to help make sense of it. (One rebuttal from the Excel team: You can expand the formula bar and use Alt+Enter to split a formula into many lines. Or, you can use the great RefTreeAnalyser Add-in from Jan Karel Pieterse.

Technique 4: Faster SmartArt

Word offers the Convert Text to SmartArt option. While Excel offers SmartArt, too, it is not very handy there because you have to copy the entries one at a time into the SmartArt pane.

Technique 5: Extract Data from a PDF

Say that someone has an Excel workbook and saves that workbook as a PDF. They send it to you. This is annoying, and clearly they don‘t want you to reuse the data. If you open the PDF in Acrobat Reader, copy the data, and paste to Excel, it will unwind into a single column. But here is the secret: Paste that data to Word first. The rows and columns will paste properly. You can then copy from Word and paste back into Excel. (If you are stuck in a pre-2013 version of Office, I recommend Able2Extract.)

The original data is shown on the left below, and you can see on the right and how it looks when you paste directly from PDF to Excel. You can see that the data “unwinds,” with B1:C1 going to A2:A3 and so on.

When you copy from PDF and paste back to Excel, you get one column of 27 rows.

Paste that same data to Word.

If you paste the same date to Word, it is 3 columns of 9 rows.

Copy from Word and paste to Excel.

The data stays in the original order. You can unapply Word Wrap and adjust the column widths to get back to the original data.

Technique 6: Change Formatting of Words Within Excel

If you have sentences of text in Excel, it is possible to select one word while in Edit mode and change the color of that word. But globally changing the color of all occurrences of the word in Excel is tedious. Instead, paste the data to Word and press Ctrl+H. Change dog to dog. Click More>> and then choose Format, Font. Choose Red. Click Replace All.

The Find and Replace dialog in Word lets you change all "dog" to "dog" in red font.

Copy from Word and paste back to Excel. The figure below shows what you end up with.

Copy sentences from Excel to Word. Replace dog with dog in red. Paste back to Excel - and all of the word Dog is red.

Technique 7: Replace While Keeping Character Formatting

Word also handles a similar problem: replacing text but leaving the text formatting as it is. Below is a survey about the best pet. Someone has highlighted certain words in the text.

In Excel, many words have formatting applied: font color, strikethrough, and so on. You are about to use Find & Replace to change Puppy to Dog.

Use Ctrl+H to do a Find and Replace, as shown on the left. When you use Replace All, if a sentence was changed, your in-cell formats will be lost. In the figure below, the strikethrough remains in the first row because that row did not have an occurrence of the word puppy and thus was not changed.

After the Find & Replace, all of the in-cell formatting is lost.

To keep the formatting in the original text, copy to Word. Do the replace in Word. Copy from Word and paste back to Excel.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1