excel tutorials Archives

A Paste Special Trick To Convert A Number To A Percentage.

March 31, 2021 by Barbara

Hello, Excellers welcome back to another #Exceltips blog post in my 2021 series. Today let’s look at using the Paste Special Function in Excel. Have you a worksheet with the % (percentage) formatting missing?. If you ever have tried to correct this then you know you could try to adjust the formatting of the cell. But, that just ends up looking like the screenshot below right?.

Who wants to edit each cell with %?. Well, by using Paste Special we can easily adjust the values to correct them with the simple steps below.

Type 100 in any cell on your worksheet (this will be used with Paste Special).
Copy that cell by hitting Ctr+C.
Next, select the cells that should be formatted as a percentage.
Hit ALT+E S (alternatively right-click and select Paste Special).
Select Divide from the Paste Special menu.
Hit ok, then correct the cell formatting to show %.

That’s it!. So, how about reversing this process?. You can remove % signs by multiplying cell values with 100. Oh Yeah! have you ever used this before?.

Want to read more about Paste Special in Excel?. I have more blog posts you maybe interested in.

Supercharge The Copy & Paste In Excel.

Convert Positive Numbers To Negative Numbers Using Paste Special Trick

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips. Click the link below to sign up.

Quick Way To Remove Blanks -Excel Data Tidy Up.

March 17, 2021 by Barbara

Hello, Excellers. Welcome to another Excel Tips post in my 2021 series. In this blog post, I will share with you how to quickly remove blanks or spaces from your data set. This super quick method in Excel is great for data tidy up. The Paste Special function is used.

So, let’s get some data tidy up done. Here is a sample data set to use. Some blanks still remain from a previous data tidy up step. These probably have been left from a previous step in the process of data tidy up.

It is really straightforward to remove those blanks with the steps below:

Select the range of cells you want to remove the blanks from. In my example it is C3:G6.
Home Tab | Editing Group | Paste Special (or shortcut ALT+E S).
Select Blank and hit Ok. All of the blank cells will be selected.
Right Click on any of the blank cells and hit Delete
Finally, in The Delete Dialog Box confirm you want to Shift cells left.

So, hit Ok the blanks will be gone. Your data are aligned and tidy. This is a super quick way to help your data tidy up.

So, paste special is an underrated function in Excel. (In my opinion of course!). To learn more about this special function feel free to read my blog post below. I walk you through sixteen. Yes, that is correct sixteen uses of Paste Special. Including of course remove blanks.

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook.

“Excel Tip- How Insert A Blank Column After Every Data Column In Your Excel Sheet

How To Find And Replace Exactly

Remove Duplicates With Power Query.

October 1, 2020 by Barbara

Hello Excellers. Today I will share with you a great Excel tip. How to remove really quickly duplicate data records using Power Query.

Power Query (previously called Get & Transform) in Excel 2016 is a really easy way to manage your data, and removing duplicate records is one of those easy tasks.

See a sample set of Excel data in the screen grab below.

Let’s take a look at how easy it is to use Power Query to resolve the duplicate rows of data by following the steps below.

Step 1. Turn Your Data Into An Excel Table.
Select your data and use the shortcut Ctrl+T or Insert Tab | Tables Group | Table to turn your data into an Excel Table. (my data above is already in table format).

Step 2. Import Your Data Into Power Query.
Next, in the Data Tab | Get and Transform Group select From Table/Range.

Step 3. Working In Power Query.
The Power Query editor will automatically open. On the Home Tab | Reduce Rows Group select the Remove Duplicates option.

remove duplicate records with power power query

Step 4. Loading The Cleansed Data Set.
The final step is to load the cleansed data into Excel. Once the duplicates have been removed, you can hit the Close & Load option. This loads your new data set into a new Excel sheet. Job Done!.

You now have a new cleansed data set. Easy as that. The duplicates have been removed.

How To Use The Quick Analysis Tool In Excel.

September 29, 2020 by Barbara

Excel is a powerful tool for data analysis and presentation. With its built-in formulas and functions, you can create complex reports and graphs with just a few clicks. However, even the most experienced Excel users can find themselves overwhelmed by all the options available to them. In this blog post, we’ll show you how to use the Quick Analysis Tool to make data analysis easier than ever. So, from Excel 2013 onwards, the Quick Analysis Tool makes it really easy to analyse your data quickly and easily. It contains a variety of Excel tools. The tool can be used with data from a range of cells or an Excel data table.

How To Access The Quick Analysis Tool.

So, it is really easy to get started with the Quick Analysis tool. Once you select a range of cells or an Excel data table, the tool appears at the bottom right of your data set. A range of analysis tools is now available to choose from.

Quick Analysis feature in Excel shortcut

Use The Quick Analysis Shortcut.

If you are an Excel shorty or shortcut person then of course there is a shortcut for the Quick Analysis tool. Simply hit Ctrl+Q. The result is the same. All your analysis options.

What Does The Quick Analysis Feature Do?.

If you want to do some really quick analysis of your data set then this feature this a great place to start. Just select the data range and the Quick Analysis feature will appear.

The options that are available will appear. These include

Conditional Formatting
Charts
Totals
Tables
Spark Lines

So, these are some very handy quick tools at your fingertips. The options menu can be seen in the screen grab below.

So, using my sample data set let’s run through some zippy quick analysis.

Conditional Formatting.

First of all, let’s run through some Conditional Formatting that is available. Conditional formatting will allow you to highlight your data by adding in Data Bars, Colours and Icons. A great tool for quick visualisation.

Of course, if you want to use the full Conditional Formatting set of tools in Excel then just navigate to Home Tab | Style Group | Conditional Formatting.

Charts.

Next, there are a number of charts that can be created with a click or two. If you select Chart in the Quick Analysis Tool then the recommended charts for the data will be displayed. Simply place the mouse the options. You will see the previews. You can then select the option you want by clicking it.

Just like with the Conditional Formatting, if you want full access to all of the Charting options, you can navigate to the Insert Tab | Charting Group.

Totals.

How about doing some quick calculations?. That would be very useful, wouldn’t it? The Total option gives you really quick access to all of the great basics of Excel calculations for your data set. Play around with it. It is super useful.

Tables.

Excel Tables help you to filter, sort and summarize your data. In the Quick Analysis Tool, there are both Pivot Table and Table options available. Under Tables, you can have a preview of what the data is like in an Excel Table. But, in the Pivot Tables, no preview is available as once it is clicked, an empty Pivot Table is loaded. This is ready to add data.

Spark Lines.

Finally, let’s take a look at the quick sparkline analysis that is available. This time I have changed the sample around to make more sense for a sparkline chart. I have created a monthly sales trend which we can visualize with the chart.

The Quick Analysis Tool feature is very useful. For a quick look at data trends and visualisations of your Excel data within seconds it is superb.

Turning Off The Quick Analysis Feature.

If you do not want the option of the Quick Analysis feature you can disable it. Follow the simple steps below:

File | Options
Select the General Tab
In the User Interface Options UN-TICK the Show Quick Analysis options on selection

Turn off the Quick Analysis Feature in Excel.

Remember if you want to use the feature again, you can easily go right back to your Excel shortcut of Ctrl+Q

How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel

October 13, 2015 by Barbara

Most Excel users would be used to working with the concept of displaying negative numbers in a worksheet with a preceding – sign in front of the number, something a bit like this below, where I have an example of monthly sales and the diference month on month of those sales figures. The negative differences are easy to see.

So, how about making the positive numbers just as identifiable with a + sign in front of the numbers. It is really easy in Excel to create these formats using the custom number formatting feature in Excel.

There are varying monthly performances; the negatives are obvious so let’s go ahead and make the positive ones just as obvious.

Word of note….. If we apply only the positive formatting to the data range in the above example we will wipe the original formatting of the negative values, (not exactly what we want), we therefore need to also take into account negative values and ensure we re- apply formatting for them as well. This is just as easy to do at the same time as applying the postive conditional formatting.

Select the range of cells you want to format.
Home tab
Number Group
More number formats
Custom

Or hit CTRL+1 to open the format cells dialog box

Select Custom
Type +0;-0;0
Hit Ok

Here is the same set of data with the new formatting- what do you think?

Both the positive and the negative are appopriately formatted (+0;-0)along with the blank cell reference having no formatting at all. This is the last ;0 part of the formatting instructions to Excel.

Have you ever used this type of formatting?. Do you think it makes a difference to the the impact of the data at all?