Excel Lists

[techwizard]

been playing around with some more advanced features in excel recently, came across pivot tables and charts. anyone else have any nifty excel tricks up their sleeves?

[img width=700 height=446]https://scontent-sea1-1.xx.fbcdn.net/hphotos-xta1/t31.0-8/11289078_903394469727067_6865477878225717641_o.jpg[/img]

[techwizard]

just found out about entering data in via forms, that's extremely useful instead of adding a blank line and entering each one that way

edit: to do this hit the drop down arrow next to the quick access bar at the top, hit more commands. then choose All Commands, find Form, hit Add. now you can just click that Form button and press "new" every time you want to add a new game entry and close when you finish. saves from having to scroll all the way down and add a new blank line (if you have formatting that needs copied). you can also search for any entry too by using the "criteria" button of the form window and typing in something to search in one of the boxes.

[techwizard]

you can sort while ignoring spaces and punctuation...wish i knew that years ago.

edit: to do this, first make your data all part of a table if it isn't already. add a new column anywhere to your sheet and use the code below to grab the data from your game title column (if that's what you sort by) and remove the spaces/punctuation. adjust the column letter to match what column you want to grab data from.

Code:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),",",""),".",""),"!",""),"'",""),"&","and")

this specific example will grab the data from cell A2 and remove spaces, commas, periods, exclamation points, apostrophes, and replace ampersand with "and". if you've done this in the first row of a blank column inside the same table as the rest of your data, it should automatically fill in the formula to every other row below including any new rows you make. it'll only do this automatically while in a table as far as i know.

this won't change anything in the actual game title column, only in the new column. now to make the new column hidden. first mark all other columns as unlocked by right-clicking the column letter at the top, go format cells, protection tab, uncheck the "locked" box and hit ok. then do the same for the new column but also mark it as hidden. now select the column with the formulas and next go to the home tab of the toolbar and in the "Cells" category go Format - hide & unhide - hide columns.

lastly, go to the data tab and hit Sort, then choose your hidden column from the drop down box and hit ok. if you'd like to sort in more detail, say by system first and then title, you can add an extra layer to do that. you should now see the list sorted by title but with spaces and punctuation ignored!

[Duke.Togo]

I've taught Excel for many years, but most folks don't get past the basics. Congrats on digging in and finding all the goodies.

[GamerNick]

I use excel too I should try this I think it would be cool!  I use excel for business presentations but that's easy data entry.  This would just look cool and me fun to do.

[techwizard]

I've taught Excel for many years, but most folks don't get past the basics. Congrats on digging in and finding all the goodies.

cool! i just dived into userforms with VBA, this should get interesting.

[subassy]

I've been using excel for a few random things for a long time too and I think I'm about ready to try and figure what exactly pivot tables are. Seems like something that would seem incredibly easy once I saw the use and how to set it up.

Of course for something like a game collection it seems like a real database like Access would be better. Then there's actual SQL queries and and all kinds of fun stuff to add in. And it's actually made to act like a program. It may as well be Visual Basic. Could even add in cart scans as part of the data. Then use some OLE stuff to pump it back into excel for charts and graphs. I don't know if I'll ever get around do doing that kind of ridiculous stuff. I have had a few reasons to really learn Access though.

[techwizard]

I've been using excel for a few random things for a long time too and I think I'm about ready to try and figure what exactly pivot tables are. Seems like something that would seem incredibly easy once I saw the use and how to set it up.

Of course for something like a game collection it seems like a real database like Access would be better. Then there's actual SQL queries and and all kinds of fun stuff to add in. And it's actually made to act like a program. It may as well be Visual Basic. Could even add in cart scans as part of the data. Then use some OLE stuff to pump it back into excel for charts and graphs. I don't know if I'll ever get around do doing that kind of ridiculous stuff. I have had a few reasons to really learn Access though.

you can do some of that in Excel too, like the cart scans. i tried access at one point but the only thing i used it for was filtering my list. then i found out if you make all your excel data into an actual table you can do a lot more with it, like enable filters.

[Duke.Togo]

At that point you might just want to move up to Access. Very user friendly database software.

[subassy]

you can do some of that in Excel too, like the cart scans. i tried access at one point but the only thing i used it for was filtering my list. then i found out if you make all your excel data into an actual table you can do a lot more with it, like enable filters.

I know, you're just using excel as a database instead of using a database for your database. Excel isn't necessarily designed for querying data while Access is designed specifically ONLY for that. I'm saying this knowing myself I should be doing this. Actually I'm using google docs spreadsheets so it's even worse than excel. 

By the way, not to detract from your accomplishment as you're more advanced than I am. Just saying you're...using a screw driver as a crowbar... 

[A8scooter]

If u add on excel like that does it move all columns down one cause mine look kinda like
Model #   title         cart  manual box  pic  text  77 rlease
Cx 2601  combat    x         x          x       x       x      x  .
Cx 2602. A s battle x          x        x       x       x   
Cx2604  space war  x         x       x       x


Would cx2603 star ship fit right in with ur add feature without me making a new line the old way? And would the columns all move over the same way too downward?

[techwizard]

which add feature, sorry? the one about entering data via forms? that will put all the data in a new line at the bottom and then you would have to use data sorting to get it where you want it. not sure what else you might be referring to.

[A8scooter]

I think that's what I'm referring to.  It was the add feature and it was suppose to make a new line for your excel  but didn't know where the line was placed  (the bottom of the page seas where I was wondering or if it auto sorted to the right spot)

[Shadow Kisuragi]

You can always sort using the built-in Excel sort, or you can define your own sorting preferences if you want.

[techwizard]

tidied up my excel list considerably. i seperated dates (date added to list, date completed, and date last played) to a seperate sheet in the same workbook. also added my digital games and hardware bundle lists as seperate sheets in my main workbook so everything is together now. went back to playing with pivot tables, now using date completed data. took a couple screenshots but not shown is i can check how many games i've beaten in any month, or how many on a specific day of each month.

https://scontent-sea1-1.x...3592011943329476869_o.jpg

https://scontent-sea1-1.x...6418290614037058622_o.jpg