Blog - Page 2 of 10 - Office PowerUps

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…

21May 2016 by Charlie Nichols No Comments

TEXTJOIN, SWITCH, IFS, MAXIFS, MINIFS, CONCAT Available for Excel 2013, 2010, 2007

Excel

[wp_ad_camp_1] [wp_ad_camp_4] New Functions Available for Excel 2013, 2010, 2007 The following functions are available for Excel 2013, Excel 2010, and Excel 2007 in the Excel PowerUps Premium Suite. CONCAT IFS MAXIFS MINIFS SWITCH TEXTJOIN These functions were introduced in a recent update for Excel 2016 and are now available for Excel 2013, 2010, and 2007 in the Excel PowerUps Premium Suite. In addition to these six functions, the Excel PowerUps Premium Suite also includes 50 functions that were added to Excel 2013. Similarly, these are available for Excel 2010 and Excel 2007. CONCAT The CONCAT function is similar to the CONCATENATE function. In addition to text strings, you are able to select multiple ranges of text to concatenate them. IFS The IFS function is a option to creating a bunch…

26Jan 2016 by Charlie Nichols No Comments

How to pick a random value from a range of cells in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to pick a random number from a range of cells Here's how to pick a random number from a range of cells in Excel. And actually, it doesn't need to be a number. If you have a range that contains data (names, numbers, dates, whatever) and you need to randomly select a value from that range, you can use the pwrRANDFROMRANGE function. It's as easy as selecting the range. See below. Example Let's say you have data contained in a range of cells between A1 and D200. Let's say this range contains a set of names and you want to randomly select one. You would use the following formula in Excel. =pwrRANDFROMRANGE(A1:D200) If your data is contained in a single column. No problem. pwrRANDFROMRANGE works just as well with…

04Nov 2015 by Charlie Nichols No Comments

Concatenate a range of cells in Excel – Easily

200 level, Excel

[wp_ad_camp_1] Concatenate a Range of Cells in Excel Concatenate a range of cells in Excel without having to individually select every cell that you want to concatenate. The pwrCONCATENATE function (part of the Text Analyzer Assistant in the Excel PowerUps Premium Suite) lets you select a range of cells to concatenate. Additionally, you can add a delimiter between the concatenated cells if you wish. Also, you have control over whether to concatenate rows first or columns first. You can have empty cells in the range. They'll just be skipped. For example, you can use the following formula. =pwrCONCATENATE(E3:G30, " ", "COL") This will concatenate the range of cells between cells E3 and G30. Each cell will have a space (" ") character inserted between and the concatenation will go in the…

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…

05Oct 2015 by Charlie Nichols No Comments

How to fix a VLOOKUP error in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] VLOOKUP error in Excel Ever have this happen? You write your VLOOKUP formula and have one of the following values returned: #VALUE! #NAME? #N/A #REF! Other UNEXPECTED result! Below you’ll find some common pitfalls that cause each of the errors above. VLOOKUP function syntax First, here’s a recap of the usage syntax for the VLOOKUP function in Excel. I’ll be referring to the parts in the explanations below. VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup]) For more detail on the arguments, see the function's documentation page. VLOOKUP errors in Excel #VALUE! error causes in VLOOKUP column_index_number is less than 1 In an effort to retrieve a value that is to the left of the lookup column, you may be tempted to put in a value less than one. However, VLOOKUP only finds…

27Sep 2015 by Charlie Nichols No Comments

Excel Financial Functions tutorial

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel as a Financial Tool Excel provides a number of functions to make calculating financial data very easy. You can find a great deal of examples and documentation on each of the functions. For this tutorial, we will use five commonly used Excel financial functions and use them together to answer some common questions. Excel Financial Functions The Excel financial functions that we will use in this tutorial are the following. PMT RATE NPER PV FV Each of these can be use to answer a likely question in the scenario for our tutorial. In this example, we'll consider the purchase of a used car and answer the following questions. What is my payment going to be? If I pay more than the minimum payment, how much earlier would…

21Sep 2015 by Charlie Nichols No Comments

Regular Expressions in Excel – Test Tool

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Regular expressions in Excel Regular expressions in Excel are made easy with the pwrREGEXMATCH function. As a demonstration of the function, the example file pictured below is set up to evaluate a regular expression entered in cell D2 against text entered in cell D4. The result is contained in cell D8. The formula used in Excel (cell D8) is the following: =pwrREGEXMATCH(D4, D2) That's it. Download the example worksheet (link below) or just create one on your own. Using this, you can easily try out different regular expressions against some target text to massage into the perfect regex for your need. [caption id="attachment_3430" align="aligncenter" width="908"] Sample Excel template demonstrating use of regular expressions in Excel thru the pwrREGEXMATCH add-in function[/caption] Download the sample workbook here.