Blogs
45. Excel Tips - Modify Array Formula
Submitted by Nick on 9 March, 2009 - 11:05This 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.
- Nick's blog
- Login or register to post comments
- Read more
- 14458 reads
44. Excel Tips - Enter Array Formula
Submitted by Nick on 9 March, 2009 - 11:03This 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:
- Nick's blog
- Login or register to post comments
- Read more
- 8781 reads
43. Excel Tips - Function debugging - using F9
Submitted by Nick on 9 March, 2009 - 11:01
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 blog
- Login or register to post comments
- Read more
- 16005 reads
42. Excel Tips - Deleting Sheets
Submitted by Nick on 9 March, 2009 - 10:59Easy 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 blog
- Login or register to post comments
- Read more
- 6382 reads
41. Excel Tips - Data Validation for percentages
Submitted by Nick on 9 March, 2009 - 10:57This 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 blog
- 1 comment
- Read more
- 35832 reads
40. Excel Tips - Remove Dupes With Multiple Columns
Submitted by Nick on 9 March, 2009 - 10:56
This is a follow-on from yesterday's tip and relates to XL 2003
- Nick's blog
- Login or register to post comments
- Read more
- 6487 reads
39. Excel Tips - Remove Duplicates from a sorted list
Submitted by Nick on 9 March, 2009 - 10:53
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 blog
- 2 comments
- Read more
- 13025 reads
38. Excel Tips - Copy Down
Submitted by Nick on 9 March, 2009 - 10:52
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:
- Nick's blog
- Login or register to post comments
- Read more
- 8033 reads
37. Excel Tips - Worried about security ? - open Excel Safely
Submitted by Nick on 9 March, 2009 - 10:36
There is a way to open Excel safely which prevents macros from running. - Excel sheet calculations will still run.
- Nick's blog
- Login or register to post comments
- Read more
- 11399 reads
36. Excel Tips - How many times does a letter appear in a cell ?
Submitted by Nick on 9 March, 2009 - 10:22
Suppose you have a big string like: "XXX0000sdsds000000000" and you want to know how many times "0" appears.
Here's our data:
The formula we use is:
=LEN(B3)-LEN(SUBSTITUTE(B3,"0",""))
- Nick's blog
- 2 comments
- Read more
- 13299 reads
35. Excel Tips - Get Initials from a Name
Submitted by Nick on 9 March, 2009 - 10:07
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:
The formula we use is:
=LEFT(B3)&MID(B3,FIND(" ",B3)+1,1)
- Nick's blog
- 2 comments
- Read more
- 62763 reads
34. Excel Tips - Introduction to Pivot tables
Submitted by Nick on 9 March, 2009 - 09:53
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:
- Nick's blog
- Login or register to post comments
- Read more
- 26505 reads
33. Excel Tips - Moving Averages
Submitted by Nick on 9 March, 2009 - 09:41
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:
In cell F3, we enter the number of days we want to calculate the average for.
- Nick's blog
- 2 comments
- Read more
- 42963 reads
32. Excel Tips - Using Go To
Submitted by Nick on 9 March, 2009 - 09:40
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:
- note all the range names are listed and you can select any of them to go to that range.
- Nick's blog
- Login or register to post comments
- Read more
- 6932 reads
31. Excel Tips - Create Range names quickly
Submitted by Nick on 9 March, 2009 - 09:37
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"
Step1:
Select the range (B2:C20 in this case)
- Nick's blog
- Login or register to post comments
- Read more
- 7405 reads
30. Excel Tips - View, Edit and Delete range names - well done XL 2007
Submitted by Nick on 9 March, 2009 - 09:35
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:
- Nick's blog
- Login or register to post comments
- Read more
- 4086 reads
29. Excel Tips - Calculating proportions with COUNTIF
Submitted by Nick on 9 March, 2009 - 09:32
Suppose you have the following data, and you want to calculate the percentage of people who are Male, and the percentage of Females
Use the COUNTIF function to count the number of each in the range, then divide by the total to get the percentages.
- Nick's blog
- Login or register to post comments
- Read more
- 49796 reads
28. Excel Tips - Easily change the print area
Submitted by Nick on 9 March, 2009 - 09:20
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
Step1:
Select the data you want to print:
Then go:
- Nick's blog
- Login or register to post comments
- Read more
- 47069 reads
27. Excel Tips - Find and Replace across the whole workbook
Submitted by Nick on 9 March, 2009 - 09:17
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
Sheet2
- Nick's blog
- 1 comment
- Read more
- 37112 reads
26. Excel Tips - Data Validation - part 1
Submitted by Nick on 9 March, 2009 - 09:15
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 blog
- Login or register to post comments
- Read more
- 7915 reads
25. Excel Tips - Conditional formatting
Submitted by Nick on 9 March, 2009 - 09:13
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 blog
- Login or register to post comments
- Read more
- 9343 reads
24. Excel Tips - Pasting values
Submitted by Nick on 9 March, 2009 - 09:12
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 blog
- Login or register to post comments
- Read more
- 6336 reads
23. Excel Tips - Inserting rows and columns
Submitted by Nick on 9 March, 2009 - 09:10
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 blog
- Login or register to post comments
- Read more
- 6369 reads
22. Excel Tips- Find the last cell for non contiguous range
Submitted by Nick on 9 March, 2009 - 09:02
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 blog
- 1 comment
- Read more
- 9126 reads
21. Excel Tips - Useful Excel shortcuts
Submitted by Nick on 9 March, 2009 - 09:00
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:
Video Training on Useful Excel Shortcuts:
- Nick's blog
- Login or register to post comments
- Read more
- 11556 reads
20. Excel Tips - VLOOKUP limitations - can use INDEX + MATCH instead
Submitted by Nick on 8 March, 2009 - 21:28
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 blog
- 6 comments
- Read more
- 62682 reads
19. Excel Tips - Function to return the worksheet name
Submitted by Nick on 8 March, 2009 - 21:27
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:
Training Video on getting Worksheet Name:
- Nick's blog
- 10 comments
- Read more
- 98576 reads
18. Excel Tips - Using the TODAY function
Submitted by Nick on 8 March, 2009 - 21:25
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 blog
- Login or register to post comments
- Read more
- 7353 reads
17. Excel Tips - Date formatting - Show the day of the week
Submitted by Nick on 8 March, 2009 - 21:24
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 blog
- Login or register to post comments
- Read more
- 33440 reads
16. Excel Tips - Hiding sheets - secret trick
Submitted by Nick on 8 March, 2009 - 21:21
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:
To the untrained eye, this Very Hidden sheet is difficult to find.
- Nick's blog
- Login or register to post comments
- Read more
- 7333 reads
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago