Nick's Excel Tip Of The Day - Part 1

Nick's picture
Nick's picture

40. Excel Tip Of The Day - Remove Dupes With Multiple Columns

This is a follow-on from yesterday's tip and relates to XL 2003

Suppose you have the following data and you want to remove the dupes (i.e. where both columns are the same):
Remove Duplicates Sorted List

Add a another column containing the concatenation of the previous 2 columns like so:
Remove Duplicates Sorted List
Copy it down, and proceed as with yesterday's tip

I need to extract repititive data from multiple columns

Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.

Vishesh's picture

do u want this using macro or

do u want this using macro or this is one time activity that you want the unique values.

I need to extract repititive data from multiple columns

Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.

I need to extract repititive data from multiple columns

Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.

Nick's picture

39. Excel Tip Of The Day - Remove Duplicates from a sorted list

For XL 2003, there are many ways to find and remove duplicates from a sorted column of data, but this is my favourite as it doesn't involve leaving formulae on the sheet.

With a bit of practice you can become very quick at it.

Suppose you have the following data:
Remove Duplicates Sorted List
and you want to end up with the duplicates removed:
Remove Duplicates Sorted List

Step1:
Enter the following formula in the cell adjacent to the 1st data cell:
Remove Duplicates Sorted List
This is basically asking if the cell is the same as the one below. If it is, then it's a duplicate.

Step2:
Copy the formula down
Remove Duplicates Sorted List

Step3:
Select the 1st heading (B2), and press in sequence:
ALT then d then f then f.. this turns on the data filter.
Now filter for TRUE (click on the arrow at cell C2, and select TRUE)- this selects only the duplicates.
Remove Duplicates Sorted List

Step4:
Select the entire row for data that has been filtered (excluding the heading).. and right click=> Delete.. to delete.
Remove Duplicates Sorted List

Step5:
Turn off the data filter by using the shortcut: ALT then d then f then f, and clear the formulae that you added... There you have it:
Remove Duplicates Sorted List

Video Training on how to Remove Duplicates:

Use AdvancedFilter too

As my comment on previous article, you can use AdvancedFilter directly on multi column data too. It will filter out unique records with ease. :)

Nick's picture

Advanced Filter

Advanced Filter doesn't actually get rid of the duplicates, it only hides rows.
That's why I use the method as described.

Use AdvancedFilter

Frankly speaking, the method here is neither fast or convinient.

Just select the column, use AdvancedFilter, fill nothing in the Criteria, and click "Unique records only".

You can choose to copy the result to another place or filter in place.

Nick's picture

Removing Duplicates

I just timed it - 15 seconds.

Still prefer this to Advanced filter...

; - >

Nick's picture

38. Excel Tip Of The Day - Copy Down

Today's tip is about saving you precious time.

Suppose you have the following data, and you're trying to fill all cells with the correct formula:
copy down

In this example we have numbers 1 to 13, and we want the other 2 columns to be populated with the squares and cubes of the numbers. We have entered formulae in the first row, and want a quick way to populate the rest of the formulae without typing them in one by one.

Step1:
With the setup as above, double click on the square at the bottom right of the cells selected
HERE:
copy down

This will populate all relevant cells with the formulae!

It's as easy as that.

copy down

Download Practise sheet

Video Training - Copy Down

Nick's picture

37. Worried about security ? - open Excel in SAFE MODE


There is a way to open Excel safely which prevents macros from running. - Excel sheet calculations will still run.


Here's how to do it:


Step1: If downloading a file from the internet, click on a link to download the file - save it... if not, skip to step 2.


Step2: Hold down the CTRL key, then open Excel. You will see the following messagebox:


open Excel safely


Click YES You'll know if you have done it right as Excel will have the words: "Safe Mode" at the top. open Excel safely


Now open the file and use it safely. Training Video on How to Open Excel Safely:


Nick's picture

34. Introduction to Pivot tables

Pivot tables are a truly amazing invention. They allow you to summarise, slice and dice data in many different ways without the need to write fiddly formulae.

Let's take the simplest of examples:
Data
In this data, you will see that you have several entries for the various items that you spend your hard earned money on.

Lets say you want a summary of that.

