Tag Archives: Office

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!

Microsoft Snip, Windows Newest Screen Capture Tool

Office, Windows, , , , , , , ,

Snip Vs. The Snipping Tool

The new, free Snip tool takes the familiar Windows Snipping Tool that has been included in the Accessories folder of the operating system (in any version above XP) to a new level. While the Snipping Tool is a great screen capture tool and includes basic annotation and highlighting features, it lacks some capabilities found in Microsoft Snip.

Snip is one of several “garage” projects created by Microsoft employees who are being encouraged to create apps outside of the regular Windows and Office programs. How awesome is that! It is not included but can be downloaded free to Windows 7 and above. You can download it here and also see examples of how people use this tool: Download Here

Windows Snip, Show and Tell

Snip has three main steps of Capture, Annotate and Share and different ways to make that happen. A great feature is that it saves each capture in a Library so that you can use the snips again. It also allows voice recording, and saving as MP4 video.

Screen Capture with Snip

Once installed and opened, the dialog box will position itself at the top center of your window but you can drag it to any border or float it anywhere on your screen.

Activate a screen capture with the by clicking the Capture icon or pressing the PrtScrn keyboard shortcut which is automatically assigned.

Windows Snip, Print Screen key

You can click on the icons directly for the specific type of capture. The main menu is divided into three primary functions, Capture, Whiteboard and Camera.

Windows Snip, Capture toolbar

Snip Editor Toolbar

If the PrtScrn key is pressed or the Capture icon clicked and either the whole screen or a portion selected, the Snip Editor toolbar displays with options to record, annotate, highlight, and more.

Captures are automatically copied to the Windows Clipboard and any audio added will automatically be converted to a MP4 file. You can then embed on websites, play as video and/or save to desired location.

Windows Snip, Snip Editor toolbar

The Capture button can be used to get screen shots by either cropping the desirable section or by capturing the entire screen. The default selection is generally entire screen and can be captured with a click or by pressing Enter which displays the Snip Editor above.

Whiteboard is more like an extended paint tool and it will let you highlight and scribble and simultaneously explain what you are doing through voice recording.

Last but definitely not least, is the Camera capture button, which simply switches on your webcam and allows you to take a picture of yourself. Editing options are same for Capture, Whiteboard and Camera.

Note: The Record button records the annotations and other screen actions as well as any voice audio you use to explain your onscreen activity. The screen can be shared by email or saved to your hard drive.

Customize Options for Snip Tool

There aren’t many but you can control a couple of things from the Settings icon on the Snip Editor toolbar. If you don’t want the PrntScrn key to auto capture the screen, you can remove that shortcut. There is a delay feature but I haven’t found any reason to use it as, unlike the Snipping Tool, you can capture drop-down menus and dialog boxes by displaying them, then pressing the PrntScrn key (which is why I leave it active) – a huge advantage!

Windows Snip, Snip Editor Settings

Take a screenshot in Windows 10 and Windows 8

Windows 8 and 10 users can press the Windows and PrntScrn keys together to capture the entire screen and the image will automatically save to a Screenshot folder inside of the Pictures library. Earlier Windows versions copy to the Clipboard.

Take a screenshot on a Windows tablet or smartphone

Windows tablet owners can take a screenshot by pressing both the Windows button and Volume–down key at the same time. The image will auto save in the Screenshots folder in the Pictures library.

On a Windows 8.1 Phone, you do this by pressing the Power button and Volume–up key together. On Windows 8 phones, press the Start button and Power button at the same time. Screenshots are automatically saved in the Photos
Hub section

Summary

It is exciting to see Microsoft releasing these “garage” projects that are innovative and free. Although there are many other programs that can do the same or similar things to Snip, most are not free (definitely a bonus)! Other ventures from Microsoft include Sway (online creative graphics) and Mix for PowerPoint which you can download into that application. Along with the purchase of other programs such as Wunderlist and LinkedIn, there seems to be a move towards creative features that can be used in both Office 365 and Windows.

Want to try Mix? Go to my blog post on Mix here for more information: PowerPoint Mix on gaylelarson.com

Go here to see my previous post on the Windows Snipping Tool: Windows Snipping tool blog on gaylelarson.com

Have you played with Snip? What feature do you like best?

Balancing Column Lengths in Word Document

Office, Word, , , , , , , ,

Do your column lengths look a little out of balance in that otherwise perfect document?

Have you struggled with creating columns in Word to get that nice, professional look only to have them display off balance, with one column much longer than the other(s)? Here’s some ways to whip that into shape!

Word allows you to give your work a more magazine or newspaper look by breaking up the document or specific paragraphs into columns. You can have Word do this automatically, or specify where you want the column breaks.

Deciding the number of columns will be based on your margins, font sizes, line and paragraph spacing, graphics and any additional settings. The fast, easy way is to let Word do the lifting.

Have Word Create Column Breaks Based on Selection

If you want specific content to be displayed in columns:

  • Select the contents that you want to display in two or more columns
  • Click the Layout tab on the Ribbon
  • In the Page Setup group, choose the drop down on Columns
  • Choose the number of desired columns

Word will automatically add section breaks at the top and bottom of the column content and make the columns as even as possible. If you add content at the end of the column(s), Word will rebalance them.

Inserting Columns with No Content Selected

If you want the entire document displayed in columns, click anywhere in the document, and follow the steps above.

The difference here is that Word does not insert section breaks so will not automatically adjust column lengths as you add content.

If the columns are not equal length, you can have Word do it by inserting a continuous section break at the end of the last column:

  1.  Click at the end of the text in the last column
  2.  Display the Page Layout tab of the ribbon
  3.  Click on the drop-down on Breaks in the Page Setup group
  4. Choose Continuous under Section Breaks section


Note: If you like the results, you are good to go but if you want to change the column widths or adjust space between columns, you can select the More
Columns… command at the bottom of the Columns drop down.

Although Word is not a full-blown desktop publisher such as InDesign or even Publisher, it can produce some pretty fine looking work just by using some built-in templates or tools. There are more adjustments you can make such as having your heading (and other content) be in one column and the rest of the document in multiple columns. We’ll look at that in a future post.

Have you used this feature in Word before? Did you get the expected results? If you haven’t experimented with columns, give it a try and let me know how it went!

Conditional Formatting for Clarity and Visual Impact in Excel

Excel, Office, , , , , , ,

Light Up the Cells with Conditional Formatting!

Conditional Formatting is a great tool for instant, visual results based on values, text or formulas in one or more cells. This is accomplished by creating rules for each desired result. It can be as simple as formatting all cells based on their values (the default), which could be applying a color in cell(s) that are above or below a certain value, contain specific text or fall within certain dates as well answer more complex questions.

Conditional Formatting is located on the Home tab | Styles group:

You can also choose to have the results displayed as Data Bars, Color Scales or Icon Sets (arrows or star ratings) instead of one solid color.

More than one condition can be applied to the same range of cells. This example below is returning two different results with two different cell colors based on two different questions (AND requires both conditions to be true but OR allows for either condition to be true to apply the rule).

The formulas in the last two columns have returned Yes or No based on True or False results. Range Names have been created from the header row text to make for easy identification of the cell references in the formulas. Then Conditional Formatting rules have been applied to designate the color(s) based on that answer.

=IF(AND(Years_Under_Contract<2,Number_of_Books_in_Print>4)=TRUE,”Yes”,”No”)

=IF(OR(Years_Under_Contract>5,Number_of_Books_in_Print>=10)=TRUE,”Yes”,”No”)

It is now very clear to see how many authors met none, one or both of the conditions.

Formatting Rules

There are four formatting rules applied here:

Rule Types

This is the Rule Type applied to the first rule for formatting only cells that contain “No” in the last two columns with Pink fill:


Each of the other three rules have their own Rule Description but all are based on Format only cells that contain (the second rule).

Note: You can include a formula directly in the condition by choosing the Rule Type: Use a formula to determine which cells to format and typing the formula in the Edit the Rule Description area.

Have you been using Conditional Formatting and, if so, which rule do you use the most? If not, I hope this post will encourage you to use this powerful feature. Thanks for reading!

Repeat Actions in the Outlook Mail Folder with Quick Steps

Office, Outlook, , , , , , , ,

Are you frustrated with performing the same actions over and over again in your Inbox? There’s an app for that, so to speak! Speed up organization and save time with Outlook’s amazing Quick Steps feature.

Default Quick Steps Built into Outlook

In your Inbox, access the Quick Steps group from the Home tab on the Ribbon. There are six default actions already built into Outlook 2010 and above:

Move To ?: If you have created a folder such as Save or Later, or frequently move emails to a specific folder, use this command. Outlook will move messages to the last folder you moved a message to (replaces the ? with the name of that folder).

Team Email:* Sends a message to everyone in a group or project that is pre-populated.

Reply & Delete: The name pretty much says it all: When you select this Quick Step, Outlook automatically opens a Message form for replying to the sender of the selected message and moves the selected message to the Deleted Items folder.

To Manager:* Forward a message automatically to person(s) you have set up. The original remains in your Inbox.

Done: This marks the selected message with the Mark Complete flag, marks the message as read, and moves the message to a designated folder.

Create New: This opens the Edit Quick Step Wizard, which allows you to create your own custom Quick Steps.

*If you are on Exchange Server, these will be automatically set up for you. If not, you can set up your own list(s).

There are additional templates and you can categorize, flag, mark as read, set up a meeting with specific people or click Custom to create your own Quick Step with the Edit Quick Step dialog box. You give your Quick Step an appropriate name to make available for repeated use:

How to Set up a Quick Step

Except for the Reply & Delete Quick Step, each of these Quick Steps requires you to make some decisions but you only have to do this once. Then, Outlook automatically repeats the actions whenever you select that Quick Step.

To use one of the six listed steps in the group, just click on desired step, such as Move to: ? to display the First Time Setup dialog box. Each of the Quick Steps is a bit different, but, here are the steps using the Move To:? Quick Step:

  • Click the Inbox icon in the Folder pane to display a list of incoming mail messages.
  • Select a message in the Inbox. (It can be any message. Don’t worry about it actually being moved. As long as this is the first time you’re using the Move To Quick Step, the message you select won’t be moved. Outlook just needs to know which type of Outlook Quick Step element you are creating).
  • Click the Home tab and click Move To: in the upper-left corner of the Quick Steps box. The First Time Setup dialog box opens. (If the dialog box already has a folder name in it, Outlook is just suggesting the last folder to which you moved a message).

  • Type in a name for the Quick Step in the First Time Setup dialog box.
  • Select a folder to where the Quick Step will move messages in the Move to Folder box by clicking the arrow at the end of the box. (If you don’t see the folder you want, choose the Other Folder selection, which opens the Select Folder window so you can see a detailed list of all available folders. You can also create a new folder using the Select Folder window).
  • Make sure the Move to Folder check box is selected.
  • Ensure the Mark as Read check box is also selected if you want each message marked as read when the Quick Step moves it.
  • To make changes to the Quick Step’s icon, add actions to it, or create a keyboard shortcut for the Quick Step, click the Options button to access those settings.
  • Click the Save button to close the First Time Setup dialog box.

Now, whenever you want to move message(s) to the specific folder, just select the message(s) and click the Quick Step you created. Message(s) will automatically move to the folder and be marked as read.

Manage Quick Steps

There are several additional options available for this feature, such as edit, delete, change the order displayed and duplicate Quick Steps:

In Mail, on the Home tab, in the Quick Steps group, click the More arrow at the side of the Quick Steps box, and then click Manage Quick Steps. You can also manipulate choices by opening the Quick Steps dialog box (the Launcher icon on the bottom right of the Quick Steps group)

Create a Custom Quick Step

Create your own Quick Step or perform multiple actions, by customizing – sort of a…If this, then do that, scenario.

You can customize Quick Steps one of several ways:

  • Click Create New in the Quick Steps group, or
  • Click the More arrow icon, pointing at New Quick Step and choosing Custom, or
  • Click the Launcher icon in the lower right corner of the group to open the Manage Quick Steps dialog box; click the drop down arrow on the New button and select Custom.

Any one of these methods will display the Edit Quick Step box where you can start creating the actions:

  • Type a name for the Quick Step.
  • Choose the first action from the drop down list, i.e., Copy to folder.
  • Click the Add Action button and choose the next action, i.e., Categorize, Create a Meeting, Forward Message, etc.
  • If you want further actions, click Add Action again and choose from the list. (All of the actions give options to add details and steps will display separately when applied for any desired editing of information, such as entering meeting times and recipient copies).
  • Create a shortcut key, if desired.
  • Create a tooltip that describes the actions, if desired. This will display when the mouse is pointed at the Quick Step.
  • Click the Finish button when completed, and that Quick Step name will appear in the Quick Step group.

Now you can just click on any message you want to apply those steps to and done. How cool is that!

NOTE: You can edit any existing Quick Step you have created by selecting the name and clicking the Edit button in the Manage Quick Steps dialog box.

Outlook is so powerful and can be confusing with all its features but you can take charge by doing one Quick Step at a time! Speaking of confusion, there is some around the difference between Quick Step, Quick Part and Rules. I’ll be covering the latter two down the road but this might help…

Quick Steps vs. Rules

Rules typically are always on and run automatically. An example would be, when a message is received from a specified person, it is moved automatically to the folder you designated. Outlook includes rule templates for common scenarios. You can use these rule templates, or design your own custom rules. Quick Steps are applied manually by choosing the appropriate Step(s) when desired.

Are you going to use Quick Steps now? Let me know in the Comments if you are have created your own or just gone with those that are built-in and how they have streamlined your Inbox.

Happy computing!

Share your PowerPoint with Office Presentation Service

Office, PowerPoint, , , , , , , , ,

Launch an Online Meeting from PowerPoint

There are now many ways you can share your presentation over the Web. Participants can join you on any device from any location using the Office Presentation Service (free Microsoft service) or Skype for Business (formerly called Lync). If using Lync meeting they have access to the slide deck with IM and audio. You can also send a link to the slides.

Use Office Presentation Service

The only thing you need to share your masterpiece is a free Microsoft account, such as Hotmail, Outlook.com, MSN, Live, Xbox or OneDrive (formerly SkyDrive). If you don’t have one yet, go to www.onedrive.com and create a free account. That way, you will also have several gigabytes of free cloud storage for your use!

In PowerPoint, close any open presentations that you don’t want to share, then:

  • Click File/Share/Present Online
  • Click the check box for Enable remote viewers to download the presentation

  • Click Present Online icon.

To send your meeting invitation to attendees, choose one of these methods:

  • Copy link and paste it somewhere others can access it
  • Send in email

Note: You can’t email your presentation directly from PowerPoint on a Windows RT PC. Instead, open your email program, create an email, and attach the presentation.

When you are ready to start your presentation, click Start Presentation.

To end your online presentation, press ESC to get out of Slide Show View, and then click End Online Presentation on the Present Online tab,

Click End Online Presentation button to confirm that you want to end the presentation and disconnect.

Use Skype for Business to Join or Share Presentations

You can schedule an online meeting ahead of time or start a meeting immediately within PowerPoint using Skype for Business. You need to have a microphone connected to your PC, so you can speak to your meeting attendees.

Note: This feature isn’t available in Office on a Windows RT PC.

Click the drop down arrow on Present Online on the Slide Show tab. If you don’t have Skype for Business installed, it will not appear in the Present Online dropdown list. (You will only see Office Presentation Service).

A list of active Skype for Business conversations and scheduled Skype for Business meetings (within 30 minutes) will appear or you can start a new meeting immediately.

In the list, pick a scheduled meeting or click Start a new Skype Meeting, and then click OK.

Begin a new meeting, by choosing Invite More People. Choose contacts from the contacts list or type each name in the box, and then click Select under Invite by Name or Phone Number.

Begin your presentation.

NOTE: Use the icons to manage audio devices and sound, video, and the content you want to share. Icon colors alert to status. If blue, means actively using, dark grey is available, and light grey means that function is not available.

More to come in other ways to share a PowerPoint presentation. Office 2016 had added new features. Stay tuned!