Blog - Office PowerUps

27Oct 2015 by Charlie Nichols 1 Comment

How to X out a cell in Excel – a visual How-to

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] X out a cell in Excel There are a couple ways to cross out a cell in Excel. My favorite way is shown first, but a more manual option is also shown right after the first. Both methods are outlined below. Method 1 (my favorite way to cross out a cell): Add a Small Tool - Make Excel Better Easily cross out cells with a click You can add a tool into Excel that will make it super easy to cross out cells. Or if a cell is crossed out, uncross it out. You can use the tool to set the line color, set the thickness of the line, and choose between a solid line or a dotted line for the "X". Or, you can right-click the highlighted…

17Dec 2020 by Charlie Nichols No Comments

How to Unhide All Sheets in Excel

100 level, Excel

In Excel out-of-the-box, you can select multiple worksheets and hide them with a single click. However, if you have a workbook with multiple hidden sheets there is no easy way to unhide all of the sheets. By default, you need to unhide each sheet individually. On a workbook with many hidden worksheets that can be a drag. Until now. Unhide All Excel Worksheets With a Single Click With the Office PowerUps (OPU) Workbook Tools for Excel you can unhide all of the hidden sheets in a workbook with a single click. This is super useful when you are sent a workbook with many hidden sheets that you need to look thru. I've had to do this countless times as I'm sure you have too. You get a workbook from somebody…

20Nov 2020 by Charlie Nichols No Comments

How to Calculate a Mortgage Payment with Excel

Excel

[wp_ad_camp_1] [wp_ad_camp_4] Calculating a mortgage payment in Microsoft Excel is really easy to do. This article will walk thru the steps needed to set up the calculation of the monthly loan payment. In the end, you will learn how to calculate a mortgage payment with Excel. Excel has a number of financial functions built in. Namely, a function called PMT() that used to calculate the payment for a loan for a fixed amount of time with a constant or fixed interest rate. Just a Few Steps In the next few steps we'll set up the basic template to calculate a mortgage payment with Excel. Start Microsoft Excel and get to a blank workbook to begin. This example will assume that you started in the very upper left corner of the…

23Sep 2019 by Charlie Nichols No Comments

How to Make Excel Calculate Automatically

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] If your formulas in Excel are no longer automatically calculating, you may have inadvertently turned the capability off. Fortunately, it's super simple to make Excel calculate automatically again. In Excel, go to the Formulas tab on the ribbon. In the Calculation group, click the Calculation Options button. Select Automatic from the choices. And that's all you need to do.

17Sep 2019 by Charlie Nichols No Comments

Excel Functions Missing in Office 2016: IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN

Excel

[wp_ad_camp_1] [wp_ad_camp_4] Where did the IFS function go? (same for MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN) Can't find IFS function in Excel 2016? Does it seem like you were once able to use the IFS function in Excel with your worksheets? Can't find the IFS function in Excel 2016? Do you have a standalone license (a non-Office 365 subscription) of Office? Do your function names now resolve to "_xlfn.IFS"? (same for MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN) In looking at various posts online, one user notes in his version 16.0.6568.2025 the IFS function worked. After an update from Microsoft to version 16.0.6868.2048 the IFS function no longer worked. (Lost new functions like "ifs" & "switch" & "maxifs" after upgrade to 16.0.6868.2048 at today) Other posts such as Excel new functions…

21Jan 2019 by Charlie Nichols 1 Comment

How to Make a PowerApps Multiline Text Input Control Automatically Adjust Its Height

PowerApps

[wp_ad_camp_1] [wp_ad_camp_4] The Problem When you add a multiline text input control to your PowerApp there is no way to make the text input box on your form get bigger or smaller to accommodate more lines of text. Instead of growing or shrinking, the text input control displays a scroll bar but does not get any larger. So how do you make a PowerApps multiline text input contol automatically adjust? Here's How to Make PowerApps Multiline Text Input Control Automatically Adjust As a workaround this approach will make the text input control behave like it has an AutoHeight property. The solution is to trick the form by using a label's AutoHeight property to control the size of the text input control. Follow the steps below. Step 1 Insert a text…

