Category Archives: Excel

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.

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!

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!

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!

Use MS Graph to Convert Word Tables to Charts

Excel, Word, , , , , , , , ,

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

 

 

 

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/ 

 

 

 

5 Amazing Must Have Excel Tips

Excel, Office, , , , ,

Have you stared at the data in your worksheet and wondered how you were going to get the same information in multiple cells, or how to automatically insert a cell with a full name from two cells with the first and last name, or how to best graphically display those sales figures? Well, hopefully, these 5 amazing must have Excel tips will give you some answers!

Enter same content in multiple cells simultaneously

Need the same labels or values in several cells?

  • Select the first cell
  • CTRL click on the other desired cells
  • Type Desired content
  • Press CTRL ENTER

Presto! Same content in all the cells.

Same content in multiple worksheets

Need different content in several cells to display in multiple worksheets in the same cell references? Group worksheets and just type it once!

Group desired worksheets by selecting worksheet name tabs:

  • If worksheets are not adjacent, click first one, and then CTRL click on each worksheet you want to have identical content.
  • If adjacent, click on first one, and SHFT click on last one (or right click and choose Select All).
  • Now type in as many cells as you want on the active worksheet (can be different data in each cell). Same contents are now in corresponding cells in all selected worksheets.
  • To Ungroup, click on a worksheet tab outside of the group, or right click on a worksheet tab and choose Ungroup Sheets.

TIP: This is a terrific shortcut for adding the same headers and footers or printer or page setup settings to multiple worksheets simultaneously. Also works for creating duplicate content for days, months, quarters or any repetitive unchanging data; basically creating an in-workbook template!

Number Worksheet Rows

Can’t figure out number of rows because your data starts several rows from the top or some other area of the worksheet?

Excel allows you to number the rows and will auto adjust when you add or delete them if you use the =ROW() function. The key is to deduct the number of rows above the one you are starting in so the numbering will always be correct.

Example: We’ll use Row 12:

  • Insert a column or click in the column where you want the numbering
  • Type: =ROW()-11 in row 12 of that column. The number 1 is displayed
  • Fill down desired number of rows.

You now have the actual number of rows you are working in!

Speaking of numbering, getting Excel to automatically create a series of sequential numbers, requires some gentle nudging…

Sequential Numbers

I you want to populate a column with sequential numbers, just enter the numbers in cells, such as

1

2

Select both cells and drag the Fill Handle for as many numbers as you wish. Excel will automatically iterate the count. Start with any number.

It will also work with jump numbering, such as this:

1

5

Select both numbers and drag down for the desired number. The next numbers would be 9 and 13.

Note: This also works with dates and text.

TIP: Here’s a trick for fast sequential numbering; just type your first number, hold down the CTRL key and drag the Fill Handle. Excel will recognize the pattern. (For non-sequential numbers, you still need two examples).

Like then so far? Let’s look at a couple more must have Excel tips!

Flash Fill

Flash Fill is a time-saving feature that reads patterns in adjacent columns and automatically fills the remaining cells in a column based on those patterns. It is useful when you need to concatenate (join) cells, or separate information in cells without wanting to write a cumbersome formula. Kind of like AutoFill on steroids! This amazing tool will make short work of many repetitive tasks, all without formulas (or the Text to Columns feature)! Here’s some things it can do:

Splitting Input Data

You received a huge spreadsheet where someone has put first and last name in one column, and you need them separated for sorting and filtering. (This could be any data, i.e., department name and phone extension, or salesperson and monthly sales figure. Doesn’t matter as long as the data has a separator, such as a space). Flash Fill to the rescue…

Example:

Full names are in Column A.

Steps:

  • Insert: two columns to right and enter appropriate column header labels.
  • Type: Fred, in appropriate cell (in example, Cell B2)
  • Press: ENTER
  • Type: Mary (note that Excel has figured out what you are doing and is displaying the remainder of first names, and is awaiting your approval)
  • Press: ENTER, and presto, all the rest of the first names are added!

Flash Fill Splitting Input Data

Repeat the above steps in the cells for Last name (In example, Cell C2)

List is now complete:

Flash Fill First and Last Name cells

TIP: You can have flash fill complete only the last name, i.e., just insert one column, and instead of typing “Fred”, you would type “Frump”, ENTER, then “Lamb”, ENTER, and you would be done.

Note: Give Excel one or two examples of what you are trying to accomplish so flash fill can see a pattern. It will work with text, dates, numbers and time. Be aware that if you change source data, cells containing flash fill data will not update as there are no formulas involved.

