Block all Windows notifications when Screen Sharing

I see it all the time – someone is sharing their screen during a meeting and an email notification pops up, usually to the audience’s amusement and the presenter’s chagrin.

There’s a simple trick you can do to block Windows (and Microsoft Outlook) from interrupting your screen sharing.

1. Open the Windows (Start) menu and type “Present”.
2. Select the first item titled “Adjust settings before giving a presentation”

 


3. Click the check box at the top of the window that appears “I am currently giving a presentation”

 


This will add the following icon to your icon tray:
 

 

4. When you are done presenting, simply right-click on this icon and select “Stop Presentation”

 

Good luck with your presentations!

PSA: Microsoft Visio & Project do not AutoRecover by default

Thank G-d, this has never been an issue for me, however a co-worker of mine lost all of her work the other day in Visio because this option was not enabled! In trying to help her recover her file, I learned that unlike in Microsoft Word, Excel, or PowerPoint, Microsoft Visio and Microsoft Project do not have the AutoRecover option turned on by default.

To enable this option in Microsoft Office 2010 and above, click on the File tab, followed by Options in the left-hand list.

Next, select Save and then check the box by Save AutoRecover information every 10 minutes. (I think the default 10 minutes is probably fine for most people, if you’re working on a document with lots of changes you may want to decrease this to every 5 or even every 1 minute).

Click OK to save this setting and exit the Options window.

Hopefully this keeps you from losing all your work next time Visio or Project crashes while you’re working!

~Yosef

Visio AutoRecovery Setting

Help! Why won’t my Excel formula calculate?!

Note: These instructions are based off Excel 2010 but are applicable for Excel 2007 through Excel 2013 (Office 365).

Has someone sent you a spreadsheet or perhaps you’re working on one and all of a sudden your formulas look like formulas and won’t calculate? Here are the top solutions I have come across for fixing this common error.

1. Cell text formatting is set to “Text” – If the formatting of the cell has been set to Text instead of General or some other format, the cell will not calculate because it assumes that anything in the cell is text and not a formula. Change the formatting of the cell by pulling down on the drop down in the Number section on the Home tab of the menu ribbon





2. Show Formulas (Ctrl + ~) has been selected / pressed – If the Show Formulas option has been selected, calculations will show their full formula and not show the calculated results. To toggle back and forth, you can either use the hotkey combo Ctrl + ~ or select/deselect the Show Formulas option in the Formula Auditing section (I highly recommend learning to use all tools in this section when troubleshooting Excel problems!) on the Formulas tab of the menu ribbon.



3. An apostrophe (‘) has been placed before the equals (=) sign – Typically someone will have done this on purpose, however placing an apostrophe before a formula will make the cell mimic the first solution listed above where the cell formatting is set to Text. Remove the apostrophe to force the cell to calculate.



4. Automatic calculation of the worksheet / cell has been turned off – If you’ve been sent an Excel sheet that’s very large, sometimes people will turn off automatic calculations so that the spreadsheet doesn’t break your computer upon opening it. To turn on or off automatic calculations within your worksheet, click on the pull down menu of the Calculation Options in the Calculation section of the Formulas tab on the ribbon bar. Alternatively, this setting can be reached by going to the File tab, selecting Options, going to the Formulas tab and then setting the Calculations options there.

Hope this helps when you get stuck! I would appreciate hearing if anyone has come across other solutions to this common complaint?

~Yosef

Excel: How to use the INDIRECT function

The Excel INDIRECT function is useful for displaying data in another cell by passing it text strings to tell it where the value is.

The syntax for the Indirect function is as follows:

INDIRECT(ref_text,[a1])

Where:

ref_text is a text reference to another cell & [a1] is a logical value that specifies what type of reference style ref_text is using.

In excel you have two types of cell reference styles:

1. A1 style is where each row has a number and each column has a letter (the default view in excel).
2. R1C1 style is where each row has a number and each column has a number.

A1 is indicated by [a1] = “TRUE”, R1C1 is indicated by [a1] = “FALSE” in the INDIRECT formula.

For example in order to display a specific column of data in a given row (in this example, row 7), the following function is used:

=INDIRECT($B$1&$A7,TRUE)

The first argument, $B$1&$A7 concatenates the values in cell B1 & A7 together to create a text statement of where the data that needs to be displayed is stored. In this case B1 = “Data!B”, A7 = 3 so the final result is “Data!B3” so the value displayed in the cell is the value on the Data tab in the B3 cell.

Column A now displays the row of the data for that particular project on the “Data” tab.

Hope you find this useful!

~Yosef

How to Hide Pivot Chart Filters

(Directions below are for Excel 2010)

I recently built a simple dashboard using pivot tables with corresponding pivot charts. I was asked by my client if I could remove the filters from the pivot charts so that they would have a cleaner look for presenting. I had never worried about this before and didn’t know how off the top of my head so I did a couple of quick Google searches & couldn’t find any references on how to hide the filters! I was very surprised that I couldn’t find anyone else asking the question so I thought I would share how to easily do this. Luckily it’s pretty simple – I just didn’t realize these options existed!

Here’s a Pivot chart with the usual filters visible:

To remove these filters, click the “Field Buttons” (or drop down for more control) button on the Analyze tab of the PivotChart Tools section of the menu ribbon (only visible if the chart is selected):

You can then choose which (if any) filters to display so that your chart looks nice & clean:

Hope this helps!

~Yosef B.

Excel: Creating a Drop Down Menu

Ever wondered how to add a drop down menu to your Excel file? Here’s an easy way to add one wherever you want!

To create a drop down menu, you have two options. First, you can create a list of all the items you want to display in your Drop Down List. E.g. “Yes, No, Maybe” or “Early, Late, None”, etc. in a range of cells (one option per cell). Alternatively, simply copy a list of items, ensuring that they are comma delimited.
Once you have your items listed:

1. Select the Data Tab from the Excel Menu Ribbon. In the “Data Tools” section, click on “Data Validation”.
2. Under the Settings Tab, select “List” from the “Allow” drop down menu.



(Click to enlarge)

3. Using the arrow on the right hand side of the “Source” box, select the list of items that you want to display in your Drop Down List.
4. Click “OK”

(Click to enlarge)

Got questions? Leave a comment below!

~Yosef

Excel: Conditional Formatting

Excel provides the ability to automatically apply specified formatting to a cell based upon the value in the cell or by comparing the value of the cell vs. the value in another cell.

Here are instructions to apply basic conditional formatting:

1. Select the first cell which you wish to have automatically highlighted based on a value in another cell.
2. Select the Home Tab on the Ribbon. In the “Styles” section select “Conditional Formatting”. In the drop down menu select “New Rule…”.



3. Select “Use a formula to determine which cells to format”
4. In the “Format values where this formula is true:” box type in a formula that describes the values in the cell the highlighted cell is based on. For example, if you want cell A1 to change to green when cell B1 contains the value “1” type “=if(B1=1,TRUE,FALSE)”. This formula will return “TRUE” if B1 contains a 1, otherwise it will return “FALSE”. Your cell will only change color if the returned value of the function is “TRUE”.
5. You can overlay multiple formatting rules on the same cell – therefore you can have the cell turn one color based on one rule and another color based off another rule. You do need to be careful that the formulas will not both be true at the same time otherwise your conditional formatting will not work.
• Note: In addition to an equality, you can also use any other comparative logic symbol. Examples include: “Does not equal” e.g. =if(A1B1,TRUE,FALSE) or “Is greater than” e.g. =if(A1>B1,TRUE,FALSE).
6. Select the “Format…” button and apply whatever formatting you want to occur when the formula returns “TRUE”.



7. Click “OK”
8. To apply your formatting to multiple cells, select the “Format Painter” tool and copy your formatting to other cells.

Pretty straight forward but if you have questions, as always leave a comment & I’ll get back to you!

~Yosef

HTTPS Protocols and Ciphers

To give a bit of background on this post –

I was researching different Firefox about:config settings for another article (to be forthcoming) and had changed a bunch of security settings to try and force Firefox to use higher grade encryption when accessing encrypted (HTTPS) websites.

Later in the day, I tried to access a website I visit very frequently and found that it wouldn’t load. I eventually realized that it must be because I had changed my Firefox security settings. The question I was stuck with was, “How do I figure out what encryption protocol and cipher a website is using in order to allow that encryption in Firefox?”. After some more research I was left with the following:

What is HTTPS & how does it work?

When you access a website, you are typically communicating over one of two protocols: HyperText Transfer Protocol (HTTP) or HyperText Transfer Protocol Secure (HTTPS). When you connect using HTTPS, all the information sent back and forth between you and the website is encrypted.

There are of course exceptions to this – for example, on your bank’s website, it may have a secure component for logging into the website but it may also have a third party advertisement that is not delivered encrypted to your browser. Depending on your browser, it may alert you when there is “mixed” content being loaded.

At a high level, when you access an HTTPS website, the server has a list of approved protocols and ciphers that it will allow communications to be encrypted with. This list is either the default list that the server comes enabled with (i.e. no one really thought about it, they just turned it on), or it’s a customized list that (hopefully) only allows the latest and greatest encryption methods.

Just like the server, your browser has a list of approved protocols and ciphers that it can use. When your browser contacts the server to load the HTTPS page, the server gives it a list of the approved encryption methods – this list is prioritized by the server’s “preference”.

Assuming your browser allows connections using one of the approved server’s methods, the browser and server conduct a “handshake” where the encryption method and a key are agreed upon and exchanged, allowing both the server & the browser to encrypt and decrypt communications between them. When the “handshake” is conducted, it is the most dangerous part of the communications process because it is done without encryption and (if done improperly) can allow for “man in the middle” (MITM) attacks or other attacks by an outside party. (I hope to cover different types of attacks and how they work in a future article.)

So what’s the difference between a protocol and a cipher? Think of encryption protocols as the method by which information is encapsulated and sent whereas ciphers are the way which information inside the capsule is scrambled (encrypted) to prevent anyone from reading the information.

Determining a Server’s allowed Protocols and Ciphers

This leads us back to my original question – if your browser does not accept the encryption types allowed by the server, how do you figure out which ones you need?

I found that there is a very handy website called http://www.ssllabs.com that has a SSL Server Test tool (available here: SSL Server Test) that allows you to plug in any website and it will determine if there are any available HTTPS connections to that website as well as give you a high level score and a very detailed breakdown of the site’s security protocols and ciphers.

There is also a Firefox plug-in available (called CipherFox) to give you this information every time you visit a website.

Tomorrow I will cover which Protocols and Ciphers are best to use & how to implement them in your Firefox browser.

Got questions or feedback? let me know in the comments below. Thanks!

Excel: Checking for Errors or Duplicates in your Data

A question I am frequently asked is how to quickly identify errors or duplicates in Microsoft Excel data.

I will show you how this can be done very quickly and easily.

First, I assume that you don’t want to simply remove all duplicated data – if you do, you can use the “Remove Duplicates” tool in the “Data Tools” section of the “Data” tab along the Ribbon.

If you want to keep your data, and simply identify where the duplicates are, here are a couple of tricks:

Assume you have the following data:

Excel Example Table

1. Order your data by the column that you wish to identify the duplicates in. (e.g. in the example above, sort by column A “Letter”
2. After you have ordered your data, create a third column with a formula that checks if the cell one line above matches the cell one line below. There are many ways to do this, for example:
3. 1. =A1=A2 – This will return TRUE if the data in cells A1 & A2 match, or it will return FALSE if they don’t
   2. =IF(A1=A2,"Duplicate","Unique") (Same check as above but gives a user friendly message)
   3. Alternatively if duplicated data is an error you could write: =IF(A1=A2,"Error","OK")
   4. =IF(A1=A2,1,0) – This is my preferred method because you can select the entire column and see if the sum of the column is greater than 0. If it is, than you know you have duplicates/errors which you then can quickly sort for and find.

Questions? Other tricks/tips? Not what you were looking for? Let me know below in the comments and I’ll get back to you as soon as I can.

Thanks for reading!

Outlook Quick Steps – Improve your workflow

Quick Steps are little automated actions that you can set up in Outlook to improve your workflow. Examples of Quick Steps include:

• Filing steps (moving, copying, deleting)
• Change message status (read, unread, important)
• Categorize, add to tasks & set flags
• Hotkeys for response actions (reply, forward, etc.)
• Hotkeys for creating new appointments
• Hotkeys for dealing with conversation threads

In addition, you can combine actions.
For example, you can set up a hotkey which sends an e-mail to a specified folder, sets the status to important and starts a new appointment dialog window all with one key.

1. To create a new Quick Step, select the “Create New” button in the “Quick Steps” section on the “Home” tab on the ribbon.



2. Enter a name for this new shortcut in the “Name:” box.
3. Pull down on the “Actions” drop down menu and select (for example) “Move to folder”.
4. In the new pull down menu below, choose which folder you want to move the mail to.
5. Select the “Add Action” button if you want to add a secondary action. Repeat steps 3 and 4 for the new action.
6. Choose a shortcut (hotkey) combination if desired.
7. Click “Finish”.