Saturday, January 20, 2018

How to Delete Blank Rows in Excel

This is the text version of a YouTube Live session of Go Ask Debbie: How to Delete Blank Rows in Excel.

I thought I'd just do a quick tip here to show people something that I've been often in Excel.

That is a lot of times when you start using data in Excel, you want to add formulas.

When you have a formula where there are blank rows, Excel stops at the first blank row, so your formula has to be manipulated a little bit - it's not so easy to calculate formulas.

So really when you're working with data in Excel, you want to get rid of all these blank rows.

They're not necessary. You may think that it looks nicer for formatting, but when you're trying to use Excel functions, really what Excel is for in calculating and manipulating data, you want to get rid of these rows.

So if the blank rows could be highlighted within your data, it's easy with a small list.

But if you have a large spreadsheet, Excel will recognize the blank rows without highlighting your data set.

But I'm going to go ahead and highlight this data set here.

Then you're going to go to your Home Tab,

and then over here on the right, you're going to click on the Find and Select button,

and then this time we're going to select Go to Special... 

a pop-up window will appear 

and you're simply going to select Blanks on the left-hand column and then hit OK.

You'll notice then that Excel highlights, in gray, all of the blank rows.

You'll see the first cell A4 is highlighted in white because that's the active cell.

But we really want to recognize these rows that are highlighted in gray.

These are truly all of our blank rows that we see on the screen here,

and to Delete those all in one swoop, we're just simply going to hit our CTRL key and then the Minus (-) sign.

Excel is going to open up another Dialog Box asking us to verify what we'd like to delete.

We want to delete the Entire Row for all these blank rows and then hit OK.

You'll see all the blank rows have disappeared, and now when I go to use Excel's function for Auto Sum, Excel knows exactly what I'd like to calculate.

There are no blank rows that are stopping the formula from calculating properly.

So again when we do this we're going to

  1. Highlight our data set from our Home tab.
  2. Click on Find and Select
  3. and then Go to Special... 
  4. Click on Blanks
  5. Hit the OK button
  6. and then CTRL - (CTRL and Minus)
  7. and select Entire Row
  8. and OK

It really is that simple.

So now imagine if you've got a spreadsheet with tens of thousands of rows worth of data and you have 500 empty rows.

This makes it a lot faster than manually searching through blank rows and deleting them one at a time.

Thanks for taking this session of Go Ask Debbie with YouTube Live.

If you like what you see today, please Like the YouTube video.

Please feel free also to SUBSCRIBE to my channel to receive tips like this, and more, on a regular basis.

Please COMMENT if you have any other ideas for tutorials that you'd like to see on this channel.

Thanks for taking Go Ask Debbie and remember, be sure to SUBSCRIBE to my channel to continue seeing tips like this.

Thanks and everyone have a great day!

Watch the video here:

Friday, January 12, 2018

How to Calculate Percentages in Excel

Did you get your annual raise yet?

Many people have asked me over the years, "How do I calculate my annual raise when I know what percentage I'm getting?"

First, let's understand percentages. The term "percent" is literally broken down as "per," which means "out of" and "cent," which is one-hundred. So, a percent is the number out of one-hundred.

So, you're probably asking, "Debbie, how does that help me when I was told I was getting a 3% salary increase?"

Well, here's how you would calculate it.

Let's take Jim. Jim was told by his boss that he would be receiving a 3% salary increase on January 15th. If Jim's current salary is $40,000, let's see what his raise would be.

If you'd like to use Excel to do this, simply write the formula as such.

Column A would contain Jim's current salary of $40,000.

Column B would contain Jim's increase percentage of 3%.

Column C would calculate the 3% of $40,000, which would output $1,2000.

Column D would then add the $1,200 increase to Jim's current salary of $40,000, which would show his NEW Salary of $41,200.

Now, this is a bit of the long way around, but if it works for you, go ahead and use it.

For those of you who like quicker ways to calculate increases, here's what you would do. (Example shown in screenshot, Row 5)

Column A would contain Jim's current salary of $40,000.

Column B would contain the formula =A5*1.03 (this means you are saying I want 100% of the current salary, PLUS 3% added). This then gives you the $41,200 NEW Salary figure we came up with the 4 Column process above.

Hopefully, many of you will receive more than 3% with the new U.S. Tax Reform announced recently! But, this is just an example of how you can easily calculate percentages using Excel.

Please LIKE and COMMENT if you found this helpful.

How to Delete Blank Rows in Excel