Cool Features
I have been working with Excel and training users on excel for over eight years. I have been working with excel 2007 for over six months now and have found it to be completely different from the older versions. I find that this new version was harder for me to get used to when I used to know where ever little menu item was and each function and how to use them. They went and changed the entire menu and threw me for a loop. I would say now I am back to where I feel comfortable again with excel. I have found some really neat features in 2007.
Here are a few that I have found that I would believe would be useful in day to day activities when you are working with large amounts of records and data.
1. Remove Duplicates. I have longed for this feature to arrive and I probably use more than most. As it states it will remove duplicates from a range of cells and give you an overview of how many it removed and the remaining distinct values upon completion of the function.
to use this function, Choose a data range in your workbook, then click on the data menu, and then choose "Remove Duplicates" button.
2. PivotTables have changed but are still a valuable tool for any office that analyzes large data sets. The newest utility in this existing feature is the "Defer Updates" button. This is a great feature as it will allow you to play around with the formatting of the pivttable dragging and dropping fields to rows and columns without physically updating the pivottable after each change. This is especially useful if you are working with a lot of data. To use this feature create your pivottable on the fields menu on the bottom left you will see the "Defer Updates" option.
3. Row and column limitation. The angels have finally sung. The new row limitation is 1,048,576. Yes this is right over a million rows of data you can have now in your workbooks, you are no longer limited to creating external csv files and using data access queries to get around the 65k row limitation.
I absolutely love this feature. Always keep in mind though the number of rows and columns in your workbooks will always make them larger. If space is a critical issue, I would look at expanding your drives as the files are now able to get a lot larger.
Now I must mention a couple of things I do not like in excel 2007.
Charts - Converted charts from older workbooks seem to be a memory hog and they just do not seem to function properly or quickly.
I really am not impressed with the way they changed the data access connection menu. I think for the average user it just leads to more confusion then there once was. I would recommend only using this feature if you really are familar with Data access queries their abilities and limitations.
Lastly I am not a big fan of where they moved the excel options from the file menu tab to the windows menu. I find it a bit cumbersome to use and things like look at a files properties seems to be harder to get to then just going to File, properties. You can always find little workarounds to make your life easier as have I. I drag alot of the functions that I regularly use into my own little menu in my default workbook properties. I find this is easier for me to manuever around the new layout.
but then again I am a command line girl and like to keep things as simple as possible.
If you have any cool new "functions" or timbits you want me to include in one of my articles send me a comment.
If you are ever looking for excel help a good forum to check out is
http://www.dbforums.com/microsoft-excel/
Saturday, January 23, 2010
Tuesday, April 7, 2009
Microsoft Excel - Data access queries
Using a procedure in a microsoft data access query in excel.
Simple
- Create your procedure in sql server, grant execute permissions to the login that will be used from within excel.
- Open a blank workbook in excel - create your microsoft data access query (see previous blog of mine about how to set up and create a query)
- Instead using a select statement call your procedure for example:
- execute burkularsamples.dbo.myproc
voila you are done.
Here is an example of code that uses a parameter
execute burkularsamples.dbo.myproc2 '2009-01-07 22:38:39.340'
How to create microsoft data access queries through excel
One of the best little know useful tools out there in accessing your databases. The microsoft datbase access query is a truly easy way to access sql databases and other databases.
I use this to create end user reports. It is a fast and efficient way to get into a database and return a result set if you want it to have a pretty front end without a lot of effort.
Okay so how do you do it? I will explain in Excel 2002 but i will work for most versions.
First open a blank workbook. Second select Data --> Import External Data --> New database query from the menu tab.
Select (New datasource) from the window, if you are familar with odbc or SQL in general creating a connection will be a breeze. For example give your datasource a name. I try to give each datasource some specific details in the description in case I want to reuse it for other queries later on. for this example i would use Employeedatabaseondevserver
Then pick a driver from the drop down menu, for this example I am using SQL Server
then click "connect" in the dialog window type the name of your sql server instance. in this example my sqlserver and instance is burkular\sqlburkular, I would recommend you specify a login id and password, using trusted connection is okay but if you want others to use it they must have the same credentials as you. so i usually use a local sql server username and password that has very little access (ie read permissions and execute permissions to specific procedures only within specific databasses) . Then click options here you can specific a specific database or leave the default. If you are going to create multiple reports off the same datasource then you just need to qualify the database name in your query explicitly. I recommend this it is easier. Once connected "master" or your default database will appear. In step 4 select a default table, leave this blank, it is not necessary. Then it is up to you and your security practices as to whether or not you want to save username and password, I tend to leave this unchecked, yes you will be prompted, but it is safer if you don't want others to be able to run the report without pluging in specific credentials each time. If you don't care about the security then save the username and password, but remember others can see this in the code. Then click "Ok" and then "Ok" again. A dialog window will pop up that will expose access to available database items from the default database. choose the first one that pops up or a table that you know is really small, don't worry you will be getting rid of it soon enough from your query. I have not figured out a way to bypass this yet, so just do it. Once you click the table the available columns will show up in the right pane, click next, next, next, you should be at a window that is titled "Query Wizard - Finish" Click view data or edit query in Microsoft Query then hit finish. A query dialog screen will invoke showing your default table and a query editor screen. This is the money location....
From the menu click on "SQL" an SQL statement window will pop up. Now if you made to here then you can pretty much do everything you need to. Clear out the existing select statement, replace it with whatever select you would like.
Here is an example if you have access to pubs database and you did not drop the authors table.
Select * from pubs.dbo.authors (ensure you specify the database and the schema)
then hit execute.
Voila data is returned
1. Yes you can have mulitple table joins
2. You can copy your code from query analyzer into this window
3. Yes you can use parameters.
4. Yes you can set parameters up in your excel spreadsheet and use these to return specific results from your query.
5. No you don't have to fully qualify your tablename.column names each time.
6. You are limited to 65536 rows of data being returned in any excel version earlier than 2007
7. Yes you can re-query your data by setting up conditions on the access query.
8. Yes you can use procedures... this is a bonus.
9. Yes you can access views.
10.. Want to learn some of these options follow my upcoming blogs
I use this to create end user reports. It is a fast and efficient way to get into a database and return a result set if you want it to have a pretty front end without a lot of effort.
Okay so how do you do it? I will explain in Excel 2002 but i will work for most versions.
First open a blank workbook. Second select Data --> Import External Data --> New database query from the menu tab.
Select (New datasource) from the window, if you are familar with odbc or SQL in general creating a connection will be a breeze. For example give your datasource a name. I try to give each datasource some specific details in the description in case I want to reuse it for other queries later on. for this example i would use Employeedatabaseondevserver
Then pick a driver from the drop down menu, for this example I am using SQL Server
then click "connect" in the dialog window type the name of your sql server instance. in this example my sqlserver and instance is burkular\sqlburkular, I would recommend you specify a login id and password, using trusted connection is okay but if you want others to use it they must have the same credentials as you. so i usually use a local sql server username and password that has very little access (ie read permissions and execute permissions to specific procedures only within specific databasses) . Then click options here you can specific a specific database or leave the default. If you are going to create multiple reports off the same datasource then you just need to qualify the database name in your query explicitly. I recommend this it is easier. Once connected "master" or your default database will appear. In step 4 select a default table, leave this blank, it is not necessary. Then it is up to you and your security practices as to whether or not you want to save username and password, I tend to leave this unchecked, yes you will be prompted, but it is safer if you don't want others to be able to run the report without pluging in specific credentials each time. If you don't care about the security then save the username and password, but remember others can see this in the code. Then click "Ok" and then "Ok" again. A dialog window will pop up that will expose access to available database items from the default database. choose the first one that pops up or a table that you know is really small, don't worry you will be getting rid of it soon enough from your query. I have not figured out a way to bypass this yet, so just do it. Once you click the table the available columns will show up in the right pane, click next, next, next, you should be at a window that is titled "Query Wizard - Finish" Click view data or edit query in Microsoft Query then hit finish. A query dialog screen will invoke showing your default table and a query editor screen. This is the money location....
From the menu click on "SQL" an SQL statement window will pop up. Now if you made to here then you can pretty much do everything you need to. Clear out the existing select statement, replace it with whatever select you would like.
Here is an example if you have access to pubs database and you did not drop the authors table.
Select * from pubs.dbo.authors (ensure you specify the database and the schema)
then hit execute.
Voila data is returned
1. Yes you can have mulitple table joins
2. You can copy your code from query analyzer into this window
3. Yes you can use parameters.
4. Yes you can set parameters up in your excel spreadsheet and use these to return specific results from your query.
5. No you don't have to fully qualify your tablename.column names each time.
6. You are limited to 65536 rows of data being returned in any excel version earlier than 2007
7. Yes you can re-query your data by setting up conditions on the access query.
8. Yes you can use procedures... this is a bonus.
9. Yes you can access views.
10.. Want to learn some of these options follow my upcoming blogs
Monday, April 6, 2009
Excel JUMP in now
Vlookup explained.
A vlookup is checking a cell or range etc to see if it is exists somewhere else.
okay so you have an excel spreadsheet with a list of names. You have another spreadsheet with a list of names. Your boss has asked you to see if there are any names on the second list that already exist on the first list. If you do then mark them with an Indicator.
so type the following items into excel start in cell a1 and work your way down.
cell a1 - Employees
cell a2 - Jim Sandal
cell a3 - Brian Shoe
cell a4 - Karen Boot
cell a5 - Mark Slipper
Then in the same spreadsheet start in cell d1 and work your way down type in the following
cell d1 - Employees
cell d2 - Kim Sandal
cell d3 - Mark Slipper
cell d4 - Andy Shoe
cell d5 - Karen Boot
Now take the following formula and add it to cell b2
=vlookup(a2, D:D, 1, 0)
then hit enter
copy the same formula in cells b3, b4, and b5
What do you see, if it worked out right then you should see an #N/A in cells B2, b3 and in cells b4 and b5 you should see Mark Slipper and Karen Boot respectively.
So vlookup explained:
=vlookup - vertical lookup
a2 - look at the value in cell a2)
D:D - check to find a match in any row in column D
1 - if found return the value in the row that matched in column D
0 - if not found return an #N/A
Cool tid-bit 1
If you had put a value of 1 instead of a zero this would of returned a value for all records.. why because 1 indicates a like match not an abosolute match like the zero does. Do not make this mistake many have.
Cool tid-bit 2
if instead of D:D you specified D1 : D30, this might not get you the values you want, you may find that you have a match in your first list to your second list when you are eyeing it over you notice #N/A values. That is because when you specifiy a specific range, it goes through it like a list starting at the top and going to the bottom. So if it has already gone by one of your values in the range, it will not go back to the top and start again. if you specify the D:D it will continue to keep looping so to speak to see if any value matches.
Cool tid-bit 3
I have multiple columns in my second list and when I do find a match i want to return a value from that list to my first list. (ie. outstanding balances in a new month)
.... more to follow..
A vlookup is checking a cell or range etc to see if it is exists somewhere else.
okay so you have an excel spreadsheet with a list of names. You have another spreadsheet with a list of names. Your boss has asked you to see if there are any names on the second list that already exist on the first list. If you do then mark them with an Indicator.
so type the following items into excel start in cell a1 and work your way down.
cell a1 - Employees
cell a2 - Jim Sandal
cell a3 - Brian Shoe
cell a4 - Karen Boot
cell a5 - Mark Slipper
Then in the same spreadsheet start in cell d1 and work your way down type in the following
cell d1 - Employees
cell d2 - Kim Sandal
cell d3 - Mark Slipper
cell d4 - Andy Shoe
cell d5 - Karen Boot
Now take the following formula and add it to cell b2
=vlookup(a2, D:D, 1, 0)
then hit enter
copy the same formula in cells b3, b4, and b5
What do you see, if it worked out right then you should see an #N/A in cells B2, b3 and in cells b4 and b5 you should see Mark Slipper and Karen Boot respectively.
So vlookup explained:
=vlookup - vertical lookup
a2 - look at the value in cell a2)
D:D - check to find a match in any row in column D
1 - if found return the value in the row that matched in column D
0 - if not found return an #N/A
Cool tid-bit 1
If you had put a value of 1 instead of a zero this would of returned a value for all records.. why because 1 indicates a like match not an abosolute match like the zero does. Do not make this mistake many have.
Cool tid-bit 2
if instead of D:D you specified D1 : D30, this might not get you the values you want, you may find that you have a match in your first list to your second list when you are eyeing it over you notice #N/A values. That is because when you specifiy a specific range, it goes through it like a list starting at the top and going to the bottom. So if it has already gone by one of your values in the range, it will not go back to the top and start again. if you specify the D:D it will continue to keep looping so to speak to see if any value matches.
Cool tid-bit 3
I have multiple columns in my second list and when I do find a match i want to return a value from that list to my first list. (ie. outstanding balances in a new month)
.... more to follow..
Subscribe to:
Posts (Atom)
