How to Paste Multiple Lines Into Single Cell in Excel

 

Have you been frustrated when pasting text that is more than one paragraph (even if just two lines) from Word to Excel when it pastes into more than one cell? Wondered how to paste multiple lines into a single cell in Excel?

Frustrated man viewing strange Excel paste results

It doesn’t matter if you use the keyboard shortcut or the Paste icon(s), your text will split into additional cells each time the Enter key was pressed in the original program. Also, the default formatting will be from Word (the source program).

Paste Multi Lines into One Cell

If I paste two or more lines that are actually multiple paragraphs into Excel in cell C4, the second paragraph will end up on C5, the third in C6 and so forth because the Enter key was used as explained above.

Force All Text into One Cell

Fortunately, there’s an app for that, so to speak. Here’s the magic fix:

  1.  Copy your text in Word to the Clipboard.
  2.  In Excel, double click in C4 or desired cell (activates Edit mode).
  3.  Paste using your favorite method (mine is CTRL V)

All your text (regardless of  the number of paragraphs) is now in that single cell (and row)!

Note that your pasted information is automatically in the Destination format to match the rest of your spreadsheet and that it will word wrap to the width of that column.

Options

Other ways of activating Edit Mode are to be in the desired cell and click in the Formula Bar or press the F2 function key in the cell for the same result.

Controlling Line Breaks

If you would like the line break(s) to be different in your single cell contents:

  1.  Click where you want to end that line.
  2.  Press Alt+Enter to move everything after that down to the next line (same row).*
  3.  Excel automatically adjusts the row height.

*Adjust the column width if needed before using the command. If you want Excel to take care of the line breaks, use the Word Wrap command  on the Ribbon in the Home tab |  Alignment group. It toggles on and off with every click.

Word Wrap icon location in Excel

Alert: Be careful how much text you try to paste into a single cell as Excel has a character limit for cells. A cell can only contain a maximum of 32,767 characters. Anything over that will be cut off.

Be sure to check out my blog for more tips that will get your work done faster in Excel: https://gaylelarson.com/5-amazing-must-have-excel-tips/

Have you had issues with pasting information from other programs and getting unexpected results in Excel? Let me know in the Comments below.

How to Activate Windows 10 Clipboard History

Finally, a way to capture multiple items from Windows screens and use them over and over again. It is not on by default so let’s look at how to activate Windows 10 Clipboard History!

Windows 10 Clipboard History

What Clipboard History Does

Simply put, Clipboard History puts Copy and Paste on steroids. Normally, Windows stores the last object you cut or copied to the clipboard and then is replaced by the next one as it could only hold one object at a time. Clipboard History changed that when it first appeared in Windows 10 at the end of 2018.

The feature supports text, HTML, and images less than 4 MB in size. Anything larger will not be stored in the history. Clipboard history stores up to 25 items. Once maximum is reached, oldest items are replaced with the new ones. Be aware that the contents are stored in RAM so any entries will be lost when your device is restarted unless you pin those you want to keep.

How to Activate Windows 10 Clipboard History

  1. Open Start menu and click on the Settings (gear) icon.
  2. Click on the System option on the Settings page.
  3. Select the Clipboard option from the left of the screen (sidebar).
  4. Click the toggle on for Save multiple items on the right panel to enable clipboard history feature on Windows 10.

Setting to turn on Windows Clipboard History

How to Retrieve and Use Clipboard History

If you have done a lot of copying and pasting, you are familiar with CTRL X to cut (move); CTRL C to copy and CTRL V to paste. Other methods such as right clicking selected object and choosing Cut or Copy also work. To view and use the Clipboard History, just press the Windows key + V key and scroll the contents. Newest entries will be at the top. Click on the entry you want to paste.

Delete or Pin Clipboard Contents

If you have an entry you want to save for future use, point at it and click the ellipsis (…) in the upper right corner to display the Delete, Pin and Clear
All options. Choose Pin to keep it available after Windows has done any Restart. Anything not pinned, will be deleted any time Windows resets.

Delete will remove only the selected object. Clear All will empty the Clipboard so you can start over and again store up to 25 items.

Windows Clipboard History dialog box

Microsoft Office Programs

Note: Clipboard History will capture from all of your screens, not just Microsoft programs. The MS Office Clipboard which has been around for many years, can store up to 24 items, but only works between Office applications.

If you are only copying and pasting between Office programs, use its own Clipboard. My experience is if you have both clipboards activated, they will both capture any text entries but the Windows clipboard doesn’t grab images inside of the Office file.

Turn on the Office Clipboard:

It is in the Home tab | Clipboard group. Just click the dialog box launcher in the corner of the group:

Turn on MS Office Clipboard

Now every cut or copy you do in any Office program will display on the Clipboard and be available in all of them.

Note: Windows Snip and Sketch has replaced the Snipping Tool and the popular garage Snip app for single copy and paste actions. If you are not familiar with this, get more information from Microsoft at: https://www.windowscentral.com/how-use-snip-sketch-take-screenshots-windows-10-october-2018-update

Want some more Windows 10 features that you may not be using? Take a look at my blog on Virtual Desktops. They are fun and useful too: https://gaylelarson.com/create-virtual-desktops-in-windows-10/ 

Thanks for reading! Have you been using Clipboard History? Let me know how it has worked for you in the Comments below.

 

Create a Drop-Down List in Excel with Data Validation

Need to have consistency for a column of text that might be names, products, departments, countries, or some such? Your hunt is over…Create a drop-down list in Excel with Data Validation!

Columns of text for Data Validation

Control What Gets Typed in a Cell

You can end up with a mish-mash of abbreviations or spellings if you are typing them in on the fly or even worse, if others are filling in the data as their entries can further add to the problem.

Well, not to fret, Excel has you covered with a feature in Data Validation which allows you to create different types of lists. A simple drop-down list can be typed directly into the dialog box or if you already have it typed somewhere in your workbook, you can capture it and avoid retyping.

How to Create a Drop-Down List in Excel with Data Validation

Let’s cover some ways to create that list…

Important Prep: Select the column or range of cells where you want the drop-down list to display:

  1. On the Data tab, click Data Validation in the Data Tools group. The Data Validation dialog box will display:

    Data Validation Settings box

  2. On the Settings tab, select List from the Allow: drop-down box which will add the Source field.
  3. In the Source box, either:

    1. Type the names of the employees, departments or whatever your list is and separate each entry with a coma such as: Marketing, Human Resources, Administration OR
    2. If you have the list already typed somewhere in your workbook, click in the Source box and select the list. Example…if it is in Sheet 2 in A2:A6, click Sheet 2 and highlight that range and it will auto input as =Sheet2!$A$2:$A$6. The drop-down list of department names has now been created.*
    3. Click OK.

     

In your worksheet, click on any cell in the column (or range) with Data Validation and a drop-down arrow will display at the right of the cell. Users are forced to click the icon and choose from the list, and If anything is typed in the cell, an error message is displayed. You have the power!

Note: The two other tabs in Data Validation are important for different types of validation but not needed here as the drop-down list is self-explanatory. Because the default setting is Stop! in the Error Alert tab the user must select from the list.

*Named Ranges for Fast List Creation

An even faster method to create a list is to select the range with the names before opening Data Validation. Click in the Name box and type a name for the list, i.e., Dept and press ENTER. Open Data Validation and in the Source box, just type =Dept and click OK and  your drop-down list is created. This is called a Range Name (or Named Range).

Want to know more about how Named Ranges (Range Names) can be used in formulas and save time and effort? See my blog: https://gaylelarson.com/use-range-names-in-formulas/

Microsoft also has a detailed article and video tutorial for Data Validation lists at: https://support.office.com/en-us/article/Create-a-drop-down-list-7693307A-59EF-400A-B769-C5402DCE407B

Thanks for reading. Has Data Validation made your spreadsheets a lot cleaner and efficient to use? Let me know in the Comments!

What is New in OneNote for Windows 10

OneNote for Windows 10

Note: This article applies to the OneNote for Windows 10 app, which is pre-installed on all editions of Windows 10 and is also available as a free download from the Microsoft Store.

New Features in OneNote for Windows 10

If you’re not using the free OneNote app, you’re missing out in a great organizing tool that Microsoft continues to improve with cool, new features. A few are listed below and for more, see the link at the bottom of this post.

Improved printouts of Office files as images in OneNote

Insert Word documents, Excel spreadsheets, or PowerPoint presentations as a virtual printout to include their pages as images in your notes.

Click Insert > File, choose an Office file to insert, and then click Insert as printout. You can move, resize, delete, and annotate any of the printout images that appear.

Insert Office files in OneNote

Easily navigate and organize your notebooks

The OneNote for Windows 10 app window now offers more note-taking space by providing an easier way to control the navigation interface.

You can choose to display the notebook, section, and page navigation panes only when you need them, while always keeping immediate access to the Search and Recent Notes buttons.

Organize OneNote Notebooks

Improved Print to OneNote

In Windows 10 Settings, select “OneNote” as your default printer and then print files from any app or browser on your PC.

Instead of printing out the information on a real printer, the printout is sent to OneNote, which lets you choose where to add the page. You can add in any section of your open notebook, or in any other notebook that you choose.

Set OneNote as default printer

Mark your notes with custom tags

Using tags in your notes is a great way to visually call out and categorize important notes for follow-up. (For example, track action items after a meeting).

To create your own custom tags, click the Tags menu dropdown on the Home tab (next to the text alignment buttons), and then click Create New Tag.

Create custom tags in OneNote

Search for tagged notes

Searching for tags in your notes (for example, “Important” or “Question”) now displays matching tags in a separate search results pane. Makes it even easier to find and follow up on important notes.

Search for tagged notes in OneNote

Search for text phrases, not just keywords

In addition to searching your notes for specific keywords, you can now enclose any search phrase in quotation marks. This allows you to find those exact words anywhere in your notes.

Click the Search button, enter your phrase into the Search box, and then press Enter.

Search for text phrases in OneNote

Why am I not seeing some of the OneNote updates mentioned here?

Microsoft reports that new and updated features in OneNote for Windows 10 may be released on a gradual roll-out basis. They may not make their way to your computer or device immediately.

What version of OneNote do I have?

In OneNote for Windows 10, click the Settings and More button , click Settings, and then click About.

The full version number is displayed immediately under the product name.

Screenshots and descriptions were taken from the Microsoft website and there are many more recent features added to OneNote. Check out the improvements and additions at: Microsoft OneNote Updates

Microsoft is also looking for feedback to make future improvements and you can do that at the above link.

For more OneNote tips and what OneNote can do for you, see my blog at: https://gaylelarson.com/?s=OneNote

Thanks for reading! Leave me a comment below if you are using OneNote or are going to start taking advantage of this powerful free tool, now that there are so many cool features available.

How to Find Cells that Contain Conditional Formatting

Applies to MS Excel 2007+

Find Cells that Contain Conditional Formatting

If you have applied Conditional Formatting in your workbooks, you may later want to know how to find cells that contain Conditional Formatting as it isn’t always obvious how the feature has been used or what the formatting represents. This is especially true if someone else created the file and now you need to work on it.

Colored numbered balls represent Conditional Formatting

Fortunately, it is relatively easy to find which cells have conditional formatting applied to them using the Go To feature of Excel.

Steps to find all conditional formatting:

1. Press F5 function key. Excel displays the Go To dialog box.

Excel Go To dialog box

2. Click Special… button to display the Go To Special dialog box.

Excel Go To Special... dialog box

3. Click on Conditional Formats 

4. Click OK

Excel now reveals all the cells in your worksheet that contain conditional formatting.

If that’s all you needed, you are free to go to lunch! If you want to see, edit or delete those formats, then read on…

How to See Existing Conditional Formatting Rules

Now that you know which cells contain conditional formatting, you might want to look at the definition of the different rules applied.

On the Home tab, Styles group, Conditional Formatting, Manage Rules:

Conditional Formatting Rules List

Select This Worksheet from drop down list to display all Conditional Formatting Rules for the worksheet:

Condiitonal Formatting Rules Manager dialog box

Click on Edit Rule… button to see details and what rule was applied or Delete Rule to remove it.

The Fast Way to Delete Existing Conditional Formatting Rules

You don’t have to be in the Rules Manager to delete any or all the conditional formatting applied, for instance…

If you want specific cells/ ranges cleared, select them first, then:

On the Home tab, Styles group, click Conditional Formatting, Clear
Rules > Clear Rules from Selected Cells

Conditional Formatting Clear Rules list options

To delete all the conditional formatting on the worksheet, you don’t need to have any particular cell selected, just choose > Clear Rules from Entire Sheet

Thanks for reading! Hope this has been valuable to you. For more helpful tips on Conditional Formatting and other neat Excel features, give a click and go to this blog: https://gaylelarson.com/conditional-formatting-clarity-visual-impact-excel/ 

Conditional formatting has six rules that can be applied to your data. For more information, visit this Microsoft site for additional options with this powerful tool:

Use Conditional Formatting to Highlight Text

Please leave a comment below and let me know how Conditional Formatting works for you in Excel…Happy Computing!

The Power of the Go To Command in Word

Available in all versions of Word

That long newsletter or report with lots of images, tables, comments, etc., is staring you in the face, and you need to edit or format several of them. Not knowing the shortcuts can involve an annoying amount of scrolling, zooming or anything else that will propel you through the pages to get to that next picture or special object.

Reviewing or editing someone else’s work that you are not familiar with makes it even more difficult but not to worry…Rescue is close at hand. Word has a solution that lets you zip through the document and skip to the next object you are searching for in a jiffy.

How to Navigate to Next Object of Same Type

That’s the job of Word’s Go To command. You have probably used the Find and Replace commands to speed up searching and replacing text.  In that same dialog box lives the Go To command tab. It allows you to jump to a specific Page or next Comment, Graphic, Table, and several other features. The default is Page but just select the command you want for navigation.

Ribbon: Home tab/Editing group/Find/Go To

Keyboard: CTRL G or F5 function key


We’ll choose Graphic for the Go To example here but it can be anything in the Go to what: list. Following is an example of a document on Efficiency Tips that has a graphic on page 3 and the next one on page 10:

  1. Start at the top of the document (CTRL HOME)
  2. Display the Go To tab
  3. Click the Next button to go to first picture on page 3
  4. Click Next again to jump to next picture which is on page 10
  5. Continue until completed or move back through the pictures with Previous

“Efficiency Tips

Do you want to be more efficient? The following tips can help you increase your productivity and are easy to implement. Being more productive will help you achieve your goals and success more quickly.

Don’t Multi-Task:
Focus on one task at a time. This will ensure that you are giving your full attention to what you are doing and increase your ability to utilize the power of the flow state. Use your to do list and pick off one item at a time.


Page 3

Plan:

You can’t achieve anything without knowing what it is you want to achieve. You need to know your goals. It’s also important to create a plan on how to achieve them. Set yourself daily goals and use a to do list to help you identify what you need to achieve each day. Tick things off as they are completed.


Page 10

Remove Distractions:
If you want to become more productive then you need to remove distractions. shut down all social media and email. Turn off the T.V. and your mobile phone. Set a specific time aside each day when you can you use those things and you’ll get a lot more accomplished when not constantly disturbed by messages and notifications.

You can become more productive if you use these tips!”

In the above example, you jump from first graphic on page 3 to next on page 10 with a quick click on the Next button!

Go To Options in Older Word Versions

Word 2010 and prior versions had an additional method for navigating through objects with the Object Browser which was located at the bottom of the vertical scrollbar. Clicking that icon gave you the same list of objects that you see on the Go To tab command. It uses arrows for Next and Previous navigation. Not sure why this handy tool was removed but when Word 2013 arrived, sadly the Object Browser did not.

The Go To dialog box is such a great, fast way to leapfrog through an object-heavy document with no guess work – no scrolling required. And, if you use the keyboard shortcuts for quick access, it’s even better!

If you like fast ways to move around your lengthy document, you might want to look at this post on Styles where instead of special objects, you can use text styles to navigate:   https://gaylelarson.com/word-styles-make-formatting-easy-peasy/ 

Let me know in the Comments  if you have saved time using the Go to what: feature on a long document! 

Analyze or Compare Workbook Versions with Spreadsheet Inquire

Analyze or Compare Workbook Versions with Spreadsheet Inquire is available with  Microsoft Office 365 or Office Professional Plus 2013 installed on your computer.

Note Spreadsheet Inquire was previously called Spreadsheet Compare.

What does Spreadsheet Inquire do?

You will want to analyze or compare workbook versions with Spreadsheet Inquire. You can also examine a workbook for problems or inconsistencies, or see links between workbooks or worksheets. Use the commands on the Inquire tab to do all these tasks, and more.

Where do I find the Spreadsheet Inquire feature?

The Inquire tab on the Excel ribbon has several groups and icons for the commands described below, and by default should display to the right of your last ribbon tab:

Spreadsheet Inquire on Excel ribbon

If you don’t see the Inquire tab in the Excel ribbon, it may not be activated…

How to turn on the Inquire add-in

If the Spreadsheet Inquire tab does not display in the ribbon, you may need to activate the add-in:

  1. Click File > Options > Add-Ins.
  2. Make sure COM Add-ins is selected in the Manage box and click the  Go… button.

  3. In the COM Add-Ins dialog box, make sure the box next to Inquire Add-in is selected.
  4. Click OK.

COM Add-in dialog box

After the add-in is turned on, the Inquire tab will appear in Excel.

Note    If you don’t see an entry for Inquire Add-in in the COM Add-Ins dialog box, it’s because either your version of Office or Excel doesn’t include it, or your organization’s system administrator has made it unavailable. Microsoft Office Professional Plus 2013 includes the Inquire add-in for Excel as does Office 365.

Compare two workbooks

Spreadsheet Inquire compares workbooks. The Compare Files command lets you see the differences, cell by cell, between two workbooks. You will need to have two workbooks open in Excel to run this command.

Results are color coded by the kind of content, such as entered values, formulas, named ranges, and formats. There’s even a window that can show VBA code changes line by line. Differences between cells are shown in an easy to read grid layout, like this:

Results of comparing two workbooks

The Compare Files command uses Microsoft Spreadsheet Compare to compare the two files (recent updates may say Spreadsheet Inquire):

  1. In Windows 8, you can start Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen.
  2. In Windows 7, click the Windows Start button and then > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013.

To learn more about Spreadsheet Compare and comparing files, read Compare two versions of a workbook.

Analyze a workbook

The Workbook Analysis command creates an interactive report showing detailed information about the workbook and its structure, formulas, cells, ranges, and warnings. The picture here shows a very simple workbook containing two formulas and data connections to an Access database and a text file.

Results of Workbook Analysis command

There are other features available in Spreadsheet Inquire. Be sure to look into them and let me know how you are using this useful add-in! More information at:   What you can do with Spreadsheet Inquire

Note
Many of  these screenshots are from the Microsoft website.

Want more ways to analyze or compare workbooks or worksheet data? Have a look at this post…

https://gaylelarson.com/5-amazing-must-have-excel-tips/ 

Let me know in the comments below if you have used some of the new Spreadsheet Inquire tools. Happy computing!

Use MS Graph to Convert Word Tables to Charts

Convert Word Tables to Charts

Switching back and forth from Word to an Excel spreadsheet to depict numerical data from a table in your document can be awkward as the Chart feature does not play well with a table. Instead, use MS Graph to convert Word tables to charts inside Word so that you have both your original table and a graph based on that data.

 

Convert Chart in Word with MS Graph

Steps to create a Microsoft Graph Chart

Inside of Word:

  1. Select your table.
  2. Click Insert tab on the ribbon.
  3. In the Text group, click the Object icon to display the dialog box.
  4. Scroll down the list of objects and choose Microsoft Graph Chart.
  5. Click on OK to display a columnar graph of your table (default style).
  West Central East
Qtr 1 9110 9005 11600
Qtr 2 9845 10700 8940
Qtr 3 11660 9995 8850
Qtr 4 14990 13445 12360

What if I don’t like the formatting or the figures change in the future? Can I edit or do I have to redo the whole table and/or chart? Funny you should ask. Thankfully, no, and here’s the fix…

Format the Graph

MS Graph behaves differently than most special features in Office in that clicking it does not automatically display a contextual ribbon for formatting. However, double clicking on the graph border (or in white space inside the border) will activate a toolbar so that you can change the chart type and other formatting. A Datasheet will also display.*

Edit the Data

Changing the figures in the table will not update the graph. Edit the figures by double clicking the graph to display a data table and a toolbar at top of window. This Datasheet can be moved above or below your graph by dragging its title bar. Make changes in the Datasheet and watch the graph auto update. When done click outside of the data table and graph (or press ESC) to return to your regular ribbon. (Your original table will not change).*

*Editing and formatting the graph can also be done by right clicking in the graph and choosing Edit or Open.

As much as I love all the continuous new features in MS Office, sometimes the old stuff does the trick!

Let me know if this was new to you and/or how you used it to amplify your Word document or report.

Want more options for using tables, graphs and charts? Check out some of the Excel posts here:

Use Excel Sparklines Instead of Charts to Display Data Trends

The Magic of Excel Tables

 

 

 

Print a Key Assignment List in Word

Print a Key Assignment List in Word for Shortcuts

If you have customized Word with shortcut key assignments for commands, macros or styles, you can print a key assignment list of those shortcuts. Depending on your version, follow these steps. (Note the other options for printing here such as AutoText Entries as well):

Print a shortcut key assignment in Word

Word 2010 and above:

  1. Press Ctrl+P to display the Print option selected at the left side of the page.
  2. Click the drop down arrow under the Settings category and choose Key
    Assignments.
  3. Click the Print button at the top of the page.


Print a Key Assignment List in Older Versions of Word

Word 2007:

  1. Press Ctrl+P. Word displays the Print dialog box.
  2. In the Print What box, select Key Assignments.
  3. Click on OK.
 


This is a great timesaver when you use a program regularly!

Don’t Know How to Customize Shortcut Keys?

If you like the idea of having and printing out key assignments but don’t know how to create keyboard shortcuts for your favorite commands, here’s a short tutorial:

Create Keyboard Shortcuts for Ribbon Commands (Word 10 and above)

Not all available commands are displayed on the ribbon as there is just not enough room but every command is accessible by customizing the ribbon.

Access the Customize Keyboard Shortcuts feature:

  1. Right click anywhere on the ribbon
  2. Choose Customize the Ribbon
  3. Click the Customize button at bottom in Keyboard Shortcuts:

The Customize Keyboard dialog box displays and you can scroll through the Tabs on left and choose the desired Command on the right.

Create Shortcut Key dialog box

  1. When the desired command is displayed any previously assigned shortcuts will display in the Current keys: box. You can make note of it and use that key combination or create your own.
  2. Create your own shortcut by pressing the keys you want to use for a shortcut. in the Press new shortcut key: box at right.

NOTE: Most letters are already in use in Windows and/or Word when combined with the CTRL key. For instance, CTRL F is Find. If you replace that shortcut with one of your own, you are overwriting the previous shortcut. If you want to keep the common shortcuts, just add SHFT so a shortcut could be CTRL SHFT + a letter.

TIP: You can also use the function keys (F1 – F12) as well as the CTRL, SHFT and ALT keys as shortcut assignments. (You can also add CTRL and/or SHFT here as well).

 You can also use the TAB and UP and DOWN Arrow keys to move through the command dialog boxes to select Categories and Commands.

Want more fast ways to get your documents done in Word? Visit this post…

Remove Formatting from a Word Document

Let me know if you have questions or have some ways you have Word work for you!

 

Quickly Email an Obscure or Unsaved Contact in Outlook

 

You are probably familiar with seeing a frequent recipient’s email address automatically pop up as you type in a new email form. But what if you need to quickly email an obscure or unsaved contact in Outlook?

Women in Send It T-shirt

If you know the name you started typing in the To: box of your email is somewhere in your Contacts or Address Books but AutoComplete does not display it, press CTRL K to display the Check Names dialog box:

 

Outlook EMail Check Names dialog box

Existing Contact

If the desired name appears in the box, just click it but if the contact has never been used or not accessed for some time, it may not show there. If not listed, click the Show More Names… button to display your full contact list of addresses and groups. Choose from the list and you are good to go.

New Contact

No need to set up your new contact before sending mail because you can just click the New Contact… button here and set up needed information on the fly.

 

Outlook New Entry Contact dialog box

Either way, these email addresses should automatically display when typing them in the To: or CC: boxes of the New email form in the future as they will be recognized by AutoComplete.

For more tips on editing and managing your Outlook contacts, see my post https://gaylelarson.com/apply-changes-multiple-outlook-contacts/

What kind of shortcuts do you use in Outlook to speed up your email tasks? Let me know in the Comments below. Thanks for reading!