Step1:
Select the data (that's Range B2 to C9) and go:
Insert => Pivot Table (XL 2007), then click OK
You'll now get a new worksheet:
Data

Step2:
On the right, click on both "Expense" and "Cost", and there you have it, a summary of the data !
Data

Training Video - Introduction to Pivot tables

Nick's picture

33. Moving Averages

This example shows you how to calculate moving averages.
They can be useful when you have a share price for example that changes every day, and you want to calculate the average of the last X days.

Here's the data we'll work with:
Data

In cell F3, we enter the number of days we want to calculate the average for.

Cell F4 contains the formula:
=AVERAGE(OFFSET(C4,COUNT(C:C)-F3,0,F3))

Let's break it down to see what it's doing.

First of all, the OFFSET function returns a range.
- How do we make sure this range is the right one ?
- We want this range to be the last 3 populated cells in the table.

OFFSET takes the following arguments:
reference,rows,cols,height,width

So, we tell the OFFSET function to create a new range with the starting cell being 10 cells below C4 (the first share price), and continuing for 3 cells down.

How does it know to start 10 cells down ?
- we enter COUNT(C:C)-F3 as the reference
COUNT(C:C) returns the amount of populated cells in the column C.. in this case 13. Subtract 3 cos we want the last 3.

... then we wrap this with the AVERAGE function.

Download Example Sheet to play with

Training Video on Moving Averages:

Calculate moving SUM

Hey,
I have a worksheet with complaints and rejected quantity that are logged in each day. So, is there any way that I can calculate the sum of the quantities that were rejected for a particular day/date?

use the sumif formula

use the sumif formula

Moving Sum for a range of dates

With Sumif I am able to get the sum of rejects for a particular date by keeping that date in the IF condition, but I want to sum the rejects for a particular range of dates which is from every Monday thru Sunday, so I want the formula to show total rejects so far on any given week, like:
On Monday, i want it from Mon to Mon
On Tuesday, Mon to tues
On Wednesday, Mon to Wed... and so on for the current calendar week.

Appreciate it!!

Nick's picture

start a new forum question,

start a new forum question, then you can add your file with an example of the data you have and what you want
http://excelexperts.com/forum/25

you're the man

Nick,

Just wanted to say a quick thanks for giving a thorough breakdown and explantion of how this formula works. I've spent a few hours on the internet today looking for this info and I was left unfullfilled until I got here.

Thanks!

Rory

Nick's picture

32. Using Go To

This is a follow-on from Tip 31

So, you've entered all your range names and now you would like to go to one of them and you can't remember where it is.

Step1:
Press CTRL + g
This brings up the Go To menu:
Data
- note all the range names are listed and you can select any of them to go to that range.
- you can either click on a name or type in a range reference like B2:C30

Step2:
There is also another very useful menu available if you choose "Special"
Data

Video Training on how to use GO TO:

Special - how do you use it?

ctrl+g to 'goto' a place in the document is a very useful tip.

'Special and then say 'comments' seems to take me to the first cell with comments. How do I go to the next?

How else do you find 'Special' useful?

BTW, embedded screen shots make the tips very easy to follow.

Nick's picture

GO TO Special

you can tab through the cells selected...
Shift Tab from the first one to go to the last.

Special can be useful in lots of ways when you know that tab takes you to the next one..

Tab - doh!

Thanks. I obviously thought I tried 'everything' :)

Nick's picture

31. Create Range names quickly

This is a MASSIVE time saver for a developer, and avoids typos.

Suppose you have the following data on your sheet, and you want to add range names to the input fields. i.e. you want the cell you enter 100,000 into to be named "House_Price"
Create Range names

Step1:
Select the range (B2:C20 in this case)
and go: ALT then i then n then c in sequence
That brings up this:
Create Range names
- by selecting "Left Column", it means that range names will be created using the data in the left column.
- Click Ok

Step2:
Now look at the range names that have been created by clicking on the dropdown shown:
Create Range names

NOTE: Excel does not allow spaces or other special characters in range names, so spaces are replaced by "_" and % is ignored. Would be nice to get better treatment of these characters in future versions.

Training Video - Create Range Names

Nick's picture

30. View and Edit range names - well done XL 2007

Here's something that has improved massively in Excel 2007:
- the handling of Range names

If you go: ALT then i then n then d (in sequence) it brings up the range name defenition panel:
Delete range names

In Excel 2003, in order to delete range names, you need to select each one individually and press delete. The can be a real pain if you have loads to delete.

Excel 2007 improves on this by allowing you to sort the range names, select multiple, and delete them.

Well done Excel Dev.

; - >

Training Video: View, Edit and Delete range names

Talk about old habits dying hard...

Despite seeing this user friendly list, I was deleting them one at a time. Thx for the tip!

"ALT then i then n then d (in sequence)" - Nick, get real :)

Nick's picture

29. Calculating proportions with COUNTIF

Suppose you have the following data, and you want to calculate the percentage of people who are Male, and the percentage of Females

COUNTIF

Use the COUNTIF function to count the number of each in the range, then divide by the total to get the percentages.

Practise sheet

Training Video on calculating Proportions:

Nick's picture

28. Easily change the print area

Here’s a tip on printing and how to change the print area.

Suppose you have this data, and you want to print up to Data6
change the print area

Step1:
Select the data you want to print:
change the print area

Then go:
Xl 2003 File => Print Area => Set Print Area
Xl 2007 Page Layout => Print Area => Set Print Area
You’ll notice the dotted lines around the area you want to print. They indicate the end of the page.
change the print area

Step2:
Now suppose you want to change the print area to include Data7
Go View => Page Break Preview
change the print area
This shows Excel in page break view, and from here, you can change the print area by clicking on the edge of the highlighted area, and dragging it.
change the print area

The print area is actually just a range name, so if you bring up the range name dialog box, you can change it from there too.

Excel annoyingly doesn't allow you to get rid of the small dotted lines once you have set the print area, but there are 2 ways round this:
1. Select a cell a long way away from your work area, and set that as the print area
2. Close and reopen the Excel

Training Video - Change the print area

Nick's picture

27. Find and Replace across the whole workbook.

This tip shows you how to find and replace across the entire workbook.
You might spend ages looking for things when a simple trick can save lots of time.
Suppose you have a Workbook that looks like this:

Sheet1
Excel-Tips-Find-and-Replace-across-the-whole-workbook

Sheet2
Excel-Tips-Find-and-Replace-across-the-whole-workbook

You have the name “Mike” on 2 worksheets.
Now suppose you want to replace “Mike” with “Nick” in the entire workbook.

Step1:
Press CTRL + H... then Options... set it up like this:
Excel-Tips-Find-and-Replace-across-the-whole-workbook

Step2:
Click “Replace All”

Result:
Excel-Tips-Find-and-Replace-across-the-whole-workbook
Excel-Tips-Find-and-Replace-across-the-whole-workbook

If you only want to replace on certain worksheets, you can select them first by clicking on “Names1”, holding CTRL, then select “Names2” tab etc... and go to Step1

Training Video on Find and Replace:

Nick's picture

26. Data Validation - part 1

Data Validation is extremely useful functionality and I use it in every system I write.

The primary aim is to guide people into entering correct data. If you allow people to type whatever they like into a field, you end up with all kind of gunk.

There are a few ways to use Data Validation... we'll discuss the way based on other cells.

Suppose you want to restrict the number entered in the following example to even numbers between 2 and 10
Data Validation

Step1
Select the cell, In Excel 2003, go: Data=> Validation... In Excel 2007, Data => Data Validation and configure like so:
Data Validation

Step2
Click on the cell next to “Number” and press ALT + DOWN ARROW... You will now see the list appear and you can use the UP and DOWN arrows to select the one you want.
Data Validation

Sheet to practise with

Video Training on Data Validation:

Nick's picture

25. Conditional formatting

This tip is one of my favourites, and I have used it a lot for trading systems.

Suppose there's something sooo fundamental on your sheet that if you get it wrong, the consequences can be very costly indeed. Well, as a system designer, you should try your hardest to protect users from making thes mistakes.

Conditional formatting helps enormously here.

In my trading system, I have formatted the headings so that if you change the BUY to a SELL, the headings change colour from blue to red automatically.

BUY:
Data

SELL:
Data

The way to do this is the following:

Select the cells you want to format like this:
Data
In Excel 2007, from the Home ribbon, select conditional formatting => new rule, then set the formula as below:
Data
...choose the format you want in the event that the formula is true, and there you go !

Similar in Excel 2003..
Format => conditional formatting... then same principles apply.

Video Training on conditional formatting:

Conditional formatting in Excel 2003

It seems to me that in Excel 2003, it is only possible to set conditional formats against the cells which contain the data which form the criteria. for example I have an accounting spreadsheet where I want the whole row formatting to change according to the contents of the cells in column W. what I'm finding, is that I can only set conditional formatting for column W. based on what is in column W. thanks

Nick's picture

Conditional formatting in Excel 2003

jb - make sure that the "$" signs are there in the conditional formatting formula:

Data

does the trading system work for you?

Conditional formatting in excel 2003

Thanks. In 2003 I don't get this screen. I get a screen which only lets you set formatting in the column which contains the criteria. I think I might have to wait for an upgrade at work.
see here: http://office.microsoft.com/en-us/excel/HA010929431033.aspx?pid=CL100570... (I have to press F5 to get it to load every time I try to look at).

Try selecting "Formula is"

Try selecting "Formula is" from the drop down and the enter: =$B$2=BUY. If B2 equals "BUY" it will trip the formatting.

Nick's picture

24. Pasting values

Why is there no easy way to paste values in Excel ?.. huh, huh.

If I got a pound for every time I had to paste values in Excel, it wld be worth at least 7 EUROs, and I'd be a Zillionaire in Zimbabwean dollars.

The best way of performing this operation known to man is the following:

Step1:
Select range to copy, and press CTRL + C

Step2:
Select the place you want want to paste to, and press the following keys in order:
ALT then e then s then v then ENTER

..there are other ways using the mouse but ExcelExperts prefer to use the keyboard for everything. (Take note Mr Ribbon Designer)

Another way

Pasting values - Video Training

Nick's picture

23. Inserting rows and columns

When using Excel for your daily work, you will often need to insert rows and columns.

Remember, when you insert rows and columns, Excel changes the references so that your formulae do not appear to change materially, however, there are some things to watch out for.

Example with 2 columns, and an addition formula:
=B3+A3
Inserting-Columns

When you insert a column in between A and B, the formula adjusts like so:
Inserting-Columns
Note: nothing is materially affected - you are still adding 10 + 2

Now, suppose unstead that you use the SUM formula:
Inserting-Columns

... When you insert a column, the formula adjusts as you'd expect, but if you look carefully there is a difference. Now, cell B3 is included in the summation:
Inserting-Columns

SO... if you want the addition of new columns to be included in your summation formula, use the SUM function... If you don't, you have to use simple addition.

Practise sheet

.. same works for rows.

Video training on Inserting Rows and Columns:

Nick's picture

22. Finding the last cell for non contiguous range

So, you have data in column 1, and you want to create a formula in column 2, and copy it down.
Normally, this is a very easy task:
- enter the formlula, select the small square in the bottom right corner of your cell, and double click on it to automatically populate your 2nd column with the formula.

However, what happens when the data looks like this?

Find-the-last-cell

The autofill will stop on line 3.

One way to complete the task is to drag down manually, but there is a much better way.

Step1: (for this example)
Select cell b2, and copy it

Step2:
Press CTRL + END
This will take you to the bottom right

Step3:
Press CTRL + LEFT ARROW
This will take you to the bottom left

Step4:
Press CTRL + UP ARROW
This will take you to the bottom left of populated data

Step5:
Press right arrow once, then CTRL + SHIFT UP ARROW
This will select the correct area to paste into

Step6:
ENTER

Once you get quick at shortcuts, you can do this entire action in a few seconds.

Download sheet to practise on

Video Training on finding the last cell:

Real Last Cell

Ctr+End has a bug
Excel cannot automatically reset the last cell
Type
A
b
c

d

e

Say Ctr+End it will take you to cell containing e
Delete cell containining e, say Ctrl+home, and Say Ctrl+End again it still takes you to the blank cell which earlier had e

The most reliable way of going to the real last cell is
Say Select A1, Ctrl+f , in find What type * and say Shift + Find Next (Equivalent to find previous)

Nick's picture

Reset Last Cell

There is a way, but u have to use VBA...
Reset Last Cell using VBA

I like ur other way of doing it...

; - >

Nick's picture

21. Useful Excel shortcuts

There are lots of shortcuts in Excel, but In my 13 years of using Excel every day, these are the ones that I think are most useful and a good Excel user should know all of these:

Useful Shortcuts
Useful Shortcuts

Video Training on Useful Excel Shortcuts:

Nick's picture

20. VLOOKUP limitations - can use INDEX + MATCH instead

VLOOKUP is all very well when the data is arranged in the right way for the function - with the thing you're looking for to the left of the thing you want to return, but what happens if you want to do the reverse of VLOOKUP ?

Well, fortunately help is at hand with INDEX + MATCH.

MATCH returns the position of a value in a range
INDEX returns the value of a cell that is offset from another.

Combined, you can use INDEX + MATCH to simulate VLOOKUP, and to do the reverse lookup as well as the VLOOKUP:

INDEX MATCH

Index Match Spreadsheet

Training Video on VLOOKUP INDEX MATCH

Index/Match

You dont have to select the entire table
=Index(b$3:b$6,match(e4,$b$3:$b$6,0))

There are lots of other advantages
Match has 3 type 0 = False of VLOOKUP
1 = True of VLookup
-1 = No Equivaltent in Vlookup

Index/Match works for both vertical and horizontal tables

Index/match array entered can be used to match based on multiple criteria
Index + a few more functions - array entered can be used to find the 2nd instance or the 3rd instance of the item that you are searching for

The MATCH function can be used to locate the last non blank cell in a row (even if there are blanks in between)

the Index/match fucntion can be used to create dependent Validation dropdowns

Nick's picture

19. Function to return the worksheet name

There isn't a single function to return the sheet name, but you can use a combination of functions to get at it.

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))

- Here's a breakdown of what it's doing:
Sheet Name example

Training Video on getting Worksheet Name:

Nick's picture

18. Using the TODAY function

I come from a financial background, and the most important date in finance is Today's date. It can mean many things, but the most significant is that it is the date at which the discount factor = 1, but I'll save that discussion for another day.

If you're writing a bond or derivative pricing function, you will need to enter today's date. The Excel function for that is: =TODAY()

- However, this function comes with a toxic hazard warning as it's the most common cause of sluggish spreadsheets in investment banks.

- The problem is that it's a volatile function, and will calculate whenever any cell changes on the sheet, AND for common usage like inserting a row or column.

- SO, the advice is: If you have a spreadsheet, that does a lot of calculations, it requires today's date, and calculation speed is a problem for you... DO NOT USE =TODAY()

Training Video on sluggish spreadsheets caused by the TODAY function:

Good tip... Will avoid it in

Good tip... Will avoid it in the future.
Is there an alternative?

Nick's picture

Alternatives to TODAY function

There are 2 ways:

1. Type in today's date when you arrive in the morning
2. Use some VBA to update TODAY's date to a static value on opening the spreadsheet, and every morning thereafter.

.. I'll add that to my VBA tips

Nick's picture

17. Date formatting - Show the day of the week

Today's tip is a quick one as I am feeling lazy, but it's a useful one none the less.

- By formatting dates in a certain way, you can display the day of the week, and not using much space.

Step1:
Select A1, and press CTRL + ;
(this gives you today's date)

Step2:
Right click => format cells => custom
type: ddd dd-mmm-yy

For today, you get:
Sat 27-Dec-08

- this is my favourite date format for the following reasons:
1. the "08" is all you need in this century
2. Sat = day of week
3. 27-Dec => it's completely unambiguous and much better than infuriating American date format of: 12/27/2008 (mm/dd/yyyy), as you will find out when it's the 2nd Jan (01/02/2009)... or is it the 1st of Feb ?!
[End of rant]

; - P

Video training on Date formatting:

Nick's picture

16. Hiding sheets - secret trick

There are 2 ways to hide a sheet.

Most people know the first way:
Format => Sheet => hide

Not many know the second way:

Press ALT + F11 to bring up the VBA editor.

.. and change the visible option as shown:

Pic

To the untrained eye, this Very Hidden sheet is difficult to find.

If you don't see the "properties" box, single click on "Sheet2" and press F4

Training Video on how to hide sheets:

Nick's picture

15. Wrap Text for headings

Screen real estate is a valuable commodity, and with this tip, you can save using it unnecessarily.

If you autofit the columns with large headings, it will look like this:

Wrap Text
However, to get it to this is only a few steps:

Wrap Text
Step1:
Select the row, right click, format cells, alignment... and click "Wrap Text"

Step2:
Still with the row selected, right click, row height, and change it to something huge like 200

Step3:
For each column reduce the column size manually until you can see each word on one line.

Step4:
Then select the column and type in sequence: ALT then O then C then A

Step5:
Select the row again, and type in sequence: ALT then O then R then A

..if you don't like the look of having one word on each line, the same process works for having 2 words per line.

Training Video on Wrapping Text for large headings: