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

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

How to fix: Microsoft Outlook 2013 Bug: Body of email disappears when sending attachment!

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

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

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

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

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

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

What’s new in Windows 10? (And whatever happened to Windows 9?)

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!

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!

Project Date Calculations in Excel

A relatively common demand management question I’ve been asked to solve in Excel numerous times is how do I figure out how much time is a person allocated to a project during a certain month?

The answer is relatively simple but requires a visual aid to help figure out the logic of the different scenarios:

As you can see – there are 4 cases that can occur. Our goal is to write an IF statement that models all of these scenarios.

Ta Da! Here you go:

=IF(AND([Project Start]<[Month Start],[Project End]>[Month End]),1,
IF(AND([Project Start]<[Month Start],[Project End]<=[Month End],[Project End]>=[Month Start]),(NETWORKDAYS([Month Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]>[Month End]),(NETWORKDAYS([Project Start],[Month End])/NETWORKDAYS([Month Start],[Month End])),
IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]<=[Month End]),(NETWORKDAYS([Project Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),0))))

Let's break this down line by line.

=IF(AND([Project Start]<[Month Start],[Project End]>[Month End]),1,

translated is case #1 and it would give us a full 100% allocation during that month.

IF(AND([Project Start]<[Month Start],[Project End]<=[Month End],[Project End]>=[Month Start]),(NETWORKDAYS([Month Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),

translated is case #2 (Project End is either before or equal to Month End), we then find the number of work days between month start & project end and then divide by the number of work days in the month to find the percentage of allocated time.

IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]>[Month End]),(NETWORKDAYS([Project Start],[Month End])/NETWORKDAYS([Month Start],[Month End])),

translated is case #4 (Project Start is after Month Start and before Month End, find the number of work days between Project Start and Month End divided by the total number of work days in the month.)

IF(AND([Project Start]>=[Month Start],[Project Start]<[Month End],[Project End]<=[Month End]),(NETWORKDAYS([Project Start],[Project End])/NETWORKDAYS([Month Start],[Month End])),0))))

translated is case #3 (Project Start is after Month Start and before Month End and Project End is before Month End, find the number of work days between Project Start and Project End divided by the total number of work days in the month.)

Using this formula, you can find (per month, or any other given date range such as an entire year) what percentage of time the project takes up within that date range. This percentage can then be used to calculate number of hours, cost of project, etc.

If you have a specific use case you are interested in getting help with let me know!

~Yosef

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