Blogs

Nick's picture

45. Excel Tips - Modify Array Formula

This is a follow-on from tip on entering an array formula in Excel

Once you have entered and array formula in Excel, you will notice that Excel won't let you modify it one cell at a time.

Modify-Array-Formula-Excel

Nick's picture

44. Excel Tips - Enter Array Formula

This Excel tip explains step by step how to enter an array formula. Why do I need array formulae ? I hear you ask. Well, one of the reasons is that you need them for functions that do not return their results in a single cell. Lets take MINVERSE for example. The following screen shot shows the data we're using:

Enter-array-formula-excel 

Nick's picture

43. Excel Tips - Function debugging - using F9


This is a follow-on from the first tip on debugging a large Excel function call

This Excel tip is really one of the most useful ones I can think of. I use it all the time, so make sure you understand it

Suppose you have a large function call, and you want to understand what is going on.

Nick's picture

42. Excel Tips - Deleting Sheets

Easy tip today... it's about how to delete sheets.

To delete a worksheet:

If you have selected the worksheet, press in sequence: ALT the e then l

Nick's picture

41. Excel Tips - Data Validation for percentages

This is a follow-on from tip 1 on Data Validation

When adding data validation to cells containing percentages, there is a little trick you need to know. If you're calculating a proportion: 100% being everything, 0% being nothing, you need to set the data validation to DECIMAL with range 0 to 1.

Nick's picture

40. Excel Tips - Remove Dupes With Multiple Columns


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

Nick's picture

39. Excel Tips - 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:

Nick's picture

38. Excel Tips - 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

Nick's picture

37. Excel Tips - Worried about security ? - open Excel Safely


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

Nick's picture

36. Excel Tips - How many times does a letter appear in a cell ?


Suppose you have a big string like: "XXX0000sdsds000000000" and you want to know how many times "0" appears.

Here's our data:
How-many-times-does-a-letter-appear-in-a-cell

The formula we use is:
=LEN(B3)-LEN(SUBSTITUTE(B3,"0",""))

Nick's picture

35. Excel Tips - Get Initials from a Name


Suppose you have a list of Names, and you only want the initials of the people.

There's an easy way to do this in Excel

Here's our data:
initials-from-name-excel

The formula we use is:
=LEFT(B3)&MID(B3,FIND(" ",B3)+1,1)

Nick's picture

34. Excel Tips - 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:
Introduction to Pivot tables

Nick's picture

33. Excel Tips - 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:
moving average

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

Nick's picture

32. Excel Tips - 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:
Go To
- note all the range names are listed and you can select any of them to go to that range.

Nick's picture

31. Excel Tips - 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)

Nick's picture

30. Excel Tips - View, Edit and Delete 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

Nick's picture

29. Excel Tips - 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.

Nick's picture

28. Excel Tips - 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:

Nick's picture

27. Excel Tips - 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

Nick's picture

26. Excel Tips - 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.

Nick's picture

25. Excel Tips - 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.

Nick's picture

24. Excel Tips - 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:

Nick's picture

23. Excel Tips - 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

Nick's picture

22. Excel Tips- Find 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?

Nick's picture

21. Excel Tips - 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. Excel Tips - 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.

Nick's picture

19. Excel Tips - 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. Excel Tips - 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()

Nick's picture

17. Excel Tips - 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

Nick's picture

16. Excel Tips - 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:

hide sheets

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

Syndicate content