Category Archives: Office

How to Paste Multiple Lines Into Single Cell in Excel

365, Excel, Office, Word, , , , , ,

 

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

Office, Windows, , , , ,

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

Excel, Office, , , ,

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

365, Office, OneNote, Windows, , , ,

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

365, Excel, Office, , , , , ,

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

365, Office, 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

365, Excel, Office, , , , , ,

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!

Quickly Email an Obscure or Unsaved Contact in Outlook

Office, 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!

5 Ways to Copy Images from PDF or Word Document

Office, Word, , ,

Ever had a long document with several pictures and wanted to copy images only? Well, there’s an app for that as they say and here’s 5 ways to copy images from PDF or Word that will cover most any circumstance. Further good news is most methods don’t require any additional software!

Copy Multiple Images

First things first…If the document is a PDF, you want to convert it to a Word file. If you have Word 2013 or above, you can do this without any other software.

Convert a PDF to a Word File

You must open the file inside of Word. Double-clicking it in a directory would open it in the PDF software.

Open MS Word:

  1. File | Open or CTRL O and browse to the PDF file.
  2. Depending on your version of Word:
    • The PDF may load with an Info Bar to Enable Editing. Click that link and the PDF is loaded and can be edited like any Word file but is still in PDF format.
    • Click Save icon and the Save As dialog box will display and automatically change the format to .docx, OR
    • Word will notify that the file is being converted to the Word format. Click OK. The PDF will then load as a Word document. When file is converted, choose File | Save As or press F12 function key. Change the filename if desired or you can keep the same name as the original PDF as they are two different formats.

Now you have both PDF and Word formats of the document.

Copy Just the Images

Here’s what will automatically put the images in a separate folder:

  1. Do Save As again (get in the habit of using F12 as it works across Office and saves time and maybe even saves a little carpal tunnel).
  2. Change the file type in Save as type: to Web Page (*.htm; *.html) OR Web Page Filtered (*.htm,*.html). Either works; only difference being that Web Page preserves all Office formatting tags and Web Page Filtered saves with regular tags and some formatting may be lost. The former format would maintain quality as well.
Save As Web Page Type Option

Note: After saving in this format, your Word document will display in Web Layout View. If you want to switch back to Page Layout View, click its icon on lower right of Status Bar or select View | Print Layout in Views group on Ribbon.

Locate the Graphics

Saving in either Web Page format will automatically create a folder with the same name as your Word file with _files added to the end of the folder name and saved to same directory. This contains all the pictures as separate files in the .jpg or .png format depending on type of graphic. Filenames will be labelled Image001, Image002, etc., and can now be renamed, copied or moved. Like magic…who knew?

Here’s another method if you already have a Word file containing images, by changing the file extension:

Change the File Extension from “.docx” to “.zip”

This way extracts all the images from the document as a batch just by changing the file extension! Ensure your file is in the .docx extension (Word 2007 and above), and it’s not a bad idea to make a copy of the Word file and use the copy to change the extension, for safety’s sake.

Note: If you just send the Word document to a zipped folder without changing the extension, the document gets zipped and unzipped as a whole, and the images are not extracted.

  • Ensure that document is closed, and in the file directory, right click on the filename and choose “Rename” (or press F2 function key to select the name).
  • Change the extension to “.zip”. (Make sure you don’t delete the “.”) Press ENTER.
  • You will get a warning dialog box but just click Yes button.
Changing file extension to .zip warning
  • The new zipped folder will display with the same filename as your Word file.
  • Double click on that folder and Windows will display the Extract group on the Ribbon.
  • Click Extract All icon.

The same folder displays but is now unzipped Double click the Word  folder and then Media folder. All images are there in .jpg format.

Copy Images with Save As Picture
Method

This is a direct method to copy images you can do one by one if you want a single or small number of pictures:

  • Right click over desired picture.
  • Choose Save As Picture…
Save As Picture command
  • Save As dialog box opens with Pictures folder as location. Change filename or location as needed. Image will automatically be saved in .jpg format.
  • Can now be opened in any image editing program .

Copy and Paste Method

This is last resort if you have an older Office version and the Save As Picture… command does not display on right click. A simple and copy and paste will work for one or two pictures:

  1. Right-click on the image and choose Copy.
  2. Open any image editing software such as Paint in Windows or freeware such as Paint.NET.
  3. Paste the image and click Save button (or CTRL S) to save the image.
  4. Paint will save in .png format but you can change to .jpg or choose from many other formats .

Use Zip Software to Extract Images

Third party unzipping software is usually no longer needed but there are free, reliable options such as 7-Zip for compressing/extracting files and graphics. After installing, when you right click on a picture, that software option should display in the shortcut menu. Click it and a separate folder should be created containing the pictures.

Hopefully, one of these was “picture perfect” for you and now your images are all in one place for easy access.

If you would like more information on different ways to save a Word or Office document as a PDF, see my blog here…https://gaylelarson.com/word-document-pdf-format/

Let me know which technique(s) worked best for you in the Comments below. Thanks for reading!

5 Steps for a Successful PivotTable

Excel, Office, , , ,

Trying to get more information out of that large spreadsheet? Functions such as VLOOKUP and SUBTOTAL are great summary tools but if your data changes frequently or you want to look at it in different ways, these 5 steps for a successful PivotTable will help you get great results.

PivotTable Numbers Picture

Why Use a PivotTable?

PivotTables allow you to look at your data in a variety of ways that aren’t possible in the regular grid format of a worksheet. Set up properly, you can also instantly access “underneath” details that automatically display on a separate worksheet.

Prepare Data for PivotTables

You only get out of PivotTables what you put into them, so there are some key points to follow before clicking that Insert button to get the best results and avoid errors.

5 Steps to a Successful PivotTable start with making sure all the data is in the rawest form:

  1. Remove any blank columns or rows.
  2. Need a header row with a name in each column in the first row of the data. (Each column name becomes a field in the PivotTable). Always a good idea to format the row bold or italic to distinguish it from the data.
  3. Don’t mix data types in a column. You want only dates in Dates column, numbers in Sales column, text with text, etc.
  4. Don’t use detail data for column headings in source data, use “Year”, not “2020”, use “Month”, not “January”, etc.
  5. Convert the data range into an Excel Table before creating the PivotTable (not necessary but has added benefits). When rows are added to expand the original worksheet table, the PivotTable will automatically update to include them when you click Refresh. If created from a regular worksheet range, only data edited within that range gets updated in the PivotTable.*

Here’s a worksheet I converted to a Table:

Excel Table Picture

Create the PivotTable

As a result, your worksheet is ready for primetime and creating the PivotTable:

  1. Click anywhere in the body of the data (if only using portion of worksheet, select that range first).
  2. Choose Insert tab and click the PivotTable icon in Tables group, to display a dialog box with your data range automatically selected. Placing it on a separate worksheet is also selected. (Can choose on same worksheet here if desired).
  3. Click OK and your new PivotTable Sheet is created.

Based on the above sample worksheet, this is the created PivotTable:

New PivotTable Picture

The PivotTable is now waiting for you to drag and drop the fields into the four area boxes at the bottom. Text columns would go in either Columns or Rows boxes and values such as sales or counts would go in the Values box.

Tweak the PivotTable

Here’s the PivotTable results after dragging Products into Columns, Salesperson into Rows and Sales into Values:

PivotTable Created from Fields Picture

So now, from here you can add filters, create subsets of data, such as dragging Year below Salesperson in Rows box would display sales by year underneath each Salesperson, and so much more!

The main thing here is to start right with clean format and data so your PivotTable behaves. Make a copy of a worksheet and play. You will be amazed!

You can also use a great feature available since Excel 2010 called Slicers which are visual filters. A blog on this feature and more PivotTable wonders coming soon!

*Click here for my blog on Excel Tables:    https://gaylelarson.com/magic-excel-tables/