18Jan 2019 by Charlie Nichols No Comments

Quick Email Tip: How you can avoid unintentionally sending a draft of an email

Outlook

[wp_ad_camp_1] [wp_ad_camp_4] This is a very simple tactic I have used to make it way hard to accidentally send an email I am writing to the recipients before I'm actually done with it. Say, you need to reply to a large distribution list on an email thread so it's not practical to remove all the recipients until you are done. Read on for the simple Email tip. The Trick You ust enter some garbage characters (like "jjlkjlkjlkjlkj") into either the To: or CC: field of the email editor. That's it. You don't need to do anything more. The effect is that if you inadvertently hit Send when you wanted to click Save or something else, you will get a notification that one of your email addresses doesn't properly resolve to…

14Nov 2018 by Charlie Nichols No Comments

Get IFBLANK and IFZERO added to Excel

200 level, Excel, Uncategorized

[wp_ad_camp_1] [wp_ad_camp_4] Do you wish you had either IFBLANK or IFZERO available in Excel? Sometimes it is handy to be able to test for a blank cell or a zero value in the same manner you would use the IFERROR function. Instead of doing something like this: =IF(SUM(A1:Z1)=0,"The value is zero",SUM(A1:Z1)) You can now do this: =pwrIFZERO(SUM(A1:Z1), "The value is zero.") As your test value or expression gets more complex, you can see how this simpler formula would be much easier to read, write, and maintain. Similarly, you can test for a blank cell using the pwrIFBLANK function. For example: =pwrIFBLANK(A1, "The cell is blank.") instead of: =IF(ISBLANK(A1), "The cell is blank", A1) Check out the pwrIFZERO and pwrIFBLANK functions in the Excel PowerUps Premium Suite add-in. You'll get the…

11Jul 2018 by Charlie Nichols 1 Comment

How to Pin an Email to the Top of Your Inbox in Outlook

Outlook

[wp_ad_camp_1] [wp_ad_camp_4] Pinning the E-Mail Message Do you have important emails you need to be able to quickly access from your Inbox in Outlook? How to pin an email to the top of my Inbox, you ask? Here is how I effectively "Pin" an e-mail message to the top of my Inbox. There are a few ways to accomplish this. The first two approaches try to make due with the version of Outlook you may have. The last option presented is for a built-in feature. Option 1: Use the Task Pane First, I keep the Task Pane in view. Next, when I want to Pin an e-mail message, I set the Follow Up flag to No Date. That way, those messages are at the top of my Tasks list always.…

12Feb 2017 by Charlie Nichols No Comments

Subscription Pricing Now Available

Excel

Subscription Pricing Now Available for Excel PowerUps Premium Suite Since the Excel PowerUps is an add-in, it needs to be installed on each PC that needs access to the new or enhanced functionality provided by the add-in. Volume purchase discounts were a way to get a break on the overall cost of installing on multiple PCs. Now, several annual subscription options have been made available to help lower the initial cost and better support installation on multiple PCs at a lower cost. Note: This does not affect existing license holders as there is no change to the perpetual licenses and ability to update to newer versions. Solo Artist Are you the only one among your colleagues that needs or uses the Excel PowerUps add-in? The Solo Artist subscription tier is…

08Feb 2017 by Charlie Nichols No Comments

Simulating an Event Happening by Using Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] TRUE AND TRUE Isn't Always TRUE From a Boolean perspective, of course TRUE AND TRUE = TRUE. However, sometimes you may want to model something in a simple manner that may be much more complex then a couple of inputs. For example, imagine you wanted to simulate whether it was going to snow on a given day. Perhaps you have one value that identifies whether it is cloudy or not. In another value you have identified whether it is freezing cold outside. If CLOUDY = TRUE, and FREEZING = TRUE, you still can't say SNOW = TRUE. The final factor may be whether or not there will be any precipitation on that day. Lets say there was a 60% chance of precipitation. The chance of snow would then be…