Excel comes with a handy tool called the “Formula Auditing tool” located on the Ribbon under the “Formulas” section under “Formula Auditing”.

(Click to Enlarge)

(Click to Enlarge)

  1. The Trace Precedents tool shows you visually which cells are used by the selected cell in it’s formula.
  2. The Trace Dependents tool shows you visually which cells are used by the selected cell in it’s formula.
  3. The Evaluate Formula tool walks you through the formula in the selected cell so you can see how it’s calculating the formula. If there is an error, this is a handy way of figuring out where the calculation goes wrong so you can potentially fix it.
(Click to Enlarge)

(Click to Enlarge)

Simple right? If not – feel free to leave a comment below & I’ll try to help!

~Yosef

Posted by: Yosef B. | July 14, 2014

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:

Pivot with Filters

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):

Pivot Chart Filter/Field Buttons

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

Pivot Chart w/o Filters

Hope this helps!

~Yosef B.

Posted by: Yosef B. | July 7, 2014

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.
  3. (Click to enlarge)

    (Click to enlarge)

  4. 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.
  5. Click “OK”
(Click to enlarge)

(Click to enlarge)

Got questions? Leave a comment below!

~Yosef

Posted by: Yosef B. | June 30, 2014

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. Conditional_Formatting1

  4. Select “Use a formula to determine which cells to format”
  5. 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”.
  6. 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).
  7. Select the “Format…” button and apply whatever formatting you want to occur when the formula returns “TRUE”.
  8. Conditional_Formatting2

  9. Click “OK”
  10. 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

Posted by: Yosef B. | June 11, 2014

Writing a Bring Your Own Device (BYOD) Policy


I was recently asked to write a Bring Your Own Device (BYOD) policy for a company. While I have some experience writing policies, I had never done a BYOD policy and it opened up a lot of questions which I had not thought of and I thought I would share them for your consideration.

In this day & age, a company must have a BYOD policy due to the simple fact that virtually all employees have smartphones & have access to company (&/or client) data on their phones (e.g. through Dropbox, Google Drive, or by having data directly on the device, etc.). Your company must have an official position on how to handle this access to data in order to be in compliance with any NDAs, contracts, etc. A major concern your policy should handle is – what happens when an employee leaves your company & takes a device that stores company data with them?

You need to be very clear of who owns what. The employee owns the device but who owns the apps & data? For example – what happens if the employee is terminated and the company remote wipes their device to remove company data, but this also deletes personal data? Is the company liable?

What types of security should a company require & how will you enforce your requirements? Do you require just a password? Do you require whole device encryption? Do you require the company to have the ability to remote access / wipe the device?

Who is responsible for supporting devices? If so, What devices are allowed or included in the policy? What level of support will you provide?

What does an employee have to do to leave with their device? Do they have to prove that they’ve removed data, apps, access?

What do you do if you find illegal information or activities (such as child pornography) on the device? Does it make a difference if it’s in an “area” of the device that’s not work related?

What level of involvement does the legal team need to have? What rights does the company have to audit or look at the device holistically vs. just company data, apps, etc.? Example being if there are multiple email accounts in the same app (work & non-work), what rights does the company have to access just the work email account of the app (if it’s even technically possible)?

If your employees are consultants and work for multiple clients and those client’s email requires different device management controls – how do you reconcile multiple security policies on the same device?

An interesting fact I came across is Juniper Networks released results of a survey (back in 2012) of more than 4,000 mobile-device users and IT professionals & found that many employees circumvent their employers official mobile-device policies, with 41% of all respondents who use their personal devices for work doing so without permission from the company.

In other words, at a bare minimum, you need a policy to cover yourselves legally but it may not be enforceable & you may just have to live with that (which could unfortunately backfire in court as well – if the argument is that you had a non-enforceable policy, the court could invalidate the entire policy).

Tricky thing writing policies…

Got an example of a good or bad BYOD policy? I would love to hear from you!

~Yosef B.

Posted by: Yosef B. | May 28, 2014

What do I do with my old…?


Got old electronics & don’t know what to do with them? Here’s a few ideas to get you started:

Sell
Try selling your electronics online. You can use services such as worthmonkey.com to find the value of your electronics, then post them to Craigslist, Amazon, or eBay.

Recycle
Many different retailers now offer free electronics recycling – some will even pay you (minimally) for your stuff. A lot of original manufacturers (such as Dell, Samsung, Apple, etc.) offer recycling programs as well. Try BestBuy, Office Depot, & Staples and see if they’ll take your unwanted items off your hands.

Donate
Another option, assuming your electronics are still usable is to donate them to a good cause. Both Goodwill, the Salvation Army, & Dell have strong electronics reuse programs which you can donate to.

Reuse
Turn your electronics into a piece of art, a dedicated media center, a planter, a media server, an electronic photo frame, a fish bowl,… the list is endless! Try an internet search for “How to reuse…” and fill in the blank. You’ll be amazed at the number of amazing ideas people have for reusing old junk.

Hand-Me-Down
Another option is to give your old electronics to your kids or nieces and nephews to use. You can also use it as a learning experience and let them take the electronics apart to figure out how they work.

Trade
If your electronics aren’t too old, you may be able to trade them in towards store credit or cash at BestBuy, GameStop, and other fine electronics retailers.

Note: Before throwing out or getting rid of old electronics, make sure you aren’t giving away your private information! Always remove hard drives (I suggest going to town with a sledgehammer on them after removal) before donating. If you’re trying to sell the item, at least make sure that you’ve wiped the hard drive numerous times using a program such as CCleaner or Eraser.

Got a particular use you like for old electronics? Share it below in the comments!

Posted by: Yosef B. | May 7, 2014

Heartbleed’ing – what do I do?


With the Heartbleed bug in the news recently, a lot of folks have asked me for advice on what to do.

First off, here’s a quick description of what Heartbleed is:

Wikipedia states: Heartbleed is a security bug in the open-source OpenSSL cryptography library, which is widely used to implement the Internet’s Transport Layer Security (TLS) protocol. This vulnerability, classified as a buffer over-read, results from a missing bounds check in the handling of the Transport Layer Security (TLS) heartbeat extension, the heartbeat being behind the bug’s name.

In plain(er) English – this means that websites that use a certain version of encryption (aka OpenSSL), are vulnerable to sharing encrypted information with anyone who queries the website in a certain way. A website’s address that starts with https:// (note the “s”) use SSL to encrypt the transaction of data back and forth between the website and your browser.

To find out if a website is affected, you can use a bunch of different tools to check the version of OpenSSL used by a website. The easiest one that I’ve come across is LastPass’s Heartbleed checker available here.

When you plug-in a website it will tell you if the website uses OpenSSL & if so, if it’s safe to use the website or not. Here’s a screenshot of what the checker tells you about yahoo.com:

LastPass Heartbleed Checker

As you can see, LastPass says that Yahoo used to use the problematic OpenSSL but has since fixed it on their website and that it’s now time to change any Yahoo passwords that you may have.

If a website hasn’t updated their OpenSSL to a fixed version, there’s really no point in changing your password – I would recommend minimizing or ideally not using the website until it has been fixed. If possible, contact the website & tell them that you are not using their website until they get it fixed.

Hope this information helps explain what Heartbleed is & what to do about it – stay safe!

~Yosef


In today’s era of computers, the numerical password or PIN is incredibly easy for a computer to guess. That being said, here are some ideas to make the number you choose harder to crack and easier for you to remember.

First off, a computer can guess any numerical PIN less than 11 digits long in about 2 seconds thanks to the power of iterative guessing.
The best way to protect your PIN is to enable a time out on whatever device you have. For example, on your phone or tablet, if someone enters the wrong PIN more than X number of times, it wipes the device’s data, or it makes you wait a minute between each try, etc.

Next, try using the following methods to pick a PIN that’s difficult (for a human) to guess, but that you can remember:

  • Make your PIN as long as you can. This will depend upon your device & your memorization abilities. Assuming you can use all 10 digits (0 – 9), a 4 digit PIN gives you 10^4 or 10,000 possible combinations. 5 digits gives you 100,000, etc. The longer the PIN, the harder to guess.
  • Don’t use a number someone else would know (such as your birthday).
  • Use the keypad on your phone to spell a random word that you will remember. E.g. NAME = 6263).
  • Make a sentence out of numbers. E.g. using the first letters of each word in “This Sentence Is Awesome!” gives a PIN (on a phone keypad) of 8742.
  • “Encrypt” your password by appending another number to it. E.g. if your birthday is July 19th, choose a PIN of 0719 and then tack on another number (such as the last 4 of your phone number) to give you: 07191234
  • Don’t use a common PIN – 10% of PINs equal 1234, another 10% are 0000 and 1111. That means that 20% of the time, I can guess your PIN using those 3 numbers. A number like 2580 looks random but it’s the 22nd most common PIN. Why? Because it’s straight up & down on a phone keypad. Be unique – don’t follow the herd.
  • Use sports players jersey numbers. This doesn’t work for me because I’m not a sports guy, but for those of you who are – just concatenate the jersey numbers of your favorite players together. E.g. Babe Ruth (3) + Lou Gehrig (4) + Yogi Berra (8) could give you a PIN of 030408.
  • Choose a number you like such as your birthday and then subtract or add another number you like. So your birth year might be 1955 and your wedding year might be 1978. The difference is 23. Subtract 23 from your birth year to get 1932. To make it even harder to guess, reverse the numbers to get 2391.
  • For a bank card, use the assigned random PIN and memorize it.

Helpful? Got tips or tricks of your own for remembering PINs? Share in the comments below!

~Yosef


Georgia’s Governor Nathan Deal proclaimed February 25th, 2014 to be “Business Cyber-security Day in Georgia”.

As part of the initiative, the Technology Association of Georgia (TAG) ran a Cyber Attack & Business Continuity Simulation, which I attended. It was an amazing presentation, not only of content but also of coordination.

The simulation was of a fictional company (The Logistics Company, aka TLC) and how it would respond to different attacks on its systems. There were seven attacks carried out during the simulation. There were roughly 30+ people involved with the simulation and the entire event was broadcast to multiple locations around the US as well as to Kuwait. Making it more interesting, the people involved hold the same positions in their real companies as the people they were portraying in the simulation. The people playing the C-suite in the simulation, are really C-suite executives in their respective companies, the lawyers in the company are really corporate lawyers in the real world, etc.

In addition, due to the Governor’s proclamation, the entire event was hosted at Dobbins Air Force Base and included participation from the Georgia Air National Guard, the Cobb County Police/911 department, the GBI, as well as the FBI.

There were five cyber attacks, one physical attack, and one social engineering attack.

In reverse order, the social engineering attack was left until last and was more informative vs. something that the business needed to directly respond to in the simulation. An outside resource recruiter informed the business that she had been approached by a Chinese company to try & poach recruits from TLC’s supply chain division. She was approaching TLC because she had become suspicious after following up with each of the recruits to see how their interviews had gone with the Chinese company and been told that the interviews were all one way with the Chinese company asking all the questions & not following up to actually hire any of the interviewees.

The end result was, the Chinese company was trying to gather intel about TLC for a variety of potentially nefarious reasons. I assume the logical response of TLC would be to warn/train its employees about how to respond to strange recruiting requests. Obviously, TLC cannot mandate that their employees don’t talk to recruiters at other companies, but they do need to be aware that every time they talk to someone outside the company, the information could be used to hurt TLC.

The physical attack proved very interesting to me because it helped me realize the role of police and other emergency response personnel in an emergency. The fictional attack was two ex-employees sabotaged the company’s data center (it turned out to be their backup data center, so it wasn’t as large of a crisis as it could have been). The employees went and destroyed cooling towers as well as backup generators so the data center had to be taken down immediately or risk frying all the equipment.

The immediate business response was to call 911. After which, they realized it was their backup facility and it did not hurt their primary data center. They then quickly made arrangements to back up their main facility to a third-party data center. In real life, the backup would take a while to accomplish but so long as their primary facility didn’t go down, it would not hurt their day-to-day operations.

The problem with the immediate business response was, as soon as the police arrive at a potential crime scene, the business loses all control of the scene until the police allow them back in. Therefore, if the business needs access to computers, data, etc. they may lose access to all of it until the police finish collecting the evidence they need. This can potentially hurt the business even more than the actual damage if they cannot complete in-progress business at the time of the crime.

The cyber attacks ranged widely in nature (I forgot what the fifth one was):

  • An international hacker encrypting the business’s data files & demanding a ransom
  • Disgruntled employees installing Raspberry Pi’s to override PLC (Programmable Logic Controller) instructions to disrupt a sorting & packaging facility
  • Hackers disrupting the shipment routing system and sending critical shipments of organ transplants to random addresses
  • The DoD finding out that routers shipped by TLC included malware which was spying on the data flowing through the networks

