Tracking small bits of information without Excel
August 2, 2015 5:05 PM Subscribe
I'd love to find a solution for quickly building lists, tracking information and exporting the info into an attractive report without using Excel. The program needs to be installed on my computer (not an online solution like Google docs). Does anything like this exist?
My new job entails tracking a lot of small bit of information--RSVPs, committees, member lists, department lists, paperwork, etc. The person who had my job previously tracked all of this information on Excel and, when it was time to print a report of the data, he would reformat the spreadsheet to look readable and attractive.
For instance, there are many different spreadsheets of members with addresses, emails and phone numbers. When my supervisor asks for one of the member lists, I have to either go into the spreadsheet and format it to look more readable or cut and paste it into another preformatted Excel spreadsheet. Or everyone in the department needs to turn in their bio and I need to track that everyone has done that so I need to build a quick checklist with everyone's name and a yes or no.
Anyway, I'm not very comfortable working in Excel and I just find it annoying. Here's what I'm looking for:
* There are some checklists that I need to regularly generate with the same 120 members. I want something that will automatically generate a checklist with those same 120 members in one field, a space to check yes or no, and a field for notes and information.
* I need to generate reports with names, emails, phone numbers and and easily move them around into different categories if I need to. In other words, if Bob Smith was on the red team, and now has moved to the blue team, I want to be able to move him easily.
* If possible, I'd like to print a report that does not look like a spreadsheet, keeps information in columns, and looks nice.
* All of this cannot be a web based program (like Google docs, etc)
I realize as I type this that I may just need to get more comfortable in Excel and quit trying to reinvent the wheel, but if anyone has a suggestion, I'd love to know!
My new job entails tracking a lot of small bit of information--RSVPs, committees, member lists, department lists, paperwork, etc. The person who had my job previously tracked all of this information on Excel and, when it was time to print a report of the data, he would reformat the spreadsheet to look readable and attractive.
For instance, there are many different spreadsheets of members with addresses, emails and phone numbers. When my supervisor asks for one of the member lists, I have to either go into the spreadsheet and format it to look more readable or cut and paste it into another preformatted Excel spreadsheet. Or everyone in the department needs to turn in their bio and I need to track that everyone has done that so I need to build a quick checklist with everyone's name and a yes or no.
Anyway, I'm not very comfortable working in Excel and I just find it annoying. Here's what I'm looking for:
* There are some checklists that I need to regularly generate with the same 120 members. I want something that will automatically generate a checklist with those same 120 members in one field, a space to check yes or no, and a field for notes and information.
* I need to generate reports with names, emails, phone numbers and and easily move them around into different categories if I need to. In other words, if Bob Smith was on the red team, and now has moved to the blue team, I want to be able to move him easily.
* If possible, I'd like to print a report that does not look like a spreadsheet, keeps information in columns, and looks nice.
* All of this cannot be a web based program (like Google docs, etc)
I realize as I type this that I may just need to get more comfortable in Excel and quit trying to reinvent the wheel, but if anyone has a suggestion, I'd love to know!
Any CRM system will do all of that. However, CRM has moved online so I'm not sure what options are still out there on the desktop.
posted by COD at 5:34 PM on August 2, 2015
posted by COD at 5:34 PM on August 2, 2015
Could you work with Excel and Word's mail merge features?
Caveat: my last office job was a decade ago.
posted by Thella at 6:03 PM on August 2, 2015
Caveat: my last office job was a decade ago.
posted by Thella at 6:03 PM on August 2, 2015
Honestly, it's going to be easier to drink the Excel koolaid.
posted by chesty_a_arthur at 6:12 PM on August 2, 2015 [2 favorites]
posted by chesty_a_arthur at 6:12 PM on August 2, 2015 [2 favorites]
Sounds like summary reporting - Excel is very good at it and there are dozens of different sets of directions found with a quick search.
First things first, however, is to make these data sets your own. You do not have to do what your predecessor did because it's obvious they didn't know how to aggregate the various spreadsheets you're office has accumulated.
Spreadsheet kool-aid sounds dangerous but it is actually very easy when you're working with your own data. So make the data your own, build an aggregate spreadsheet that references all of the other spreadsheets into one coherent whole and away you go.
posted by ptm at 6:18 PM on August 2, 2015 [2 favorites]
First things first, however, is to make these data sets your own. You do not have to do what your predecessor did because it's obvious they didn't know how to aggregate the various spreadsheets you're office has accumulated.
Spreadsheet kool-aid sounds dangerous but it is actually very easy when you're working with your own data. So make the data your own, build an aggregate spreadsheet that references all of the other spreadsheets into one coherent whole and away you go.
posted by ptm at 6:18 PM on August 2, 2015 [2 favorites]
Any program that you can customize that much will require you to learn _how_ to customize it to be the way you want it for each different function. So, you'll have to spend time learning something -- until MS MindReader comes out, of course.
posted by amtho at 6:38 PM on August 2, 2015 [1 favorite]
posted by amtho at 6:38 PM on August 2, 2015 [1 favorite]
You could go with expensive Constituent Record Management (CRM) software.
But they are just fancy databases - and while they are great if you have 10k people to keep track of, you can do the same with MS Access for cheaper, and with more ultimate control over the data. (CRM software sometimes likes to be "helpful" by doing things like hiding the UID. Yeah, super helpful).
If you're not familiar with relational databases, you'll want to take some training. You can stumble through yourself, but it takes so much longer -- a four day workshop taught me more about database design than I could have figured out in months of playing (and that was a slow workshop - we could have done it in two days).
Access is best if you want to store data and bring up one person at a time, or do relational work (like linking a table of people to a table of sales, make reports, etc). Using Excel summary sheets may be easier if what you are doing are just trying to summarize stuff (as noted above). For my research, I've actually gone back & forth: data entry via excel, relational connections in access, then export back to excel for summary analyses.
posted by jb at 6:53 PM on August 2, 2015
But they are just fancy databases - and while they are great if you have 10k people to keep track of, you can do the same with MS Access for cheaper, and with more ultimate control over the data. (CRM software sometimes likes to be "helpful" by doing things like hiding the UID. Yeah, super helpful).
If you're not familiar with relational databases, you'll want to take some training. You can stumble through yourself, but it takes so much longer -- a four day workshop taught me more about database design than I could have figured out in months of playing (and that was a slow workshop - we could have done it in two days).
Access is best if you want to store data and bring up one person at a time, or do relational work (like linking a table of people to a table of sales, make reports, etc). Using Excel summary sheets may be easier if what you are doing are just trying to summarize stuff (as noted above). For my research, I've actually gone back & forth: data entry via excel, relational connections in access, then export back to excel for summary analyses.
posted by jb at 6:53 PM on August 2, 2015
You shouldn't use Excel for this.
I mean, you can use Excel, but you shouldn't. All the fiddly things that are hard to do are because Excel is not a database, but people like to use it like a database.
If you've got a bunch of data which relates to each other and you need to mess about with combining people, places things in different ways what you need to use is a relational database. In the long run it will make your job easier and make you look like a genius information manager type person.
Want a list of who is in what department and attended conference A and B but not C?
No problem, 30 seconds work in a Relational Database, but about an hour in excel.
Want to move bob from the red team to the blue team?
Go to your TeamMembers table. It has two columns, Person ID and Team ID, which reference two other tables: people and teams.
Build automatic reports which look however you want and are automatically produced whenever you need them.
The bad news is the words "long run".
If you don't know databases it is going to be a total pain to get up and running. There is a learning curve, but I guarantee it will be worth it.
If you've not done anything with databases or Access before then I would second the advice to go find a course to go on. It's not monstrously hard, but it takes a bit of time to get the mindset of chopping up your data the right way and slotting it back together.
Here is a fun online tutorial about SQL, which is heavier than you need (SQL (Structured Query Language, usually pronounced like Sequel) is the language that most databases use to take instruction about what to do with your data. Access uses SQL, but tries it's best to hide it from you and does it all through little wizards, but I'm duty bound to recommend GalaxQL in all database discussions because I think it's neat and it was my only source of SQL learning like ever, and I am currently a professional databasey sort of person)
posted by Just this guy, y'know at 2:18 AM on August 3, 2015 [3 favorites]
I mean, you can use Excel, but you shouldn't. All the fiddly things that are hard to do are because Excel is not a database, but people like to use it like a database.
If you've got a bunch of data which relates to each other and you need to mess about with combining people, places things in different ways what you need to use is a relational database. In the long run it will make your job easier and make you look like a genius information manager type person.
Want a list of who is in what department and attended conference A and B but not C?
No problem, 30 seconds work in a Relational Database, but about an hour in excel.
Want to move bob from the red team to the blue team?
Go to your TeamMembers table. It has two columns, Person ID and Team ID, which reference two other tables: people and teams.
Build automatic reports which look however you want and are automatically produced whenever you need them.
The bad news is the words "long run".
If you don't know databases it is going to be a total pain to get up and running. There is a learning curve, but I guarantee it will be worth it.
If you've not done anything with databases or Access before then I would second the advice to go find a course to go on. It's not monstrously hard, but it takes a bit of time to get the mindset of chopping up your data the right way and slotting it back together.
Here is a fun online tutorial about SQL, which is heavier than you need (SQL (Structured Query Language, usually pronounced like Sequel) is the language that most databases use to take instruction about what to do with your data. Access uses SQL, but tries it's best to hide it from you and does it all through little wizards, but I'm duty bound to recommend GalaxQL in all database discussions because I think it's neat and it was my only source of SQL learning like ever, and I am currently a professional databasey sort of person)
posted by Just this guy, y'know at 2:18 AM on August 3, 2015 [3 favorites]
does not look like a spreadsheet, keeps information in columns, and looks nice
If you want columns and rows, that is going to look like a spreadsheet. Perhaps you don't want the gridlines printed, so you can turn them off in the Print Preview area.
Bob Smith was on the red team, and now has moved to the blue team, I want to be able to move him easily.
In Excel this is just finding his row and typing in the cell "Blue". To find him, the best way is to use a filter. In fact, filters are the first thing you should learn.
format it to look more readable
If this is done regularly, why do you have to keep reformatting? If you have fixed the formatting once, then it should stay nicely formatted unless you make major changes.
many different spreadsheets of members
If the same member is in a bunch of different spreadsheets, then you have a crude version of a database already. Once you are comfortable with Excel, Access is the next step. It is powerful when you know how to use it but frustrating until you do. I was lucky that I got to attend some classes, but there are lots of tutorials and books available for all Office products. My community ed office has some classes for Excel.
posted by soelo at 7:37 AM on August 3, 2015
If you want columns and rows, that is going to look like a spreadsheet. Perhaps you don't want the gridlines printed, so you can turn them off in the Print Preview area.
Bob Smith was on the red team, and now has moved to the blue team, I want to be able to move him easily.
In Excel this is just finding his row and typing in the cell "Blue". To find him, the best way is to use a filter. In fact, filters are the first thing you should learn.
format it to look more readable
If this is done regularly, why do you have to keep reformatting? If you have fixed the formatting once, then it should stay nicely formatted unless you make major changes.
many different spreadsheets of members
If the same member is in a bunch of different spreadsheets, then you have a crude version of a database already. Once you are comfortable with Excel, Access is the next step. It is powerful when you know how to use it but frustrating until you do. I was lucky that I got to attend some classes, but there are lots of tutorials and books available for all Office products. My community ed office has some classes for Excel.
posted by soelo at 7:37 AM on August 3, 2015
Filemaker Pro is a good user-friendly alternative made by an Apple subsidiary. Excel is not without shortcomings though, and there have been numerous attempts to supplant it. Wikipedia has a long list of other alternatives that have made it to market.
Getting comfortable with Excel is a good idea regardless as it still runs much of the world, for better or worse. The other point of consideration is any software more modern and 'hip' is going to be some online cloud-based contraptions because that's how new software is these days (eg using Google Forms to make your checklist), so you'll have to challenge your not-online condition to take advantage of the latest and greatest.
Want a list of who is in what department and attended conference A and B but not C?
No problem, 30 seconds work in a Relational Database, but about an hour in excel.
Once you get really good with Excel formulas, this will take you 30 seconds (IF, COUNTIF, and a filter), but if you don't manage to 'get' SQL, you'll never figure out how to get that list out of the database. More importantly though, once you've figured out how to get Excel to do it the first time, you can just copy and paste your formula (and filter) the next time you need it.
posted by fragmede at 9:40 AM on August 3, 2015
Getting comfortable with Excel is a good idea regardless as it still runs much of the world, for better or worse. The other point of consideration is any software more modern and 'hip' is going to be some online cloud-based contraptions because that's how new software is these days (eg using Google Forms to make your checklist), so you'll have to challenge your not-online condition to take advantage of the latest and greatest.
Want a list of who is in what department and attended conference A and B but not C?
No problem, 30 seconds work in a Relational Database, but about an hour in excel.
Once you get really good with Excel formulas, this will take you 30 seconds (IF, COUNTIF, and a filter), but if you don't manage to 'get' SQL, you'll never figure out how to get that list out of the database. More importantly though, once you've figured out how to get Excel to do it the first time, you can just copy and paste your formula (and filter) the next time you need it.
posted by fragmede at 9:40 AM on August 3, 2015
For generating reports, I would use Mail Merge with Word and Excel, given the scale of what you're talking about.
As an example of what I' talking about, I'm a college professor, and I keep my grades in a spreadsheet. I print out individual "grade reports" for my students using Mail Merge. The idea is that my spreadsheet has rows for each student and columns for their grade information. Then in the report I generate, it makes a page for each row, and I can type a combination of text (which you just type in Word) and the data from the spreadsheet, which you stick in as a "field" thing in the mail merge.
So it looks something like
Grade report for Jane Student
Exam 1: Exam1Grade / 45
Exam 2: Exam2Grade / 90
Current Course Grade: CurrentLetter
Where the normal styled stuff is what I typed, and the bold placeholders are from the mail merge. And then when you do the mail merge, the right data is filled in for each student.
It might take some careful thinking and spreadsheet manipulation to make the right kind of reports (e.g., you want one page with lots of data, instead of multiple pages with little data), but it might be effective at least for some of the reports you need to generate. And I think you can keep the mail merge files pre-generation if you need to run the reports multiple times.
posted by leahwrenn at 10:34 AM on August 3, 2015
As an example of what I' talking about, I'm a college professor, and I keep my grades in a spreadsheet. I print out individual "grade reports" for my students using Mail Merge. The idea is that my spreadsheet has rows for each student and columns for their grade information. Then in the report I generate, it makes a page for each row, and I can type a combination of text (which you just type in Word) and the data from the spreadsheet, which you stick in as a "field" thing in the mail merge.
So it looks something like
Grade report for Jane Student
Exam 1: Exam1Grade / 45
Exam 2: Exam2Grade / 90
Current Course Grade: CurrentLetter
Where the normal styled stuff is what I typed, and the bold placeholders are from the mail merge. And then when you do the mail merge, the right data is filled in for each student.
It might take some careful thinking and spreadsheet manipulation to make the right kind of reports (e.g., you want one page with lots of data, instead of multiple pages with little data), but it might be effective at least for some of the reports you need to generate. And I think you can keep the mail merge files pre-generation if you need to run the reports multiple times.
posted by leahwrenn at 10:34 AM on August 3, 2015
I'm wondering if you need a friendly database builder. I've used Podio in this way. It can also create to do lists, though I haven't used the to-do lists extensively so I'm not sure how they are integrated into larger workflows.
posted by lillygog at 4:23 PM on August 4, 2015
posted by lillygog at 4:23 PM on August 4, 2015
Response by poster: Thanks everyone for your responses. I didn't mark any one answer as best because you are all right--I need a database like Access or Filemaker, but since I don't have time to set those up right now, I'm going to have to drink the Excel Kool-aid until they come out with MS MindReader. Thank you all!
posted by biscuits at 5:31 PM on August 4, 2015
posted by biscuits at 5:31 PM on August 4, 2015
This thread is closed to new comments.
Data storage and a reporting function.
People tend to not use it because it is more annoying than Excel if you don't understand how to use it.
posted by srboisvert at 5:13 PM on August 2, 2015 [3 favorites]