PDA

View Full Version : Excel



jmcarson
April 17th 2007, 09:01 AM
I have a solid foundation in Excel but would like to brush up my old skills and learn some new ones, especially with respect to writing macros. Can anyone recommend a good book that I can teach myself Excel skills with?

Thanks for your time.

FSA
April 17th 2007, 02:01 PM
I have, on my shelf, two books by John Walkenbach and they are all I've ever needed:

"Excel 2003 Power Programming with VBA"
and
"Excel 2003 Formulas".

Every time Excel comes out with a new version he updates the books, so there are Excel 2002 and Excel 97 versions. There should be a 2007 version out soon, if not already.

His website, by the way, is www.j-walk.com.

FiscGen
April 30th 2007, 06:29 PM
No doubt about it. I also have Excel Power Programming with VBA and it is an awesome book.

aleburns
May 20th 2007, 04:44 PM
If you want something specifically for writing macros and already have a good Excel foundation, look for an O'Reilly book for VBA programming in Excel.

Thomas H
May 21st 2007, 08:33 PM
Is there a book that walks a newbie through the topics and has problems to work with example code?

For example, there is a book C++ for Mathematicians that walks you through the topics and has problems at the end of the chapters. Solutions in the form of sample code are presented so that you can see how to actually code up what the problem wanted. I thought it was a great self-learning tool. I'd like to find something like that to learn the VBA coding.

Jo_M.
June 2nd 2007, 11:32 AM
I took a Visual Basic programming course last semester, and we used this book:

Microsoft Excel VBA Programming (2nd ed) for the absolute beginner. (By Duane Birnbaum)

Its a good book to start with when you know nothing about visual basic programming, but are familiar with the basics of excel.

Also, there are challenge questions and ''projects'' at each chapter to help you practice.

robertr24
June 14th 2007, 12:36 AM
Try these. (http://www.amazon.com/s/ref=nb_ss_gw/103-6269248-0459856?url=search-alias%3Daps&field-keywords=Excel+Step+by+Step&Go.x=0&Go.y=0&Go=Go)

There are explanations and many brief exercises that teach you pretty much all the basic stuff you need to know (formatting, pivot-tables, graphs, basic macros, etc.)



Looking back at your post, I realized you already have a solid foundation in the basics. Nevermind, insomnia's a real bummer....

djerry81
June 14th 2007, 07:12 AM
go to google type learn excel and wal la

hatch33049
June 23rd 2007, 05:56 AM
HP offers online courses and I think that maybe Excel is one of them. I took the MS Project course and liked it. Best of all, it's FREE! www.hp.com

Good luck to you!

325
July 20th 2007, 04:48 PM
I have a solid foundation in Excel but would like to brush up my old skills and learn some new ones, especially with respect to writing macros. Can anyone recommend a good book that I can teach myself Excel skills with?

Thanks for your time.

I'm in the same situation as you. I went to a bookstore today and saw there were books on excel 2007 and 2003. I've been using excel 2003 at home for school assignments but if all the companies are using 2007 now then i may as well just get the 2007 edition.

what do you think?

jazbury1
May 19th 2008, 04:07 PM
I doubt many companies are eager to upgrade Excel right away. (Yes, one million rows is a tremendous improvement, but upgrades can be expensive in several ways.) If you know Excel 2003 I'm sure you will do fine with Excel 2007.

As for finding books for learning Excel and VBA...
I have nothing against books. However, there are many free websites dedicated to Excel and VBA. It is entirely possible to teach yourself to use both software packages by way of research on the web and experimenting.

A good place for you to start with Excel VBA is http://www.anthony-vba.kefra.com/vba/vbabasic1.htm#Creating_Your_First_Macro
One website I use frequently for reference is www.vbaexpress.com. Another good resource for the difficult problems is www.experts-exchange.com.

Irish Blues
May 20th 2008, 01:59 PM
One of the beauties of going from Excel 97 to 00 to 03 was that each version looked substantially like the one before. '07 looks nothing like '03.

I've tried to use Excel 2007. IMO, it ''''' royally because it is incredibly unfriendly and difficult to navigate in.

excelpro
July 15th 2008, 01:48 PM
I can sympathize. But there are a lot of hidden gems still associated with Excel (2003 or 07). Yes, unfortunately these typically aren't covered in text references, with the exception of perhaps "Excel Basics to Blackbelt" which covers some fairly unique tricks and capabilities you won't find anywhere else (also a good refresher for those moving from 2003 to 2007).:coolman:

edwinjaxfl
September 16th 2008, 08:27 PM
I liked that hp.com excel tutorial - thanks

dabears32
November 26th 2008, 10:18 PM
Hi, I am doing a project in excel for a class. Today I went to change a number that I had typed in wrong and it did not change all of the other numbers (other cells with this cell linked in the formula). I had to go to every cell that mentioned this cell and push enter. When i Have done excel projects before it did this automatically. Any tips on how to fix this would be appreciated. Thanks

NoMoreExams
November 26th 2008, 10:47 PM
Tools -> Options -> Calculation Tab. What is it set to? Automatic or Manual?

mughal010
November 27th 2008, 12:10 PM
yep u have to set it to automatic and make sure that iterations are about 10,000.

NoMoreExams
February 19th 2009, 10:39 AM
Hi friends

To modify the Data you entered earlier. Copy the entire datasheet except the cell which you need to to modify and then past the whole datasheet at a new datasheet . Now you can change the value of the specified crell.

Joseph

That's not what he was asking and pretty sure it got answered twice.

KA-time
August 28th 2009, 01:39 AM
I am carrying out an audit of a scheme to verify the accuracy of amounts held in individual accounts. The data is very messy and unique identifiers change year in year out. Names too aren’t identical so I can’t call in data from other worksheet unless I do it manually by searching one by one. Does anyone have an idea how I can go about this?



I was thinking of a macro that determines the likelihood of two names being identical and returns the row number of the cell where names are most likely to be identical by working as follows

· Assume we have two worksheets A & B,

· We want to call in data from worksheet B by using names in A

· Let z be an integer

· The names are not fully identical eg we have John Albert in A and the name of the person in B is indicated as John Albert James.

· The macro takes the first two letters, Jo, and determines if the same letters are available in some cell in B, preferably over a specific range

· If the letters are in that cell, the add 1 to z where initially z=0, otherwise add 0

· The same procedure is repeated with the second pair of letters, “oh” to the last “rt” (spaces should not be ignored)

· Then z is divided by the maximum len between the two cells in comparison

· This “probability” is stored inn temporary memory

· This is repeated for all the cells in range containing names in B

· Then the row number with the highest probability is returned





The problem is that I am a beginner in macros and at the moment I can’t be able to implement the above solution



The code for the above will be highly appreciated



Any other suggestions are also welcome.

Sinjin
August 28th 2009, 03:42 PM
What sort of format are the name fields in?
Are they all: "NAME SURNAME [MIDDLE NAME]"
Or are those 3 fields likely to switch around.

If the format is undetermined, I would match names like so:


a = uppercase("John Albert")
b = uppercase("John Albert James")
arr_a = a.split(" ")
arr_b = a.split(" ")

if length(arr_a) >= length(arr_b)
max = length(arr_a)
else
max = length(arr_b)

matches = 0
for n in arr_a
if n in arr_b
matches += 1
arr_b.pop(n)

probability = matches / max

I really don't know what your data looks like, so I am just suggesting blindly.

jlehmann
December 9th 2009, 07:36 AM
I was wondering which version of excel most actuaries use. I am trying to get some tech skills under my belt to make myself a more desirable hire and would like to know which direction would be the most beneficial to me 2003 or 2007.

Undercoat
January 15th 2010, 11:06 AM
Also, use the help menu to learn keyboard shortcuts. They really help to get work done quickly.

Learning a newer version of any software is probably better.

SandiOwl
August 9th 2010, 10:03 AM
This is a good site with some online Excel tutorials:
Techtutorials.net (http://www.techtutorials.net/tutorials/applications/microsoft_office/excel.html)

Although I don't think that you can beat learning in a classroom environment myself. It can take a day or two out of your week but you'll learn an awful quicker with someone to point out where you're going wrong immediately rather than once you've spent hours creating a spreadsheet that falls over!
Best courses I've seen are at Wise Owl (http://www.wiseowl.co.uk) in the UK or at Learning Tree (http://www.learningtree.com/) for the US.

BTW, Excel 2007 is vastly different from 2003 but the macros part is more or less identical.

SandiOwl
August 9th 2010, 10:19 AM
I woudl recommend the 2007 version. You'll get a free upgrade to v.2010 as well. There's not much difference between these two (2007 & 2010) - MS have just speeded up the calcalations in v.2010 - this was was a big negative with 2007 in that if you have huge spreadsheets, it took longer for calculations to run than v.2003 but they've sorted it our in v.2010.
You can see a summary of the differences between the two versions here (http://www.wiseowl.co.uk/ms-training-version/version-index.htm)

alekhine4149
August 9th 2010, 05:50 PM
Agree with 2007. Some things to know well in Excel due to their ubiquity: vlookup, data to columns, concatenate, custom sorting, conditional formatting, paste special, using the $ sign before symbols in formulas.

Irish Blues
August 23rd 2010, 01:46 PM
Learn Excel, period. If you can work in pre-2007, you'll be able to work in 2007 or later. Things you should know [in a vaguely particular order]:

-- Shortcut keys [especially cut, copy, paste, insert, delete, undo/redo, and change/replace]
-- Formatting and conditional formatting
-- Sorting and filtering
-- VLOOKUP and HLOOKUP, INDEX and MATCH, and OFFSET [don't use OFFSET a lot, just know how it works because a lot of files you'll run across will probably use it]
-- Pivot Tables [lots of computational power for little memory cost]
-- Naming cells and ranges [learn how to name your ranges dynamically, you'll make yourself look incredibly smart]
-- VBA [if you can do all of the above, and then put Visual Basic to work to do some of it via macros, ... you will be on the road to being an Excel guru in your office].

SandiOwl
August 24th 2010, 03:16 AM
If you are new to Excel, I would learn the 2007 version (2010 no difference to speak of). Although there are some things that remain the same, 2007 has some new and better features in 2007 which are a mystery for 2003 users until they spend time finding their way around. It's definitely one of those "if you don't know they're there then you won't use them" situations! The visual basic part of it is no different at all.
There's a good summary of the changes here (http://www.wiseowl.co.uk/ms-training-version/training-microsoft-excel.htm):