Combining Data from Different Columns

The reverse is also true. You have a worksheet with first and last names (or any data) in two columns and you want them combined into one. This used to require the CONCATENATE function but now Flash fill can do it automatically. Drop kick that CONCATENATE function off your spreadsheet. Tough enough to spell it, let alone use it!

Here’s the data in two columns and how to combine into one:

Flash Fill combined cells

Steps:

  • Insert: a blank column to the right of split data
  • Type first full name in the new column (C2)
  • Press ENTER
  • Type second full name, and before you are finished, Excel sees you want the remainder filled in
  • Press ENTER to complete the list

Extend the Combined Data

You just got word that you need to add the email addresses for all the people in your company that you just did your flash fill magic on (or any such list). To make it worse, the addresses are last name first. Flash fill is not intimidated and has your back:

  • Insert your new, blank column to the right of the Full Name column
  • Type the email address in the first cell
  • Press ENTER
  • Start typing the second email address, and the dependable flash fill displays
  • Press ENTER to complete the email list for the remaining cells.

Flash Fill auto email addresses

You have saved so much time, you go to coffee!

Keyboard shortcut lovers…Here’s an even faster way to activate flash fill. Type the entry in the first cell. Press CTRL ENTER, and then press CTRL E and the column is filled in a flash!

TIP: If you accidentally lose the Flash Fill display, click the Data tab, and in the Data
Tools group, click the Flash Fill icon (ensure you are in the second entry in your column). If the ribbon and Flash Fill are greyed out, click outside your data range and then click in the second cell again and retype.

There is so much more that Flash Fill can do, so play with it and see if it will display that very thing you want.

Note: If Flash Fill is not working and the above tip doesn’t activate it, it may be corrupted or missing system files. There is an easy fix and download thanks to Microsoft Gold Certified Partner, Scott Chan. Just follow the information and link on his website: Scott Chan PC Support

The Quick Analysis Tool

You’ll find this a really helpful feature whether you are an experienced user or fairly new to Excel. Just select the data to analyze, and the Quick Analysis icon appears in the bottom-right corner of the selected data.

Click that icon, and a dialog appears showing a range of tools for analyzing the data, such as Formatting, Charts, Totals, Tables and Sparklines. Click any option, and a series of selectable choices appear; preview those choices by mousing over them. Next, click the option you want to apply it to your data. This feature speeds up the process of formatting, charting and writing formulas.

Steps:

  • Select: the cells to analyze. When the mouse is released, you see the Quick Analysis Tool icon at bottom right of the selection:

Data selected with Quick Analysis tool displayed

  • Point: at the icon to display the tool tip:

Quick Analysis icon

  • Click: the icon to display the options:

Quick Analysis tool options displayed

  • Roll your mouse over each of the options and when you see the one you want, just click!
    • Formatting: Adds Conditional Formatting to your selection based on Excel’s default rules
    • Charts: Displays the main types of charts that will work with your data
    • Totals: Sum, Average, Count…The equivalent of AutoSum feature. Running totals and percentages also available
    • Tables: Automatically converts your data to a table with the filters in every column
    • Sparklines: Displays miniature charts in the cells to the right of each row of data (3 types available)

Example of choosing Formatting: Selecting Greater Than under Formatting, Excel will automatically display the rule it used to color every cell with a number over 6065 pink. You can then make adjustments right on the screen. You can change the base number here and also if pink isn’t your color, change it here!

Conditonal Formatting displayed from Quick Analysis tool

TIP: You can apply any one of these and then edit if you want to change the rule or behavior. For example, to see more options for the above, Greater Than Quick Analysis tool, (or any of the other Conditional Formatting options):

  • Click the Home tab on the Ribbon, Styles Group, and click the drop down arrow on Conditional Formatting icon, (Note that many more conditional formatting choices are available on this drop down list).
  • Select Manage Rules (bottom of list).
  • Select the rule, and click Edit Rule to display options. (Can also Delete Rule here)
  • Click OK to confirm any changes.

Note: If you want this tool to put in the formulas for you, such as totals, averages, counts, etc., don’t enter them yourself until you see if the automatic calculations under Totals: work for you!

See more awesome Excel tips and tricks here: https://gaylelarson.com/copy-and-paste-filtered-subtotals-or-visible-cells-only-in-excel/ or use Search for a specific topic.

Which feature is most helpful to you?

Inspect and Remove Sensitive Document Properties with Document Inspector

Excel, Office, PowerPoint, Word, , , , , ,

