Excel course/books/online

S

Stefanmuller

Expert Member
Joined
Mar 12, 2008
Messages
2,924
Reaction score
128
Location
Durbanville
I would like to advance my Excel skills. I use the basic functions like manual formulas, sorting, filters, drag formulas, basic lookups etc at work.

Things I need some tips on is how to manipulate and interrogate a database. I am an accountant so there is a lot you can do from exports from accounting packages beyond just the normal sorting if you know how. Not an essential skill to have but can save you lots of time.

Anyone know courses or books than take you through the most useful and powerful excel functions for interrogating databases into usefull info? I dont want to know everything about excel but rather would like to be skilled in the important and most useful stuff. I did Excel in undergrad but that was 12 years ago. I know there are lots of online tutorials but you first have to know about the function before you can google how to use it. I want a course that can tell me about all the functions and how to use it.

nothing more frustrating than being able to tell an 8 year old how to manually look through data line by line and bring back a specific value and then knowing you can obviously use the program to do it for you in an instant but dont know how to actually tell it how or which function to use.
 
HavocXphere said:
What kind of database are we talking about here?
Click to expand...

Depends. Mostly a general ledger export so you will have different accounts with field containing date, document number, reference, detail transaction description and amount. But obviously I dont only use general ledgers as databases. I can get any kind of info to do work on.

For instance, the other day someone I know had a list of employees with next to it the name of the training course they have completed and the date they started it and whether they have completed it or not. Lets say 30 people and 12 different courses. The list only has it separately per course in other words with names down in column A and the rest of the detail in the colums next to it. So if John did 5 courses his name will appear 5 times in column A and next to each only the details of one course. What they wanted from the data is a table format with all the names on the y axis and the different courses on the x axis, basically showing an X in the block opposite name and specific course if they have completed it. I got more or less to the required using a pivot table.

But it got me thinking that I need to hone up on my excel skills. Because it is simple to tell a 8year old to manually do it for you so it should be easy to instruct the computer to do it. It was entirely non work related though but it got me thinking that better skills vcan save me a lot of time and also can help me give clients added value by using their data to get meaningfull and helpful info.
 
I see. Try this:

http://excelexposure.com/

Dunno...most of my Excel skills were acquired via trial & error tbh. Boils down to throwing the right combination of formulas at things in a nested fashion rather than using more advanced formulas in my experience.
 
When importing data from external applications into Excel - the most common formats are Text Files - usually using a character such as a tab or a space as the delimiter (can also be CSV - comma separated values text file).

Once you have imported the text file into Excel (the 'From Text' button in the Get External Data group on the Data tab), you can use the 'Text to Columns' button (in the Data Tools group) to further define your data set - like splitting one column of data into two (or more) columns based on the delimiter character - or to delete an unnecessary column or set of data.

Once you have the data table set up the way you want to analyse the data, you can use the 'PivotTable' or 'PivotChart' button (on the Insert tab) to then set up your report just the way you want, so that your data can be analysed more effectively.


See this MS Office link for more details:

http://office.microsoft.com/en-za/e...le-to-analyze-worksheet-data-HA102840046.aspx
 
HavocXphere said:
I see. Try this:

http://excelexposure.com/

Dunno...most of my Excel skills were acquired via trial & error tbh. Boils down to throwing the right combination of formulas at things in a nested fashion rather than using more advanced formulas in my experience.
Click to expand...

Same here. I would use the Sort function 3 times ie first sort the whole list, then sort the sublist resulting from the first Sort by a different field and the sort that again. But obviously there is a function that can get you to the end result with one "run". I use Sumifs and basic Vlookup but there is a lot more you can do.
 
Ivan Leon said:
When importing data from external applications into Excel - the most common formats are Text Files - usually using a character such as a tab or a space as the delimiter (can also be CSV - comma separated values text file).

Once you have imported the text file into Excel (the 'From Text' button in the Get External Data group on the Data tab), you can use the 'Text to Columns' button (in the Data Tools group) to further define your data set - like splitting one column of data into two (or more) columns based on the delimiter character - or to delete an unnecessary column or set of data.

Once you have the data table set up the way you want to analyse the data, you can use the 'PivotTable' or 'PivotChart' button (on the Insert tab) to then set up your report just the way you want, so that your data can be analysed more effectively.


See this MS Office link for more details:

http://office.microsoft.com/en-za/e...le-to-analyze-worksheet-data-HA102840046.aspx
Click to expand...

Yep, I think it is worth investigating pivot tables further. What I want is to get a crash course on the most important functions using simulated data to explain it. A good example would be using one comprehensive data base and the go through a tutorial showing you what Excel can do with that data. Most online tips are too specific.
 
Your best bet would be to attend an Excel course at a dedicated IT training centre - they have specific modules that cover Data Analysis & Pivot Tables.

Do a Google Search for Excel courses in Cape Town - here is one I found:

EMI Consulting (trading as Excel Skills International)
Reg No: 2005/033664/23 | VAT No: 4530236449

Business Address
23A High Street, Durbanville, Cape Town, South Africa, 7550

Contact Details
Contact Person
Wilhelm van Noordwyk CA(SA)

Telephone
+27 82 340 5968

Fax:
0866 718 919

E-mail
[email protected]

Website Enquiries
[email protected]
 
Ivan Leon said:
Your best bet would be to attend an Excel course at a dedicated IT training centre - they have specific modules that cover Data Analysis & Pivot Tables.

Do a Google Search for Excel courses in Cape Town - here is one I found:

EMI Consulting (trading as Excel Skills International)
Reg No: 2005/033664/23 | VAT No: 4530236449

Business Address
23A High Street, Durbanville, Cape Town, South Africa, 7550

Contact Details
Contact Person
Wilhelm van Noordwyk CA(SA)

Telephone
+27 82 340 5968

Fax:
0866 718 919

E-mail
[email protected]

Website Enquiries
[email protected]
Click to expand...

Cool thx. Seeing as he is a CA himself he would know which functions would be useful to me.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X