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.

Tuesday, November 28, 2017

What Google can teach us about PowerPoint

Since 1998, Google has been giving us Google Doodles. The first Google Doodle was in honor of the Burning Man Festival.

It was simple and still used the Google text with an image behind the word.

What Google can teach us about PowerPoint is that you don’t need to use text to communicate your message. Yes, many Google Doodles still use the Google Text, but they have been transformed into images that tell a story.

Take the Google Doodle from June 1, 2015 “Children’s Day 2015 (Multiple Countries).”

While the Doodle does show the letters in the word “Google,” they have been transformed to show a children’s playground. Each letter represents a child playing, laughing, and singing.

You know it is Google’s Home Page when you get there, even though you don’t see Google’s true logo. You think about children on a playground. Google Doodle’s are telling a story, much like a PowerPoint Presentation should do. Your mind reads the story that is being told to you in the image.

Most, if not all, of you have either sat through or created a PowerPoint Presentation that you would consider “Death by PowerPoint.” You know, those blank white slides with bulleted text so small that you have a tough time reading it, or can’t read it at all. The presenter just reads the text as if you were a 2 year old.

Google Doodles were initially neither animated nor hyperlinked; Doodles transformed. From January 2010 with the first animated Doodle honoring Sir Isaac Newton,

to the first interactive Doodle appearing shortly thereafter celebrating Pac-Man, 

and then hyperlinks began to be added to Doodles, usually linking to a search results page for the subject of the Doodle.

As simple as it may sound, these are the things Google can teach us about PowerPoint.

As Google Doodles have transformed, so has PowerPoint. PowerPoint now offers features that allow you to make your presentations come alive.

Transitions, Image layering, Animations, Hyperlinks, and other incredible features aren’t there for you to present boring white slides with bullets. These amazing features allow you to engage your audience, to tell your story.  We should make our PowerPoint slides visual, interesting to the eyes, and interactive whenever possible.

Now, don’t think you need to go crazy with using PowerPoint features. Look to Google Doodles for inspiration: Keep slides simple, but tell a story. Use an image for your audience to view as you speak to your topic. Let the audience use all of their senses to hear your story.

You also don’t need to be a graphic designer. There are plenty of free image sites out there. One of my favorite is Pexels.

You can search by keywords to find the image that best represents your story. Simply place the image into the slide and fill the slide with the image. Often times, you don’t need any text at all. Your audience will absorb the image and your story as they view the image and you speak to them. It can also insight a conversation.

Tip: When using images, not only do you want to make sure avoid copyrighted images, but don’t use a goofy clipart image when you are speaking about a serious topic, for example. And, don’t just place the image next to your bulleted text. While sometimes that will work, it still looks boring and it looks like an afterthought.

I bet everyone reading this remembers at least one (1) Google Doodle they’ve seen over the years. And, so it should be when your audience views your PowerPoint Presentation. They should remember something about what you presented. They shouldn’t come out of your presentation thinking they’ve just seen another “Death by PowerPoint” presentation.

The moral of the story – Google can teach us about PowerPoint. It’s how you use what you've learned that will make you better.

Learn from Google Doodles and go make engaging, exciting, story-telling PowerPoint Presentations.

Please Like, Comment, and Share!

Friday, November 17, 2017

PowerPoint Tutorial: Cinemagraphs in 3 Steps

Have you noticed all the rage about Cinemagraphs and GIFs? Or wondered how to make them?

I’m going to show you how to easily create Cinemagraphs using PowerPoint in just 3 simple steps…

Follow the steps below OR watch the video tutorial here:

So what’s the difference between a GIF and a Cinemagraph?

A GIF is simply a series of still images placed together to look like they are moving – or to look like a short video.

See in this GIF here that it looks a little choppy – it’s just images that were put together to form what appears to be a video. And there is movement all around the clip.

A Cinemagraph, on the other hand, is a video that has an isolated portion of the video that keeps the movement. They can draw attention to a specific area of the video to help make a point. This is perfect for PowerPoint presentations when you need to explain or point out something, but don’t want to just use boring arrows or other ways of drawing the viewer’s eyes.

Look at this first video – this is  a boring video from a shopping mall’s camera – so it’s black and white and nothing.

However, after I add the effect, the little girl on the escalator is the only thing moving and it draws your attention to her – and somehow is no longer a boring black and white video or photo.

So, now let me show you how I did this.

First, open PowerPoint and Insert the video you’d like to edit. You should really think of the message you want to send before you select your video… and of course, make sure you have appropriate copyrights to use the video.

This original video shows the entire movement of the water and the sky, including the clouds.
But let’s say we’re talking about cloud formations and would like to isolate the cloud movement.

First, I’ll duplicate the video using Copy and Paste. Depending on the effect, you may want to trim the video to only the portion you want to show.

So, I’ll crop the copy of this video so that it only includes the sky. Basically, I have both videos on the slide, but I’m going to leave one static, meaning it will not play – it will appear as an image. And, the 2nd video, the one I’ve cropped to just include the sky and clouds, will keep its movement or motion.

Next, you’ll fix the animations.

So, I’ll select the first video, the one I don’t want to move and click on the “Playback” Tab. Since this is already setup to Start On Click, I’ll leave it alone.

Next, click on the copy, the one I cropped to just include the sky and clouds, and click on the “Playback” Tab. I’ll select the “Automatic” Start option.

Next, to check that all of my settings are correct, I’ll click on the “Animations” Tab and open the Animation Pane. If the original video shows in the Animation Pane, I like to delete it just to make sure I don’t accidentally click and start it playing while I’m presenting.

So, let’s Play our Slideshow and see how this looks.

Notice that the water and waterfalls remain motionless and the sky and clouds are moving. When playing this slide, you would draw the viewers eyes to the sky and clouds. Cool, right?!

So, that’s how you create a Cinemagraph using PowerPoint.

You can get really creative and make your PowerPoint presentations standout by using this technique. However, as with all PowerPoint Tips and Tricks, limit your presentations – do not have every slide include a Cinemagraph, for example. It will get old and lose its effect on your audience.
Practice this technique with various videos and see what works best.

If you like this tip, please LIKE and COMMENT below and to receive other tips and tricks, be sure to click JOIN NEWSLETTER above (on my menu) and receive my FREE eBook: 65+ Ways to Use Office to be More Productive

Friday, November 3, 2017

Excel Macros Tutorial

For many years I've had students asking how to create Macros in Excel. So I created this Excel Macros Tutorial to show how you can create, setup, and use an Excel Macro in 3 Easy Steps.

If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want.

When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works.

Suppose that every month, you create a report for your manager. You want to sum the sales of the customers' revenue and apply bold formatting. You can create and then run a macro that quickly applies these formatting changes to the cells you select.

Here is the video showing you exactly that example:

As always, please Like, Comment, and Share my videos to keep them coming.

If you'd like tips like these and others, please SUBSCRIBE.


How to Delete Blank Rows in Excel