It may come as a shock to some just how much information Microsoft collects in Document Properties during the creation of a file. This blog reveals how to inspect and remove sensitive document properties with Document Inspector. We have enough to handle just getting the document to be correct and look the way we want to present it without worrying about broadcasting sensitive data!

Office collects personal data

There are positive uses for this information and even for creating our own custom Document Properties, but we’ll cover that in a future blog as this is about protecting your information in Word, Excel and PowerPoint.

If the document is not leaving your computer or office, it may not be an issue to have unexpected details revealed about the creation path and timeframe for editing, how many revisions and more. On the other hand, if this is confidential or going to another department, or worse yet, to a client or outside organization, there could be a problem Houston!

NOTE: Document Inspector tends to be an all or nothing thing. Might want to create and save a copy of the document before you inspect, remove and send, because it may remove elements that you want to keep in your original.

What Data is Collected

As you work, here is what starts to accumulate about your document and you (or the user who is working on it).

Click File tab to display the Info screen (Backstage) with the Properties of the current document (left screenshot):

Word Document Properties

Much of the data is what you would expect to see if you were looking at the file in a directory, but note there are fields such as Total Editing Time, Author, Last Modified, Last Printed (and by whom).

If you click the link for Show All Properties at the bottom of the screen, you see more information is revealed such as Company and Manager (shown on the screenshot on the right). There are also several fields where you, the user, can enter details to identify the file for searching and clarity.

Let’s go one step further…At the top of the Properties column (in either screen), click the dropdown arrow and then click Advanced Properties button:

The Advanced Properties dialog box will display with five tabs that collect different data:

  1. The General tab contains the information you would see with the Details view in a directory.
  2. The Summary tab is where you can choose to add your own information to identify and describe the document.
  3. Statistics tab contains some file details but adds personal data about the construction of the file.
  4. The Contents tab pulls document properties from fields. For example, if you added a Title in the Title tab, it will appear here.
  5. The Custom tab is where you can add properties from the list such as Department or Editor or create your own.

As mentioned, you may want to utilize these properties for various reasons but, for now, we’ll just concentrate on what Word is collecting and tracking, and how to get rid of the information, if needed.

Note: Previously you could display the Document Panel from Advanced Properties directly at the top of your document and fill in the property tags there. It was removed from Office 2016.

Review with Document Inspector

Let’s look at all the document and personal information being collected:

  • Click the File tab and ensure Info is selected.

Inspect Document screen

  • Click Inspect Document under the Check for Issues dropdown arrow.
  • The Document Inspector displays where you can choose what content to check for.
  • Leave them all checked and click the Inspect button at the bottom of the dialog box.

Document Inspector dialog box pre-run

The same list displays again with the requested data flagged with a red exclamation mark and a list of the information found.

Document Inspector after running

Remove Hidden Data

If you want that data deleted, click the Remove All button. Click Reinspect to ensure it is gone or remove other information.

You can now send that file without fear that it is revealing your inner most document secrets, but you might want to take it one step further if the document has ever been shared, or you have cropped images! (See below).

Document Properties in Excel and PowerPoint

Both these programs use the same method for collecting data about your file but because of their diverse purposes, track some different information. You inspect and remove the same way with the Document Inspector.

Like Word, Excel and PowerPoint collect data on:
  • Comments, and Annotations
  • Document Properties and Personal Information.
  • Invisible Content
  • Custom XML Data
Excel adds:
  • Headers and Footers
  • Hidden Rows and Columns
  • Hidden Worksheets
PowerPoint adds:
  • Off-slide Content
  • Presentation Notes
 Word adds:
  • Revisions and Versions,
  • Metadata, Microsoft SharePoint properties, custom properties, and other content information.
  • Headers, Footers, and Watermarks
  • Hidden Text
  • Task Pane add-ins

How to Inspect and Remove

The same for all programs. Go to File | Info |Check for Issues | Inspect Document.  Note the list of things that will be inspected, leave them all selected and click the Inspect button.

Check Before Sending

There are some things not covered by the Document Inspector that could cause embarrassing or legal issues if the original information remained intact. Cropped images may display as you edited in the document, but the complete original image remains unless deleted. Same is true of Tracked Changes that have been edited if someone turns on All Marks.

Delete Cropped Areas of Images

  • Click on an image
  • In the Picture Tools | Format tab | Adjust group, click Compress Pictures

Compress pictures and delete cropped areas

  • Ensure there is NO checkmark in Apply only to this picture.
  • Ensure there IS a checkmark in Delete cropped areas of pictures.
  • Click OK.

Remove Tracked Changes

Accept or reject tracked changes to remove them from your document:

  • To look at each revision one at a time, on the Review tab, click Next in the Changes group, and then Accept or Reject.

Use Ribbon to remove tracked changes

Word keeps or removes the change and then moves to the next tracked change.

  • To accept all the changes at the same time, click the arrow below Accept, and then click Accept All Changes.
  • To reject all the changes at the same time, click the arrow below Reject, and then click Reject All Changes.

IMPORTANT:  Choosing the No Markup view helps you see what the final document will look like, but it only hides tracked changes temporarily. The changes are not deleted, and they’ll appear again the next time someone opens the document. To delete the tracked changes permanently, you’ll need to accept or reject them.

Whew! Now your clean and lean document can be sent without all that hidden data. If you want more information on security for your Office files, see the related blogs…

https://gaylelarson.com/word-document-protection/

https://gaylelarson.com/delete-personal-content-from-public-computers/

Have you had any surprise experiences with sharing sensitive information? Let me know in the Comments.

 

Using Range Names in Formulas

Excel, Office, , , , , , , , ,

Have Excel Automatically Create Range Names

A quick way to create range names is to base them on heading cell text (worksheet labels). In the example shown below, the cells representing quarterly sales for all regions will be named based on the labels in columns B through E.

NOTE:  If the labels contains spaces, those are replaced with an underscore. Other invalid characters, such as & and # will be removed, or replaced by an underscore character.

Excel worksheet with column and row labels

Name Cells or Ranges Based on Worksheet Labels:

  • Select the cells that you want to name, including the labels. These can be above, below, left or right of the cells to be named. Here, we are having Excel name the four quarter labels all at once by selecting B4:E8

Selected cells with column labels for naming ranges

  • Click the Formulas tab on the Ribbon, then Create from Selection in the Defined
    Names group.

Create names from selection

  • Excel will automatically place a check mark for the location of the labels; assumes you want to use the first cell as the name (in this case Top Row of the selection).
  • Click OK to add the range names to the Name box and the Name Manager.

Name box with range names

  • Click the dropdown arrow on the Name box to see your new range names. Just click one to highlight the included cells (Quarter1 would highlight B5:B8 as B4 is the label and not included in the range).

    NOTE    : If there are spaces in the labels, they are replaced with an underscore. Quarter 1 would become Quarter_1.

Auto apply range names for the row headers by selecting the text in Column A through the numbers in Row 8 – A5:E8 (don’t include totals):

Cells selected with row labels

Repeat Steps 2, 3 and 4. Note that the check box is now Left row. Click OK.

Display range names from the Name Box:

Name box with alpha range names list

Notice that Excel alphabetizes the list no matter what the order of creation.

Before we put those names into action, we probably will want to know totals for all the eastern regions and all the western ranges at some point, so we’ll create range names for them. Also, we’ll abbreviate the individual regional names so they are much easier to use in our formulas:

Regional Annual Quarterly Sales sheet

Since we are naming the range, we only select the numerical cells:

  • Select B5:E6 (all quarterly sales for the two eastern regions)
  • Click in the Name box and type East
  • Press ENTER

Do the same for both the western regions:

  • Select B7:E8
  • Click in Name box and type West
  • Press ENTER

East and West names are added to the Name box list and the Name Manager.

Now, add a section below your existing data to capture total and average sales for the two regions:

Spreadsheet with Total and Average Sales area

We could call it done and begin using the created names in formulas but the regional names are too long, so for efficiency, let’s change them to just two letters.

Change a Named Range

After you create a named range, you might need to adjust the referred to cell references or, in our case, abbreviate the name to make it easier to use in a formula. Here’s the steps:

  • Click the Formulas tab, and in the Defined Names group, Click Name Manager icon
  • Click on the name that you want to change In the list (in this case Northeast)

Name Manager dialog box

  • Click the Edit… button or double click the name to display Edit dialog box
  • The name field shows the name highlighted
  • Type NE in the box to replace it
  • Click OK

Edit Name dialog box

  1. You can now choose the other three long regional names and replace with NW, SE and SW using Steps 3 through 6
  2. When completed, click the Close button in the Name Manager

Now we’re ready to rock and roll using them in our formulas!

Use Excel Names in Formulas

The real fun begins! We saw in earlier posts how to use range names for navigation or selection – very handy but here’s the real power. We want to find the totals for eastern and western regions for the four quarters. Because we made separate range names for the east and west regions, we can create formulas for the northern or southern regions and/or east and west like this:

  • In B13, type: =sum(ne,se) ENTER   (Range names are not case sensitive so can be typed lower case and Excel will convert)

Formula for Total Sales East Coast

Note: Another advantage of range names is that they are added to the auto display list along with function formula names and you don’t have to remember any cell references!

Next, we’ll simplify even more using the east/west names:

  • In B14, type: =sum(east) ENTER

Presto! There’s the total for the Northeast and Southeast regions.

For the average section, formulas are the same, changing the function:

  • In B17, type: =average(east) ENTER (or you could type: =average(ne,se) ENTER
  • In B18, rinse and repeat with (west) for final results!

Total and Average Sales results cells

TIP: If you need to change the cell references for a range name, open the Name Manager; select the name, and edit the contents of the Refers To box, or highlight the new range on the worksheet with the mouse, and Excel will edit for you. Click the check mark to save the change and close the Name Manager. No need to retype anything.

Your names can be created from references on one worksheet and the formulas used on another because their scope is available across the workbook. I’m pretty smitten with named ranges myself. What do you think? Do you see lots of uses for using this feature? Let me know in the Comments. Thanks!

For more information on Range Names, see:  https://gaylelarson.com/quick-navigation-excel-worksheet/ and https://gaylelarson.com/excel-name-box-for-navigation/

 

Use Excel Name Box for Navigation and Selection

Excel, Office, , , , , ,

In a previous post we covered navigating in Excel using the Name Box by typing a cell reference or a name for a cell or range of cells in the box or using the Go To dialog box (see link below).

compass for navigating your worksheet

When you name cells, they are called Range Names and become much more powerful, or at least more convenient, compared to using cell references.

Name Box for Navigation

The box reflects the current, active cell but does so much more. Although it looks separate, it is really part of the Formula Bar, so if you hide the Formula Bar, it disappears also.

The Excel Name Box

A quick recap on accessing the Name Box. There is no keyboard shortcut for landing there but you can press F5 function key to display the Go To… Dialog box and type in a cell reference in the Reference box; press ENTER or click OK, to go directly to that cell.

The Go To Dialog Box

Name Box for Selection of  Cell Ranges

Selecting huge ranges of data can be frustrating but if you know the cell references for the desired selection, the Name Box is the way to go. Just type in the range (or as close to it as you remember), i.e., a150:r8765, press ENTER, and that entire range is highlighted.

Tip: If you forgot column(s) or row(s) or included too many, just use SHFT and arrow keys to add or remove them. (Beats re-selecting two or three times).

Create Range Names in Name Box

The real power of the Name Box is to use it to create a name or ID for an often-used cell or range of cells, such as Commission or Regions, etc. You can create names that refer to cells, formulas, or a specific value.

The names can then be used for quick navigation or in a formula instead of cell references. You can create as many named ranges as desired and they are accessible from any worksheet in that workbook.

Steps to create range names:

  • Select the cell(s) that you want to name
  • Click in the Name box
  • Type a name for the cells (descriptive but short). There are some rules for range names:
    • Must start with a letter or an underscore
    • No spaces (can use an underscore to represent a space) *
    • Name may contain letters and numbers and periods but nothing that could be mistaken as a cell reference (examples: C, 2, R7C4)
    • Not case sensitive
    • Is ABSOLUTE cell referencing by default
    • Press ENTER after naming you range

 * Examples of naming a range could be: TotalSales or Total_Sales

Range Name example

Note: If there is a formula in that cell, it displays in the Formula Bar and the applied name in the Name Box.

I can now be anywhere in my workbook and return to that range with a click on the dropdown arrow in the Name box:

Name box with cell name

Now I don’t need to know the cell reference or even where it is located and can use that name in a formula, either for part of the formula or with other range names. For instance, if I had a commission rate in F8 and had named that cell, my formula would be: =TotalSales*Commission from any cell in my workbook. How sweet is that!

Name Box Limitations

There are several ways to create named ranges but the Name Box is the quickest. Be aware though that you can only create range names here, you cannot edit or delete them. That must be done from the Formulas tab on the Ribbon. You can also have Excel create range names for you using existing row and column labels to name them (covered later).

See original post for ways to navigate your worksheet:   https://gaylelarson.com/quick-navigation-excel-worksheet/

See the blog on Using Range Names in Formulas for more ways to use this great feature!  https://gaylelarson.com/use-range-names-in-formulas/

Are you liking this handy Name Box? Let me know in the Comments below!