100 level 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…

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.

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…

24Jun 2015 by Charlie Nichols No Comments

How to drag and drop columns in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] This is a quick little tip that makes it really easy to move columns of data around in Excel. It's basically a drag and drop operation - basically. In the example below, we'll just move the data in column C over to column B. When done, the column headings will be in order: First Column; Second Column; Third Column. [caption id="attachment_3350" align="alignnone" width="588"] Sample worksheet[/caption] First, select the entire column. To select a column, click the column heading (one of the letters or double-letters). Don't select a cell and try and highlight everything - just click the column heading. In this example, click the "C" so that we can move column C. [caption id="attachment_3351" align="alignnone" width="586"] Select the column to move[/caption] Next, hover your cursor over one of…

08Dec 2014 by Charlie Nichols No Comments

How to Hide Gridlines in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] I've got this question several times. How do you hide the gridlines in Excel? It's a really quick task as you can see below. For each of Excel 2013, Excel 2010, and Excel 2007 you can click the View tab in the Ribbon. In the Show group, uncheck the Gridlines checkbox. Now you know how to hide gridlines in Excel. To show gridlines in Excel, just check the Gridlines box. There you go.

24Mar 2014 by Charlie Nichols No Comments

Excel add-in update: version 1.6.3 of Excel PowerUps is Available – Get the update today

100 level, Excel

A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new and enhanced functions along with some bug fixes and tool additions. Go to the download page. New Functions SKEW.P SKEW.P was added to match the Excel 2013 function of the same name. SHEET SHEET was added to match the Excel 2013 function of the same name. SHEETS SHEETS was added to match the Excel 2013 function of the same name. pwrISREGEXMATCH pwrISREGEXMATCH will identify a regular expression match within a string. pwrREGEXREPLACE pwrREGEXREPLACE will replace text identified by a regular expression with text provided in the function call. Enhanced Functions pwrISBROKENURL pwrISBROKENURL added identification of bad URLs by Title or URL pattern. Now you can…

15Feb 2014 by Charlie Nichols No Comments

Creating a Constant in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Occasionally, it's useful to have a constant defined that you can simply refer to instead of having to remember the value. For example, the value of Pi is commonly remembered as 3.14. Or, you may need to have a value that may change but you don't want to update formulas throughout your workbook whenever the value changes. One way to go about this is by creating a constant in Excel. Constants have the properties desired as well as showing up in the autocomplete list that appears when you enter formulas. Steps for Creating a Constant in Excel Creating a constant in Excel is pretty simple. The steps are outlined below. First, go to the Formulas tab in Excel and click Define Name on the Define Name menu in…

14Feb 2014 by Charlie Nichols No Comments

Excel Order of Operations is the Same as Math

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel follows the same order of operations that you learned back in your math classes. If you don't pay attention to these rules when you create your formulas, you may wind up struggling to figure out why your results make no sense. Excel Order of Operations The following ordering dictates in which order your formulas will get evaluated. Although our eyes may scan from left to right, Excel doesn't look at formulas that way. ParenthesesExponentsMultiplication and DivisionAddition and Subtraction Excel Order of Operations Example To illustrate how the order of operations matters, consider the example below. 1+2*4 If you were to try and evaluate this from left to right as it is written, you would first get 1+2=3 Then, you would multiply that 3 times 4, giving you…

11Dec 2013 by Charlie Nichols 1 Comment

Excel Formulas Showing Up as Text

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Help! My Worksheet's Excel formulas showing up as text! If you suddenly have Excel formulas showing up as text in your Excel worksheet instead of the results of the formulas, there are a couple of common causes. If you fall into one of these buckets it's a quick fix to get back to normal. Situation 1: You have formula viewing toggled on The easiest thing to try is to toggle the formula view off. You can do this by pressing the CTRL key at the same time as the "`" key. That's the backwards single quote key (in the upper left corner of my keyboard along with the tilde ("~") character. CTRL ` Pressing CTRL+` repeatedly will toggle the formula viewing on/off. Situation 2: You have cells formatted…

26Aug 2013 by Charlie Nichols No Comments

Date Values in Excel Explained

100 level, Excel

Date values in Excel are numbers In Excel, a date is the number of days since January 1, 1900 starting with January 1, 1900 being “1”. Each date after that, Excel adds one more number to that sequence. So August 26, 2013 is 41512, or 41,512 days since January 1, 1900. The integer part of the number is used for the days. The decimal part of the number is the fractional part of the day -- or the time. So .5 would be 50% of the way thru the day, or 12:00 noon. That makes 41,512.5 to be equivalent to 12:00 noon on August 26, 2013. The date number pattern is a follows. DDDDD.TTTTTT So as you might imagine, adding 3 to the date value of August 30, 2013 results…