Posted by: Yosef B. | June 19, 2017

Setting Default PivotTable settings in Excel


Microsoft’s latest update to Excel now allows you to set defaults for how you want your PivotTables formatted. As someone who builds PivotTables all the time & 98% of the time want them setup the same way – this is a huge help!

To access the default options, open Excel and go to: File > Options > Data > & click on the Edit Default Layout button.

If you have an existing PivotTable that’s setup the way you want, simply use the Layout Import option at the top of the window to select the existing PivotTable and import the options all ready set.

The options I have set are:

  • Subtotals: Do Not Show Subtotals
  • Grand Totals: On for Rows and Columns
  • Report Layout: Show in Tabular Form
  • repeat All Item Labels: Checked box

In addition, click the PivotTable Options button and you can optimize all your PivotTables moving forward (huge file size saver!).

Select the Data tab and select/unselect the following options:

  • Save source data with file: Unchecked
  • Enable show details: Checked
  • Refresh data when opening the file: Checked
  • Number of items to retain per field: Select the None option

This will force excel to refresh the PivotTable every time the file is opened but it will also not save the source data (calculations behind the PivotTable) which makes the file size much smaller & less susceptible to file corruption.

Enjoy!
~Yosef

Posted by: Yosef B. | June 13, 2017

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.  

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

    This will add the following icon to your icon tray:
     

     

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

Good luck with your presentations!

Posted by: Yosef B. | May 30, 2017

Taking control of Windows 10 System Updates


A number of colleagues seem to be having trouble with their Windows 10 PC’s restarting on them at inopportune times – here’s how you can take back control…

Notes:

  • The options you see will depend on what settings your company/computer Administrator has set. If you don’t see a corresponding option to the steps below, please follow-up with your administrator to see if they can allow the option for you.
  • Click on any image below to enlarge.
  1. Open the Windows (Start) menu.
  2. Type “Windows Update” and select the “Check for updates” option from the list that appears.
     
  3. In the middle of the window that opens, under the Update settings section are three links to more advanced options:
     

    1. Change active hours
    2. Restart options
    3. Advanced options

    Select the first one: Change active hours
     

    Here you can set the normal hours that you work. Windows won’t restart your computer during these hours without at least warning you first. Feel free to buffer on both sides of your day to give yourself enough time to finish up before Windows decides to restart your computer for you. Click Save to save and exit this window to return to the other options.

    Note: If you don’t leave your computer running during non-work hours, you run the risk of not getting important security updates in a timely manner. To counteract this – make it a practice to manually check for updates on at least a weekly basis.

  4. Back on the Windows Update window, select the next option: “Restart options” to open the following window:
     

    Here you can set a scheduled time for Windows to apply updates. Best use of this is if you know you always leave your machine running over the weekend – schedule the updates to be installed on Friday night while you’re not at work.

    You can also elect to have Windows pop up with a reminder to tell you when it’s going to auto-restart your machine. If you’re getting blindsided by it auto-restarting – here’s the option you’ve been looking for!

  5. Go back to the Windows Update window to select the final option: “Advanced options” to open the following window:
     

    I recommend selecting the checkbox for “Give me updates for other Microsoft products when I update Windows.” This will help ensure other Microsoft products like Office are getting updates regularly as well.

    I do NOT recommend turning on the Pause Updates option. This is a big potential security risk if an important patch is deployed by Microsoft and you’ve paused the updates for a month (e.g. the recent WannaCry Ransomware patch).

  6. At the bottom of this screen is another link to the final set of options: “Choose how updates are delivered”
     

    These options allow you to get updates delivered to you quicker. Essentially – if any other computers on your local network have already downloaded the update you need, your computer will be able to get all or part of the update directly from them instead of having to go out to the web to re-download it. Since your local connection is usually much faster than your internet connection – this can save a bit of time & potentially internet bandwidth in getting updates delivered to your computer.

Hope this helps with your reboot woes – as always, let me know if you have any questions.
~Yosef

Posted by: Yosef B. | August 22, 2016

Understanding Windows Group Policy Changes


Whether you are a Sys Admin or a user, troubleshooting Windows Group Policy on a domain connected client PC can be difficult. Luckily through the use of a few free programs and a Windows built-in tool, you can make sense out of the Group Policies applied to a computer.

To get started, you will need software that allows you to compare two text or html files (I prefer working with HTML as it’s easier to read, but it can be a bit more tricky to understand the file differences). I use the free Notepad++ with its Compare plugin for this but you can also use the Windows 10 (Anniversary edition 🙂 ) Bash diff command, or Winmerge, etc. You can download Notepad++ for free here: https://notepad-plus-plus.org/

In addition, the Sys Internals ProcMon (Process Monitor) program is helpful for identifying which registry settings a Group Policy object modifies. You can download it for free here: https://technet.microsoft.com/en-us/sysinternals/processmonitor.aspx

Finally, you need to get comfortable with the DOS command gpresult.

The basic process is as follows:

  1. Generate a Group Policy report using the gpresult command
  2. Review it to see which policies are applied
  3. Use the ProcMon program to monitor which registry settings are applied when you change a group policy
  4. Open / edit a Group Policy
  5. Rerun the gpresult command to generate a new report (make sure to change the name of your output file so you don’t overwrite it!)
  6. Compare the two reports using your program of choice to see what changed!

Step one: Export a list of all applied group policies on the Domain connected computer. To do so, open a DOS prompt in Administrative mode and type the following command:

gpresult /S Name_of_PC_Goes_Here /H “C:\output_file.html”

Where Name_of_PC_Goes_Here is the name of the PC that you’re trying to generate a Group Policy report for and the output_file is the path and name of the report that you’re trying to generate.

If you prefer a text file instead of HTML, remove the /H flag and just pipe the output into a text file, e.g.:

gpresult /S Name_of_PC_Goes_Here >”C:\output_file.txt”

Step two: Open file in editor/viewer of your choice to see what group policies are already in place.

Step three: Run ProcMon, press Ctrl+L to bring up the Process Monitor Filter, and then add the following filter conditions:

  1. Process Name is mmc.exe then Include
  2. Operations is RegSetValue then Include

Step four: Open the Group Policy editor and make any changes that you’re interested in.

Step five: Switch over to ProcMon and you should see the registry key(s) listed there. Right click on it and select the Jump To… option from the context menu to open up Regedit and take you to the exact key that was modified.

Step six: Rerun the gpresult command (remember to change your output file name so you don’t overwrite your first report!)

Step seven: Use your program of choice (e.g. Notepad++) to compare the two gpresult reports to see what changed.

Hope this helps! If you have questions leave a comment & I’ll get back to you as soon as I can.

Yosef


I’m sharing this bug (maybe Microsoft considers it a feature?) as well as the (partial) solution so others can be aware that this issue occurs and how to avoid it in the first place.

The problem I discovered was that when sharing a copy of a Microsoft Office document (such as a Word, Excel, or PowerPoint file) using the built-in File > Share > Email > Send as attachment option from within the Office program, the body of the email sent sometimes would disappear! The person receiving my email would get a blank email with just a subject and attachment but no explanation!

After researching the issue, I know why it now happens – and for most folks the fix I found should work in all cases for you. Unfortunately, my own case is more complicated because as a consultant, in addition to my own company’s Microsoft Exchange Server email, I typically have a client’s Microsoft Exchange Server email account added to my laptop’s Outlook account.

The reason this issue occurs is because your Microsoft email account file (typically a .OST file extension) is not set as the default data file. Your default is probably set to an archive .PST file, or in my case, my company of employment’s .OST file but not my client’s .OST file.

Hence, when I try to send an attachment from my client’s email – while I can change the email address I’m sending the email from (it defaults to my company of employment’s email address), the body of the message is not delivered when I hit the send button. It only works if I use my company of employment’s default email address.

To check if your email account is set as the default data file, open up Outlook and navigate to File > Account Settings > Account Settings > Data Files (tab). Ensure that the little black circle with a white check mark (default) is set to the correct .OST file for your exchange account. If it’s not, you need to set it to default by selecting the correct file in the list & clicking the “Set as Default” button.

Unfortunately for me – this means it will continue to work so long as I always use my email for my company of employment, if I want to send the email using my client’s email address, I have to manually save the file & attach to the email using other methods.

Hope this helps you if you are experiencing this issue!

Yosef

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!

Older Posts »

Categories

%d bloggers like this: