Posted by: Yosef B. | April 12, 2016

Building Dynamically Driven Excel Hyperlinks


I recently came upon a challenge that at face value looked straight forward but actually proved to be quite tricky.

What I wanted to do was create a drop-down menu in Excel. Depending on the choice selected, a hyperlink would be created that would allow you to “jump” to a specific location in the Excel workbook based upon your selection.

The solution I hit upon is to use the Hyperlink formula with a unique twist.

First, I created two different worksheets – for this example I’ve creatively named them “Test Sheet 1” & “Test Sheet 2”.

Next, I created a drop-down list menu using the two worksheet names (cell C2 in picture below; see my previous post for details: Excel: Creating a Drop Down Menu).

Excel: Drop Down Menu Example

Excel: Drop Down Menu Example

Finally, in cell C4 I put the following formula =HYPERLINK("#'"&C2&"'!A1","Go to "&C2) where the first part "#'"&C2"'!A1" builds the actual link #’Test Sheet 1′!A1 or #’Test Sheet 2′!A1 (depending on the drop down selection) that takes you to the A1 cell on either the Test Sheet 1 or Test Sheet 2 worksheet, and the second half "Go to "&C2 builds the text that’s displayed to the user in cell C4.

Excel: Dynamic Hyperlink Formula

Excel: Dynamic Hyperlink Formula

Viola! Depending on the drop down item selected, the hyperlink will dynamically update and direct the user to the correct worksheet.

The tricky part was in figuring out where to put all the single & double quote marks as well as learning about using the hash mark to indicate that the URL was inside the current workbook without having to specify the full name of the workbook.

This should come in handy when building table of contents, forms, &/or surveys in Excel.

~Yosef

Posted by: Yosef B. | March 15, 2016

Windows 10 Calculator Shortcut


I’m used to pulling up the built-in Microsoft Windows calculator app when I have a quick calculation to do.
I recently discovered that with Windows 10 I don’t even need to open the calculator! I can simply open the start menu (Windows key) and start typing my math problem (including trig functions!) and it will display the answer right there in my start menu!

For all I know this has been available in Windows 8 for a while but I definitely use this tip now that I know about it!

Here’s a screenshot of what I mean – try it out yourself!

Windows 10 Start Calculator

Windows 10 Start Calculator

Posted by: Yosef B. | February 16, 2016

PSA: Use Microsoft Word to create & edit PDFs!


PSA! You no longer need a professional PDF editing program such as Adobe Acrobat to create & edit the majority of PDF files!

It has come to my attention that folks may not know about these features in Microsoft Word. The latest couple of versions of Microsoft Word have built in features to convert and create PDF files.

To open a PDF and convert it to an editable Word document, simply open the PDF file from inside Word.

  1. Open Microsoft Word
  2. Click the Open Other Documents link on the bottom left of the screen
  3. Browse to the folder location of the PDF file and select it
  4. Click the Open button
  5. Word will present you with a message that it will now try to convert the PDF file to an editable Word document. Depending on the size of the file, this may take a while. Select OK

That’s it! Word should create a new Word document that looks identical (or really close) to the PDF document. Depending on the PDF, it may treat text as text or as a picture – it depends on how the PDF was created and if the fonts are embedded in the document or not. In other words, your mileage may vary but in general this works quite well.

Once you’ve modified your Word document, you can save it directly from Word to a PDF document.

  1. Click on the File tab
  2. Select Save As from the menu on the left
  3. Browse to where you want to save the file
  4. Select PDF (*.pdf) from the Save as type drop down
  5. Click the Save button
Posted by: Yosef B. | January 13, 2016

How to Capture your Screen & Audio with PowerPoint 2016


I’ve had the new Office 2016 installed for a few months now but haven’t had much time to really dig into the new features. One that I do want to highlight that I came across recently is a new PowerPoint 2016 feature that allows you to do a screen & audio capture and embed the resulting video directly in a PowerPoint document.

Not only that, simply right-click on the embedded video in PowerPoint & select “Save Media as…” to save the video as an MP4 file which you can reuse or upload anywhere!

The video capture is very basic but works well – options include:

  • Show or hide your mouse while recording
  • Mute or unmute your microphone
  • Select a specific screen area to capture (you must choose an area to begin recording, simply highlight the entire screen to capture everything)

I have not tested capturing game play with this, but I did test it capturing another video that was playing on the screen & I didn’t have any issues doing so.

To access the screen capture, open PowerPoint, open an existing document or create a new one, select Screen Recording in the Media section on the Insert tab along the Ribbon.

I love the fact that I don’t need to have a separate application to do this anymore & that it’s so simple – you don’t need to specify screen resolution, file location, or anything to simply & quickly capture a video. This will make creating tutorials much easier!

Enjoy!
~Yosef


I’ve been experimenting with a new way of synchronizing and archiving documents between devices and wanted to share what I’ve learned thus far in case it’s helpful for anyone.

My issue is this – my main computer is my company laptop that I use during the workday. In the evenings & weekends I have both my home laptop and documents server that I use. However, there are many times during the week when I’m on my company laptop that I need to create a personal document of some sort. I have a personal folder on my company laptop to hold onto these working items – most of them are items which I might need for a week or two but then they really need to be moved from my company laptop to my personal machines – mostly to be archived.

I’ve tried many different synchronizing ideas over time. I’ve tried using an external hard drive & backing up these documents on a weekly basis & then manually trying to move them off the external to my personal machines to archive them. I’ve tried using Google Drive, Dropbox, etc. to keep copies of my stuff. I’ve even tried emailing documents to myself.

All of these attempts have issues including:

  • Manual steps that I need to remember to do
  • They don’t really archive because it leaves documents behind on the company laptop (unless I manually remember to delete)
  • In the case of the cloud services, they require duplication of files in multiple places – not necessarily something I need & they have size limits unless I want to pay for more storage

Therefore – as I initially said – I’m experimenting with a new way of moving & archiving files without the need for any manual intervention.

Here’s the basic setup:

  1. Setup a cloud drive service (Google Drive, Dropbox, etc.) on the computer you are normally working on (e.g. my company laptop) and on the computer you want to archive to.
  2. Create a working document folder in this cloud drive. This is where I put all of my personal documents that I’m working on on my company laptop.
  3. This folder will automatically be synchronized & duplicated on both machines.
  4. Create an archive folder on your archive computer (e.g. my personal server).
  5. Setup a batch script (see below for example) to move any files & folders that are older than 60 days from your cloud drive folder to the archive folder.
  6. Setup a scheduled task to run the batch script on a daily basis (see a previous post for more info on how to do this here).

The end result is I now have an automatically synchronizing folder that keeps my working files on both my company & personal computers and automatically removes old working files from my company computer to an archive folder structure on the personal computer.

The batch script I used is:


robocopy "C:\Google Drive\Working Files" "C:\Google Drive Working File Archive" /move /minage:60 /copyall /s

Breaking it down:

  • robocopy is short for a batch command called Robust File Copy
  • The /move flag moves (and deletes from the source) all files
  • The /minage:60 flag filters files for any that are older than 60 days since their created date
  • The /copyall flag ensures that all file attributes are copied
  • The /s flag copies sub-directories (excluding empty directories)

As always, I’m curious to hear if this helps you or if you have another approach you want to share!

Posted by: Yosef B. | December 7, 2015

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

Visio AutoRecovery Setting

Posted by: Yosef B. | August 3, 2015

Picking an Anti-Virus Software


There are many different Anti-Virus software available for both PC’s & Mac’s (yes, Mac’s get virus’s too…) – the question is, how do you choose one?

Personally, I have had very good success with Avast! Anti-Virus software (www.avast.com/) however I also practice very careful internet browsing so I probably don’t attract the same number of nasty programs as your typical internet browser.

That being said, I recently did some research on Anti-Virus software and came across a great resource, a publication called Virus Bulletin (www.virusbtn.com). In reviewing their test results, I built an interesting graph that I think compares a lot of software in an easy to digest manner (click the below picture for a full size view):

Virus Bulletin Software Comparison - July 2015

Virus Bulletin Software Comparison – July 2015 (click to zoom)

I’ve highlighted in red two different software that I think are noteworthy, ESET & Microsoft Endpoint Protection (aka Windows Defender – per Microsoft’s statement of: “Most of our security software uses the same technology and offers the same level of protection.”).

ESET has been around a very long time and has been reviewed by Virus Bulletin for a total of 90 tests. It was not submitted for review for 4 test periods & failed only 2 tests. This by far, is the most impressive long-term passing streak of any of the software reviewed.

In contrast, Microsoft’s product has not been around for that long (roughly half as many test cycles as ESET). It has also not been submitted for review for almost half of the time it’s been being developed. That being said, of every test it’s been submitted for, it has never failed a test – all in all, pretty impressive as well.

I think that this type of visual depiction is very helpful for a quick comparison – keep in mind though that you should review Virus Bulletin’s methods to ensure that you’re comfortable with their testing strategy.

I hope these resources help – I would love to hear which Anti-Virus software you think is the best & why!

~Yosef

Posted by: Yosef B. | July 8, 2015

Personal Tips: How to keep your information secure


Disclaimer: Even the tips provided below cannot completely protect you. Work to develop your Security Mindset. Always remember, if you’re unsure if something is secure, ask!

In this post, I will highlight how a “Security Thinker” thinks, I’ll cover different avenues of attack including physical, personal, & digital attacks. Finally, I’ll spend a little time on how you can protect your data.

Becoming a “Security Thinker”

Ever picked up your car from the dealer after an oil change? The conversation probably went something like this…

Me: “Hi, I’m here to pick up my car…”
Customer Rep: “What’s your last name?”
Me: “Beck”
Customer Rep: “Got it, I see you in my list. I’ll have them pull your car right out front.”
Me: “Thanks!”

Great customer service? Or a massive breach in validating the real owner of the car?

How about this product?

SmartWater is a water-based, clear solution “paint” which is brushed or sprayed onto property, drying totally invisible. Each bottle contains the owner’s unique forensic formula (PIN) which is logged into a secure database so recovered property can be traced. The paint transfers to a thief’s clothing and skin, providing microscopic forensic evidence to prove the presence of the suspect at the time of the incident.

Let’s think about this for a second. Here’s a fun scenario…

I have a bottle of SmartWater and I’m over at your house. I really like your new TV… when you go out of the room to get me a beer, I brush a little on under the front corner. The next day, I call the cops to report that you stole my TV – and I have proof!

Good security product? Or easy way to legally steal?

Hopefully these stories highlight how a “Security Thinker” should think.

Avenues of Attack

Social Engineering refers to someone using psychological manipulation to get information from someone else. For example, you are working in a call center & someone calls and says “Hello, this is Yosef from IT, I’m working from home today and can’t log in, can you help me?” If you know Yosef, you may recognize that the person on the line isn’t him. Probably, you won’t know Yosef from Adam & if you are not careful, you may provide sensitive information without realizing it. To help protect yourself, here are a few tips:

  • Take off your employee badge or put it in your pocket when you leave the office. Anyone eating lunch next to your table can potentially gain all sorts of valuable information by looking at it such as the company you work for, your name, & even potentially your employee number & your title.
  • Do NOT share matters related to work, such as campaigns, products, services, complaints, or customers with people you don’t know.
  • Do NOT let unknown individuals into your office or a client’s office. Piggybacking is not allowed!
  • Double/triple check requests for confidential information – especially e-mail requests! A follow up phone call is good practice.

Another potential avenue of attack is your mobile devices. Now a days, your phone can potentially give someone access to your credit cards, your bank accounts, your social media accounts, and a variety of other information such as confidential documents, etc. Don’t forget your laptop or data thumb drive either! For all of these devices, follow these tips to be safe:

  • Use a login password.
  • Set password program to wipe your device if your password is improperly entered X number of times. Note – this may not be practical if you live with toddlers who like to press random buttons…
  • Setup a program to remotely wipe your device in case it’s stolen.
  • Encrypt your devices! Good encryption programs include Windows BitLocker, Apple LionVault, & GnuPG
  • Try not to leave mobile devices in your car unattended – and NEVER leave them in plain sight!

With regards to your phone, another avenue of attack is through software:

  • Only download apps that have been downloaded many times before (e.g. 1 Million+)
  • Understand the permissions that an app is requesting – does your flashlight program really need access to the internet?
  • Watch for battery to start draining quicker than normal – this may indicate that an unwanted app is running in the background.
  • Turn off features you don’t need such as: NFC, Android Beam, Bluetooth, picture geo-location tagging, & automatic uploading of pictures to Social Media sites.
  • Here are some Social Media tips for staying safe:

    • Be an adult. Don’t talk to strangers!
    • Don’t post information you don’t want others to know
    • Don’t friend strangers just to collect “friends”
    • If you get a friend request & you think you’re already friends with them, check!
    • Remember that you don’t know for sure who’s really on the other end of a chat

    For any of your computing devices, always make sure that you are keeping up with the latest security updates – this is for both your operating system & any software / apps. This is especially true for any programs that access or interface with the internet!

      • If you are on Windows, security patch updates include fixing vulnerabilities that Anti-Virus programs may not catch! Don’t postpone & if you have updates set to update automatically, shut your device down fully at least once a week to allow updates a chance to fully install.
      • Updating software includes updating any plug-ins – these may not update at the same time as the main piece of software. For example, if you’re running the Mozilla Firefox browser, you need to keep your add-ons & plug-ins up to date yourself.
      • Hopefully everyone already does this but don’t open e-mail attachments from people you don’t know! Always make sure your anti-virus program scans an attachment before you open it. Also, keep in mind that file extensions can be changed! A simple TXT file may actually be an executable file that will damage your computer!
      • Do NOT rely on anti-virus to keep you safe! If you don’t know what your anti-virus is prompting you to do try Googling/Binging the message &/or ask a friend!
      • Passwords: Do NOT share them! Do NOT use the same password for personal & work access. Make your passwords looooooong! Anything under 12 characters can be guessed by a computer program in just a couple of hours. If someone wants to get into your account they probably can but don’t make it any easier for them than you have to.

    Protect your Data!

    Data comes in all types & sizes, it can be your SSN, your phone number, address, contact list, work documents, financial information, etc. Tips for protecting your data include:

          • Log out of websites & IM services when you leave your PC
          • Instead of doing a simple delete of computer files that have sensitive information – use a shredding program (such as Eraser – http://eraser.heidi.ie/)
          • Shred your physical papers, credit cards, CDs, envelopes, receipts, etc. Anything that has sensitive information should be destroyed before putting in the trash.
          • Backup your data! Use hard backups such as making copies on an external hard drive, backup to the cloud (top rated backup program is https://www.code42.com/crashplan/)

    Remember! Just because you’re paranoid… doesn’t mean they aren’t out to get you!😉


Last question first – according to a Reddit poster (a reputable source of news I know…) the name Windows 9 may have been skipped due to lazy developers.

Apparently a lot of 3rd party products (e.g. non-Microsoft) may have checked the Windows version they were running on by looking for “Windows 9” to figure out if they were on Windows 95, Windows 98, Windows 98SE, etc. An easy way around breaking a bunch of old software was to simply skip to Windows 10. Certainly makes the most sense out of anything else I’ve read.:-)

Now – what’s new in Windows 10?

Here are the highlights:

  1. Windows 10 is free to upgrade too for 1 year. After that, it’s a flat, up-front fee to buy the software – no annual fees (like Office 365).
  2. Microsoft’s version of Siri or “Ok Google” is called Cortana and it’s coming to all devices including your laptop. This means built-in dictation as well as interactive search, etc. May be a bit difficult to use in an open-cube environment but otherwise has a lot of potential.
  3. Universal Apps – the pipe dream of many end users is now a reality – developers will be able to build apps (aka “programs”) that run on any device including your phone, tablet, XBox, & PC. This means that regardless of what device you pick up, you theoretically can use the same app everywhere.
  4. Microsoft Edge – with Windows 10, Microsoft gets rid of Internet Explorer. To replace it, they have built a brand new internet browser called Edge which brings Microsoft’s browser into the 21st century. Not a lot of new functionality over other modern browsers (such as Chrome or Firefox), however one major update is the ability to literally draw on a web page and add notations, then share your marked up page with anyone. In addition, Edge comes with handy Cortana integration built-in which means that Cortana is constantly scanning the websites you are on to try to help you. For example you go to a restaurant’s website – Cortana will ask you if you would like to make a reservation. If you say yes, Cortana can initiate the call over Skype right from your browser.
  5. I don’t run in the hard-core gamer circles but for those of you with an XBox One, it can now send live gaming to your Windows 10 PC, allowing you to remotely play XBox One games anywhere provided you have your PC with you.
  6. For those Command Line power users out there (myself included of course!) the good old DOS prompt has gotten a much-needed face-lift. Text will now wrap, the window is fully adjustable to your screen, & you now have the ability to Ctrl+C, Ctrl+V into & out of the console!
  7. No longer limited to Microsoft applications, Windows Notifications are now accessible by 3rd party applications (such as DropBox, Google Drive, etc.) to let you know when events occur.
  8. You can pin the recycle bin to the Start Menu & Taskbar & finally delete it off your desktop!
  9. File Explorer now opens by default to a new “Home” screen that shows you any files & folders you’ve designated as favorites as well as your most frequently used files & folders.
  10. Windows has had the ability to give you multiple “virtual” desktops for quite a while. With Windows 10, you now get two key shortcuts to make switching quicker & easier. Win + Tab brings up an interface showing thumbnails of each of your desktops, allowing you to select one. Ctrl+Win+Right/Left will switch desktops in either direction.
  11. File History has been an on-again, off-again feature of Windows but is now standard in Windows 10. Basically it gives you a built-in time machine for accessing previous versions of files (provided you have it turned on).

All in all, I’m definitely looking forward to upgrading from Windows 8.1!

Posted by: Yosef B. | April 15, 2015

Excel PivotTable Slicer Options Explained


Microsoft has a great tutorial on how to use slicers to filter PivotTable data and I don’t feel that I need to duplicate their tutorial here. You can read all about it here: Microsoft: Use slicers to filter PivotTable data

What the article does not cover is the Slicer settings available by right-clicking on a slicer and selecting that bottom option “Slicer Settings”.

There are 3 main settings that you can set here – I typically only change the last one and leave the rest on their default settings. They are:

  1. Name – this is the name by which you can refer to the slicer in formulas. I typically just let this be the default. (e.g. CUBERANKEDMEMBER("PowerPivot Data",Slicer_Name,ROW()))
  2. Header – this is the name displayed above the slicer. You can choose to turn it on or off and customize the name if needed. Again, I typically just leave the header visible with the default name.
  3. Item Sorting and Filtering – Here’s where you can choose to:
    • order the values ascending or descending
    • Perhaps most important for visual aesthetics (and the value I typically choose to modify most often) you may hide items with no data
    • Choose to visually indicate items with no data (by making them more transparent)
    • Choose to show items, which have no data associated with them, last in the list
    • Continue to show items which have been deleted from the original data source table

Questions? Suggestions? Leave a comment!

Older Posts »

Categories

Follow

Get every new post delivered to your Inbox.

Join 207 other followers

%d bloggers like this: