Excel Archives - Office PowerUps

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…

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…

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…

15Sep 2016 by Charlie Nichols 2 Comments

How to Create a Burn Down Chart in Excel (Excel Burndown Chart)

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] What’s an Excel Burndown Chart For? In agile or iterative development methodologies such as Scrum an Excel burndown chart is an excellent way to illustrate the progress (or lack of) towards completing all of the tasks or backlog items that are in scope for the current iteration or sprint. Excel can be an effective tool to track these iterations, or sprints, as well as report on the progress using a burndown chart in Excel.We’ll break the task of creating an Excel burndown chart into four main groups. Set up the sprint’s information.Set up the work backlog.Set up the burn down table.Create the chart. Setting up the Sprint Information for Your Excel Burndown Chart The key thing the Burn Down chart will show is a plot of the amount of…

03Jun 2016 by Charlie Nichols No Comments

IFS Function in Excel 2016, 2013, 2010 and 2007

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] You can add the IFS Function to Excel 2016, 2013, 2010, and 2007 Excel 365 or Excel 2019 introduced a new function called IFS. You can add an IFS function in Excel 2016, 2013 or your copy of Excel 2010, or 2007 with the Excel PowerUps add-in. This IFS function in Excel 2016 (or earlier) allows you to specify a series of conditions easily in a single function without having to nest several IF functions. For example: =IF([test], IF([test2], IF([test3], IF([test4],[value4_test4_true],[value4_test4_false]), [value3_test3_false]), [value2_test2_false]), [value_test_false]) Each condition of the IFS function is followed by the value to be returned if the condition is true. The value returned will be for the first condition that is true. You can also specify a default value to be returned if none of the…

01Jun 2016 by Charlie Nichols 1 Comment

SWITCH Function for Excel 2013, 2010, and 2007

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Now Available: SWITCH Function for Excel 2013, 2010, and 2007 Excel 2016 introduced a new function called SWITCH. You can add a SWITCH function to your copy of Excel 2013, 2010, or 2007 with the Excel PowerUps add-in. The SWITCH function allows you to specify an expression and a series of values and results. Each of the values are compared to the expression and the first one to match has its corresponding result returned. You can also specify a default value to be returned if none of the values matches the expression. If you have ever have to fight keeping track of nested IF statements, you'll definitely appreciate the simplicity of the SWITCH function. Example of the SWITCH function This is an example of the SWITCH function in Excel…