All of the cyber attacks were pretty complicated and included potential damage to the business’s brand image in the marketplace (especially when the critical organ transplants were not delivered on time), as well as complications involving contracts and insurance policy questions. The main lesson learned here was to ensure that your legal team is involved in preparing your business continuity planning to ensure that you are not breaking contracts with suppliers, clients, or insurance policies depending on how you respond in an emergency situation. There may be SLAs, legislation (such as HIPAA or other privacy laws), or other contractual agreements which you may be bound to regardless of what has happened to your company.

The three main points I got out of the simulation were:

  1. Having a single page listing all important phone numbers is better than any 8 inch thick binder full of amazing plans, because the binder will get left on the shelf during an actual crises.
  2. The act of planning is almost more important than your response in a situation. The act of planning forces you to organize and think through your response so that you at least do something instead of freezing in an emergency situation.
  3. It’s important to have a good response team put together – but even more important is to have all of them in communication during a crisis. Get the key decision makers in one room, or on one conference call. No one leaves until the crisis is over. If they need information, send out others to collect it. Keep your decision makers in the loop at all times so they can respond as soon as the situation changes.

I’m looking forward to attending next year’s simulation!

Posted by: Yosef B. | February 28, 2014

The Power of Complaints: Parking Security?


I had a fascinating insight into one of my blind-spots with regards to both security and complaining today that I would like to share.

Here are the facts:

  • For the past couple of months I have been working at a new building for one of my clients.
  • The building has many different tenants and it has a huge parking deck. One level of the deck has a gated, paid visitor parking area while employees park for free in different, gated areas.
  • As a visitor, you enter through a different door and the security guards in the lobby ask all guests to sign in.
  • When you sign in, they ask for your name, company you are visiting, time in, & time out.
  • When you enter or leave the parking deck, some or all of the gates are sometimes open, allowing anyone to drive through – usually without paying.
  • Because I was coming to the building on & off for a couple of months, the desk security began to recognized me and told me that since I was a regular visitor, I didn’t have to park in the paid parking area, I could simply pull up to any of the employee gates, press the intercom and tell them: “Employee trying to get in (or out)”.

Those are the facts. How I interpreted these facts (and was the basis for my complaint) was:

Obviously the building security didn’t have a clue or didn’t care that it was:

  • Losing revenue by leaving the gates open
  • Not protecting the vehicles parked in the lot by leaving the gates open
  • Did not have a way to contact any visitor to the building in the case of an emergency (because they didn’t ask for contact info)
  • Had no way to know if a real employee was trying to use the employee parking area!

Based on my view, my conclusion was, the security was inadequate for protecting property or people and therefore was stupid.

As I said, this was my complaint and helping me validate it was, everything making up my complaint was true!

However, I realized that I was being inauthentic because this was only my view and I didn’t have a clue what the view of the people administering the “security” to the building was. Today, I walked up to the lobby security and told them that I was very confused by what the parking and building security appeared to be from my view and I asked them why they did what they did.

This is what their view turned out to be. They are only interested in ensuring that visitors have places to park and that visitors pay for their parking. Therefore they:

  • Leave the gates open at random intervals because the visitor lot is utilized by restaurant across the street for random events & those visitors do not have to pay for parking.
  • If you come in through the visitor’s door, they ask you to sign in so that they can check your name against the list of employees in the building to ensure that you’re not an employee using up a visitor spot.

As you can see – their security goals are entirely different from what I thought they were trying to accomplish! They are actually trying to protect their visitor spots from building employees! Not the building employees or their vehicles from any external harm!

What I discovered from all of this is that my view, while always true (according to my view), is by definition, not the same view as someone else.
I also discovered that security goals are not always going to be obvious or make sense from the outside and that you need to really ensure that you understand the driving force behind the security measures before attempting to critique them.

Edit: Finally, & most importantly, I discovered that there is always something missing which is causing my complaints and that by looking at them and by figuring out what is missing, I can make my complaints disappear. Finally, I discovered that my complaint was not the truth. Having the complaint in the first place was inauthentic of me because I should not have a complaint about something I obviously didn’t understand, and that by taking action to explore what was possible (such as asking another for their view) made my complaint disappear.

Once I understood that my complaint was based on what I felt should be, versus what actually was, my complaint disappeared!

I enjoyed sharing these awesome discoveries with you & I would love to hear your feedback!

~Yosef B.

Older Posts »

Categories

Follow

Get every new post delivered to your Inbox.

Join 150 other followers

%d bloggers like this: