Category Archives: Office

5 Amazing Must Have Excel Tips

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: http://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

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…

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

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

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

 

Best Features of Office Mix Will Be Integrated into PowerPoint 365

Microsoft announced it will retire Office Mix on May 1, 2018, but the best features of Office Mix will be integrated into PowerPoint 365 so it will no longer be an add-in download. This will also include Microsoft Stream and Forms for easier creation and sharing of interactive online videos for Office 365 users. It has something for everyone who wants tools for creating slick media from PowerPoint.

Office Mix has something for everyone!

What is Office Mix and Should I Care?

Mix is a PowerPoint add-in that Microsoft introduced about three years ago and is/was available for Office 2013 and above. It is pretty amazing and looks to be even more so as a feature baked directly into PowerPoint. You can create presentations videos, screen recordings, narrations, ink and audio, to name a few features, and upload to the cloud for storing and/or sharing. You can also download the converted video to your hard drive or wherever if you choose. It is directed toward educators but available for anyone with a 365 account. It even includes interactive quizzes and simulations.

Note: See the link below to my earlier blog with an overview of Office Mix.

Most of the information below is from Microsoft and includes links to relevant pages and support a for transitioning from Mix to Stream if you are a current user, and how to access these great interactive tools if you want to start.

What do you need to do to prepare for this change?

If you have a qualifying account* and would like us to migrate your existing mixes to Microsoft Stream, please click here to visit the Office Mix migration page, sign in, and follow the prompts to automatically migrate your data. When the process completes, your mixes will be stored on Microsoft Stream as videos. You can continue to access Office Mix until M‍ay 1, 2‍018.

Mixes migrated to Stream will not include analytics data, quizzes or apps. However, over time these mixes will become interactive again. If you would like to save this content, you can download your mixes as PowerPoint files (.pptx), and your analytics data as Excel files (.xlsx) to save to your storage location of choice at any time before M‍ay 1, 2‍018. Please visit our help article for more details.

If you’re an Office Mix user, you can find full details on how to migrate your mixes ahead of the shutdown at Microsoft’s support page.

Migrate your content from Office Mix

Applies To: PowerPoint 2016

Just over three years ago we launched the Office Mix Preview to help everyone from educators to business create and share interactive online recordings of their presentations. Thanks to the positive feedback from our users during the Preview, we are excited to share that we are bringing the best of Office Mix directly into PowerPoint, Microsoft Stream, and Microsoft Forms for Office 365 subscribers on Windows PCs.

This new integrated experience in PowerPoint will remove the need for downloading an add-in. You’ll be able to easily access the feature via the Recording tab in PowerPoint after you turn on the feature by customizing your PowerPoint toolbar ribbon.

In the coming months, you’ll also be able to publish these recordings to Microsoft Stream, which offers a simple way to upload and share videos securely across your organization to improve communication, participation, and learning.

If you’d like us to migrate your existing mixes to Microsoft Stream, please click here to visit the Office Mix migration page, sign in, and follow the prompts to automatically migrate your data. When the process finishes, your mixes will be stored on Microsoft Stream as videos. You can continue to access Office Mix until May 1, 2018.

This article contains details on how to back up content you currently have on mix.office.com. Please note that all content must be moved off Mix by May 1, 2018, to avoid losing it. If you take no action by that date, your files will no longer be accessible. We will continually update this article as more information becomes available.

I’m a current Office Mix user—What does this change mean for me?

The Office Mix site and existing content stored on its servers will be retired according to the following schedule:

  • October 20, 2017:   If you have an existing Office Mix account, you’ll still be able to view, edit, publish, download, and delete your existing content. If you have a qualifying* Office 365 work or school account, you can sign in to migrate your mixes as videos to Microsoft Stream. If you don’t have access to an Office 365 work or school account, you can download your Mixes as PowerPoint files (.pptx), and your analytics data as Excel files (.xlsx) to save to a storage location of your choosing.

*Office 365 plan features vary by license. See licensing details to learn if you already have access to Microsoft Stream and what features you can use, or to upgrade your plan.

  • January 1, 2018:   You’ll no longer be able to sign up as a new user or download the Office Mix add-in from the website. Existing users who already have the Mix add-in installed will still be able to use it to upload, edit, view, and download their existing content.
  • May 1, 2018:   The Office Mix site and all its content will be officially discontinued. The site will no longer be accessible after that date. Any links to your Office Mix content that you previously shared with others will stop working after this date.

How do I move my files and content from Office Mix?

For Office 365 work or school accounts

Do the following:

  1. Please visit https://mix.office.com and sign in with your Office 365 work or school account that you were using for Office Mix.

  1. Click the Migrate button.
  2. On the confirmation page, click Migrate Now.

  1. Once you click Migrate Now, you’ll be asked to sign in by using your Office 365 account; after sign-in is successful, the migration will start for all Mixes in the “Ready for Migration” state. Migration may take some time to complete, and during migration you’ll be able to get status on the migration on the website. Please visit https://mix.office.com again to confirm the results.

For Microsoft accounts (Outlook.com, Hotmail.com, Live.com, etc.) and Google and Facebook accounts

Do the following:

  1. Please visit https://mix.office.com and sign in with the account that you were using for Office Mix.
  2. Click My Mixes.
  3. Under the uploaded mixes page, click Presentation to download your mix as a PowerPoint file (.pptx). If you enabled mobile playback during upload, you may see a Video button that you can use to download a video (.mp4) of your Mix as well.
  4. To download your quiz results and analytics data, click Analytics and then click the Excel icon to download an Excel file (.xlsx). For more detailed instructions, see Export your analytics to Excel.

For Microsoft, Google, and Facebook accounts with access to a valid school email address

We can back up your mixes as videos to your Microsoft Stream account. All you need to get started is to enter a valid school email address. Please see Get Office 365 for Education for free. Students and teachers are eligible for Office 365 for Education, which includes Word, Excel, PowerPoint, OneNote, and now Microsoft Teams, plus additional classroom tools.

You can also choose to sign in to Office Mix and follow the auto-migration prompts yourself. When the process is complete, you will find all compatible content you had previously published to Office Mix backed up to your Microsoft Stream account. The original content on Office Mix will thereafter only be available to view, download, and delete.

Which Office 365 plans will include Microsoft Stream?

Stream is available currently to all the same plans that Office 365 Video was available in except (Government Community Cloud, Germany, and China).

These are the plans that include Stream:

  • Office 365 Education
  • Office 365 Education Plus
  • Office 365 Enterprise K1
  • Office 365 Enterprise K2
  • Office 365 Enterprise E1
  • Office 365 Enterprise E3
  • Office 365 Enterprise E5

Over the course of time we will be adding Stream instances and licensing to match the existing set of regions and plans supported today by Office 365 Video.

For Office 365 Accounts without Microsoft Stream

Sign in to your Office Mix profile where you can download and save your content to your device or your preferred storage and sharing platforms. You can also delete your Office Mix account and content.

What about content that I have already linked from Office Mix to other Web sites?

For users who have embedded or shared content in a Learning Management System using the Office Mix LTI tool, you can embed content using Microsoft Stream. If you want to remove linked content, simply delete the files from your My Mixes page. On May 1, 2018, all Office Mix content and links will stop working, so please make sure to manually update any embedded Mixes on your other Web sites before this date.

What if my Office Mix content exceeds my Microsoft Stream upload storage limit?

If you exceed your available storage limit on Microsoft Stream, the migration of your Office Mix content will be interrupted. Any files that were successfully transferred will remain on your Microsoft Stream account, but any files that couldn’t be included won’t be backed up and the migration process will stop.

Content migration can be resumed after you have freed up or purchased additional storage space on your Microsoft Stream account. To resume an interrupted migration, sign back in to Office Mix and then start migration again. To learn more about the quotations and limitations for Microsoft Stream, click here.

I am an Office 365 Administrator. What do I need to know?

If you are an Office 365 administrator, you can share this article with your organization. Currently, we do not support migrating content from a tenant level, so each Office Mix account holder needs to sign in and migrate his or her own mixes.

When will the Recording tab come to PowerPoint for Mac?

Currently, the Recording tab is only available to Office 365 subscribers on Windows PCs. In the future we may consider rolling out these features to other platforms over time.

When will Microsoft Stream support interactive quizzes and analytics?

We are working to bring interactivity to the Microsoft Stream video player so you can build, upload, play back, and share more Mix-like content on Microsoft Stream (that includes quizzes, ability to jump to different parts of the presentation, and more). Over time, we’ll enhance the analytics capabilities for videos on Microsoft Stream. For more information about the current capabilities of Microsoft Stream, please click here.

How do I turn on the Recording tab in PowerPoint?

My Note: I have Office 365 (Educator edition), and the Recording tab automatically appeared on the Ribbon, with this notation:

If you have Office 365 and The Recording tab is not automatically on the Ribbon, follow these Microsoft instructions:

Turn on the Recording tab of the ribbon:

On the File tab of the ribbon, click Options. In the Options dialog box, click the Customize Ribbon tab on the left. Then, in the right-hand box that lists the available ribbon tabs, select the Recording check box. Click OK. For more information about the PowerPoint Recording tab, see Record a slide show with narration and slide timings.

How do I upload a video to Microsoft Stream?

You can find the upload button at the top of any page or just drag new videos to one of your groups or channels. You can upload multiple videos at the same time and even browse Microsoft Stream while your videos are uploading in the background. In the Microsoft Stream portal, select Create > Upload a video or the “upload” icon from the top navigation bar. Drag and drop or select files from your computer or device. For more information, see Upload a video.

I need more help!

If you’re unsure of what steps to take before the Office Mix service is retired, or you are encountering any issues during your content migration, please contact support at https://officemix.uservoice.com.

We sincerely appreciate your feedback and we’ll be happy to provide further assistance with this transition.

* Office 365 plan features vary by license. See licensing details to learn if you already have access to Microsoft Stream and what features you can use, or to upgrade your plan.

Thank you for using Office Mix and being on the journey with us. If you’re unsure of what steps to take before Office Mix is retired, or you are encountering any issues during your content migration, please visit our help article.

My Note: Here is the link to my previous post on Office Mix:

http://gaylelarson.com/share-powerpoints-office-mix/

Have you been using Office Mix? Tell me about your experiences and if you are going to take advantage of the new features!

Document Protection in Word

Word document protection can be turned on by restricting style selection and types of editing. This prevents others from changing your prized content! Regardless of your version of Word, you have a lot of security control when sharing a document.

Word security lock graphic

Word Document Protection in 2007

The steps are very similar in all the later versions of Word, but the ribbon button is different as is  the wording in the 2007 task pane:

  • Click the Review tab on the ribbon.
  • Click the Protect Document button in the Protect group of the Review tab.

Word Document Protection for 2007 task pane

  • Select Restrict Formatting and Editing from the drop-down menu so there is a check mark next to the option.
  • The Restrict Formatting and Editing pane displays.

Restrict Editing in Word 2010 and Above

  • Click the Review tab on the ribbon.
  • Click the Restrict Editing button in the Protect group.
  • Limit  style formatting by clicking in that box under 1. Formatting restrictions.

Word Restrict Editing task pane options

  • The list of styles displays where you can choose approved styles and make other formatting selections:

Word Protection Formatting Restrictions for Styles

  • Limit the types of editing, such as only allowing filling in form fields or Comments  by clicking the box under 2. Editing Restrictions:

Word Protection Restrict Editing Types

  • Click Start enforcement when you have applied all desired restrictions.

Exceptions

You can allow editing to select parts of the document even if you have made it Read Only. If you are on a domain, specific users can be selected by name in More users… under the Groups: section. This option will display after checking the box under 2. Editing restrictions. (See link below).

Note: Ignore the Restrict permission… link under the See also section at the very bottom of the task pane, as it requires Information Rights Management and must be installed and enabled by your company. If not configured, you will get this dialog box:

Word Protection IRM Alert Display

Permit Changes to Parts of a Document

Word document protection can be configured to allow changes to only select areas of your document. There is an excellent step by step instruction for permitting editing parts of your document at the Microsoft Support Site:

Allow Changes to Parts of a Protected Document

PowerPoint Designer Works with Pictures and Charts

PowerPoint Design Ideas in Designer works with pictures and charts and is built into PowerPoint 365. Have the Design Ideas suggestions give you options for arranging text, photos, or other graphics along with any included text automatically for stunning slides!

Note: Requires Office 365 subscription. (For PowerPoint Online, PowerPoint Designer is available to subscribers when they are using files stored in SharePoint Online.)

PowerPoint Designer works with pictures and charts

PowerPoint Design Ideas for Pictures or Charts

PowerPoint Designer automatically suggests options for you when you’re online and you add a picture or chart to your PowerPoint presentation.

  • Click the slide to which you want to add a picture, chart or any type of graphic
  • Click Insert tab and choose Pictures, Chart or another graphic
  • The first time you use Designer, a message will appear asking your permission to get design ideas for you. If you want to use Designer, select Turn on.

Turn on Intelligent Services for Design Ideas

Once you’ve turned on intelligent services, PowerPoint will automatically show you design ideas when you upload photographic content.

Add Photo to Cover Slide

Designer will work on any slide layout. For instance, add a photo of your own or from the web to your Title slide.

Designer opens a task pane for you with suggestions. Click a thumbnail to apply that design idea to your slide:

Title Slide Designer Design Ideas

Previous two graphics from Microsoft.

Tip:   You can open Designer any time by going to Design tab, Design Ideas in Designer group.

Combine Text and Graphics with PowerPoint Design Ideas

If you just have steps in bullets or numbering and want to insert or rearrange an existing graphic, Designer comes to the rescue here too.

Inserting a Picture:

  • Click anywhere on the desired slide
  • Click Insert tab and make your choice of graphic type from the Illustrations group
  • Insert the graphic
  • Designer will display the visual options on right
  • Scroll down and click the thumbnails to preview

Insert graphic directly over slide text

  • Choose one and make that eye-appealing (and mouth-watering) slide!

Design Ideas to combine text and graphics

Big disclaimer here…Don’t try this recipe at home or anywhere else – for design demo purposes only. I’m guessing – not edible!

A Real Tip: You can add more than one photo to a slide and the Designer will automatically adjust the options to reflect the new addition(s).

Add Design to Existing Graphic Slide

You’ve inserted a picture with no text on a new Title and Content Layout slide, and want to see what Designer would do with it:

  • Click on the picture
  • If Design Ideas does not display, click Design tab and choose from end of Ribbon*
  • Design ideas displayed will depend on your slide layout. (Experiment with different slide layouts by right clicking over the slide thumbnail in the Navigator at left, and Designer will auto-adjust to new layout)
  • You can choose a design that adds text or one that is just the best arrangement for your picture

Design Ideas for existing slide pictures

*If you are using the Designer regularly, add it to the QAT: Right click over its icon on the Ribbon and choose Add to Quick Access toolbar. It will now be available with one click whenever needed.

Note: When inserting pictures from online or any source not your own, always ensure that it is copyright-free. To use, you may have to seek permission and/or give credit to the owner of the content. However, there are many sources that are in the public domain and/or are license-free and safe to use, such as Creative Commons License Zero (CC0); emphasis on the 0.

Install Add-ins to PowerPoint and Word for Free Images

Speaking of free and safe images to use, there are plenty available on a couple of Add-ins. Again, it may depend on what version of Office you are using, but here’s how to install from the Microsoft Store:

  • Inside PowerPoint or Word, click the Office Add-ins icon under Home tab (far right on Ribbon)
  • In the Search, type Pickit to display it for download
  • Download and follow the instructions on the pop-up
  • Do the same steps again but in Search, type Pexels

You now have access to free images from both apps in both programs. How cool is that! One odd thing to note…Pickit gets installed at the end of the Home tab, and Pexels gets installed under the Insert tab in both programs. A head-scratcher but they’re free so we don’t criticize! To use either, just click on their icon which will display a task pane to the right; type in a category or specific item you are looking for and let the images flow. You may find some videos as well!

Auto Arrange Chart and Text on a Slide

Designer is versatile and can make a spiffy chart slide (from Excel) in no time too…

  • I created a new Title and Content slide
  • Typed the Title text and the line of text in the Content area
  • Clicked Insert tab, Chart (column). You could also click on the Chart icon in the slide
  • After you choose your chart, an Excel worksheet window auto displays with guide content for you to replace
  • As you fill in your data, the chart is being created on your slide. It may look a mess (like this one) but no need to faint as fixable with a click!

Use Insert Chart to add to PowerPoint slide

  • When completed, close the Excel window to return to your PowerPoint
  • Click on chart, if needed, and click on your Designer shortcut on the QAT (you added it right?) as it may not automatically display.
  • Choose your favorite look from the options
  • Add a title to chart and done! You could also size the chart and the size and/or move them manually, if desired

Designer's Design Ideas for chart layout

Designer is so amazing. No longer do you spend precious time wrangling with the slide layout to create dazzling presentations, and new features get added monthly to Office so be sure to check them out on a regular basis. It makes us look so smart!

Want to use Designer to convert a bulleted or numbered list to an amazing graphic? Here you go:  http://gaylelarson.com/powerpoint-designer-converts-lists-to-graphics/

Hope you had fun with this. What new things have you discovered in Office 365? Let me know in the Comments below!

Convert Slide Lists to Graphics with PowerPoint Designer

Built into Office 365, is the ability to convert slide lists to graphics with PowerPoint Designer. Make that bulleted or numbered list in into an eye-catching graphic.

Note: Requires Office 365 subscription. (For PowerPoint Online, PowerPoint Designer is available to subscribers when they are using files stored in SharePoint Online.)

Design Ideas with PowerPoint Designer

Jazz Up Bulleted Lists with PowerPoint Designer

Here is my original, boring bulleted slide:

Convert bulleted list to graphic

I want to jazz up my boring bullets and have Designer automatically give me the task pane to choose an image. It will analyze my list and display its best suggestions at the right. This can be any kind of list, whether actual steps or relevant points.

If my bulleted list is obviously steps or a process, Designer will display automatically, but if not, I can manually display design suggestions even if not a process list:

  • Select the bulleted list

Manually display Design Ideas

  • Click Design tab, choose Design Ideas from Designer group

Design Ideas icon on Ribbon

  • Scroll through and click on the suggested Design Ideas in the task pane to insert into slide, such as:

Graphic for bullet steps

Jazz up a Numbered List

I’ll get some different results if it is a numbered rather than bulleted list:

  • Select the list
  • Click the Numbering icon in the Paragraph group, Home tab
  • The Designer will display with different suggestions…

Use Designer with a numbered list

  • No more boring slide – totally rearranged with one mouse click!

PowerPoint Design Ideas for Lists

Play around with these for interesting results. This is fast and easy but the suggestions may be limited and I may want more graphic selections. There’s an app for that, so to speak! Although a little more manual, I can achieve similar results with SmartArt like below…

Convert Bulleted List into Graphic with SmartArt

For more selections:

  • Select the bulleted list
  • Right click over selection, choose Convert to SmartArt from list
  • Roll over available options to see Live Preview. If you want additional selections, click More SmartArt Graphics… at the bottom

Convert PowerPoint list to SmartArt

Preview SmartArt designs for bulleted list

  • (Note you don’t get Live Preview here as the object change shows at right). After inserting your choice, any time you click on that part of the slide, the special tabs and groups for SmartArt will display on the Ribbon so you can edit the design as much as you want.

TIP: Which type of list should I use – bullets or numbering? Use a numbered list if you want order of priority or specific steps. Use the bulleted list for items that don’t need to be in order of process.

Think this is an awesome feature? Well it is, but it gets even better when you add photos, charts or other graphics, and let Designer make those slides sing! You can see them and use them too, with this blog:

Also, a couple of other blog posts that may interest you as they involve SmartArt and graphics with PowerPoint but don’t require Designer:  http://gaylelarson.com/convert-slide-bullets-to-smartart-graphics/; and http://gaylelarson.com/powerpoint-quickstarter-for-research/

Let me know in the Comments below how you are using Designer or other graphic tools to liven up that presentation!

Use Pick Up Style in PowerPoint to Repeat Object Formatting

What is the Pick Up Style?

The Pick Up Style feature allows you to copy formatting of an object including its text to another object, similar to a color picker.

You can use the keyboard shortcuts or add the Pick Up Style and Apply Style icons to the Quick Access toolbar. We’ll look at both.

The shortcuts are the Copy/Paste ones with the SHIFT key added so that only the style and formatting are copied:

  • Pick Up Style: CTRL+SHIFT+C
  • Apply Style: CTRL+SHIFT+V

If you are not a keyboard shortcut fan, just add the icons to the Quick Access Toolbar (QAT) as follows…

Add Pick Up Style to Quick Access Toolbar

The QAT as it is fondly known, is a super tool included in all Office applications for quick access to your most used commands. There are several ways to access its Customize screen to add and remove commands.  We’ll add the Pick Up Style and the Apply Style icons:

  • Right click on the Ribbon or the QAT and choose Customize Quick Access Toolbar, (or click the dropdown arrow at the end of the QAT, and choose More Commands)
  • In the PowerPoint Options dialog box, change the Popular Commands option to All Commands. (You could also choose Commands Not in The Ribbon in this instance, but always choosing the former will save you time and aggravation down the road)

PowerPoint Quick Access toolbar dialog box

  • Scroll down in left pane and click Pick Up Style
  • Click Add to display It at the bottom of the QAT. (If you want it to display in a specific location on the QAT, click the command above that location in the right pane before choosing Add)

PowerPoint Options Pick Up Style command

  • Click Apply Style in left pane (ensure Pick Up Style is selected in right pane)
  • Click Add
  • You can use the arrow boxes to move the commands up or down in the list, if needed
  • Click OK to return to your presentation

     

    You now have these two icons on the QAT. Note that the Pick Up Style has the Up arrow and the Apply Style has the down arrow:

PowerPoint Pick Up Style and Apply Style icons

Apply Style to Object(s)

We’ll use Shapes here for an easy example of how efficient these tools are:

  • Click Insert tab, and in the Illustrations group, click dropdown arrow on Shapes
  • Draw any shape on the slide (see example below)
  • Format the shape and type and format text
  • Draw another shape and add text to it

PowerPoint Shapes inserted before Pick Up Style

  • Select Shape 1 and click Pick Up Style icon on QAT (or press CTRL+SHIFT+C)
  • Select Shape 2 and click Apply Style icon (or press CTRL+SHIFT+V)

PowerPoint Shapes formatted with Apply Styles

Your shapes are now matched in color and text formatting!

NOTE: You can also use the Format Painter to copy and paste formatting but the actions have to be in succession (like using F4 to repeat last action), whereas the Pick Up Style is remembered even if Apply Style is used later, so you can continue to apply to multiple objects while working in the presentation.

TIP: If the QAT gets too cluttered, remove any command with a right click over desired icon; choose Remove from QAT

I hope this tool makes creating presentations faster and more enjoyable for you. Want more automatic formatting features in PowerPoint? Have a look at this blog:  http://gaylelarson.com/convert-slide-bullets-to-smartart-graphics/

Let me know the features you use to speed up your presentation masterpieces in the Comments below!

Using Range Names in Formulas

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:  http://gaylelarson.com/quick-navigation-excel-worksheet/ and http://gaylelarson.com/excel-name-box-for-navigation/

 

Use Excel Name Box for Navigation and Selection

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:   http://gaylelarson.com/quick-navigation-excel-worksheet/

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

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

Use PowerPoint QuickStarter to Research a Topic

PowerPoint QuickStarter template builds an outline based on your requested subject and the slides you select to help you get started researching that subject.

Note: QuickStarter is only available in Office 365 (subscription) PowerPoint.

Bike racers represent PowerPoint Quickstarter

Create QuickStarter Template

To create an outline with QuickStarter template:

  • In PowerPoint 2016, select File > New. Then select the QuickStarter template:

QuickStarter Template

  • If you haven’t already turned on Office Intelligent Services, a dialog box displays asking you to do so. (Intelligent Services needs to be turned on to use QuickStarter).

Intelligent Services dialog box

  • Specify a subject to search on, or choose from a popular subject shown at the bottom of the dialog box.

QuickStarter Search options

I typed “Create a Marketing Report” in the Search box and then clicked on Social media marketing tile:

QuickStarter suggested categories

QuickStarter then gathers information and presents you with a list of subjects related to your search topic.

Note: If you see inappropriate content, move your mouse pointer over it, click the more (…) command in the upper right corner of the icon, and select Report inappropriate topic.



I like all the content for my Social Media topic, so left all slides selected.

Create Your Presentation with QuickStarter

Steps to complete the presentation from the template:

  1. All the subjects may be selected by default but you can just select the boxes for topics you want to include in the outline that QuickStarter will assemble for you.
  2. Click Next to confirm the list of subjects you’ve selected to include in the outline. (Each topic you selected becomes a slide in the outline.)
  3. Choose a design for the slides in the presentation, and then click Create. (Don’t worry about the lack of selection here, you can change the look later).


  1. PowerPoint 2016 puts together your outline presentation and opens it for you.
  2. Look over the outline and start doing your research. Use the key facts (on slide 1) and related topics (slide 2) for ideas as you research and write about your topic. (Also, there may be additional information, such as a Summary, in the Notes below the slide).


Note: There may or may not be pictures on your slides, depending on the subject you chose. You can add, remove or change any of these.

The QuickStarter outline includes:

  • Two slides of information gathered from online sources:
    • The first slide lists key facts on the slide and in the Notes pane below the slide.
    • The second slide lists related topics for you to research. (These two slides are just for you, so they are hidden from view when you present your slide show to others.)
  • A title slide.
  • A table of contents, based on selections you made.
  • A slide for each subject, sometimes with additional suggested points in the Notes below the slide.
  • A concluding slide, “Works Cited,” where you list the sources of information you used for research.

Any Images included in the outline are public domain or licensed under Creative Commons.

Research Information Online with Smart Lookup

The first slide in the presentation is, “Here’s your outline.” It includes a summary about the topic, and usually includes dates and other kinds of useful information. Any paragraph on the slide that displays an ellipsis (…) is repeated in full in the slide Notes below.

The Outline is a great guide but you’ll want to fill in the holes in your subject details. The second slide in the presentation is “Related topics.” which lists terms you can use for research. Smart Lookup returns definitions and search results from Wikipedia and related websites.

Use the Smart Lookup feature to expand on related topics from Slide 2:

  • In Slide 2, right click a word or phrase you want to research.
  • Click on Smart Lookup to display search results in a task pane to right of your slide.


  • Insert any desired information.

Other slides in the presentation are suggested topics (based on the subjects you chose originally). Some slides may include “Consider talking about” points in the Notes pane on that slide.

Designer Feature for Design Ideas

To add variety to the look and feel of your presentation, open Design Ideas by going to Design tab, Designer group and click Design Ideas.


Click on a slide to display different design layouts for the text and images on that slide.

You can also use all the normal design features available in all presentations.

Celebrate your masterpiece!

No excuse anymore for not having a stunning presentation with captivating data. If you use PowerPoint at all, this feature alone is worth switching or upgrading to Office 365.

What do you think about this new feature? Let me know in the Comments below.