There are some technical considerations to think about long term. For instance, always consider the types of data you are working with (go to 2/1 lesson plan for more on that).
Because I can’t expect everyone to have any experience with statistics or with programming, I decided the simplest thing would be to work with software you all had access to and to require that you all work with one (common) file type that data are often managed and analyzed on: comma separated values (csv).
CSV files work really well in Excel, a program you all have access to. If you do not already have Excel downloaded to your device, go to the Baruch Computing and Technology Center’s (BCTC) page for free downloads for students. Once there, you can download the Microsoft Office package that includes Excel.
I recommend Excel instead of just using Google Sheets for two reasons:
- there are more formulas you can use in Excel compared to Sheets.
- You should be saving many, many different versions of your files, which is much more intuitive to do in Excel compared to operating Sheets from the browser.
Basic Analysis in Excel
In future lessons and learning modules, we will talk about cleaning data, which will be very important in doing analysis. However, I thought it might be useful to give you a few basic analysis features in Excel
There’s a lot of ways to analyze data in Excel–see the resource here on different functions in Excel for statistical analysis. Some important ones to keep in mind, especially for visualization purposes:
- To add up numerical data in a column: =SUM(cell1:cell_end_of_range)
- To take the mean: =AVERAGE(cell1:cell_end_of_range)
- To take the median: =MEDIAN(cell1:cell_end_of_range)
- To count specific items in a column: =COUNTIF (cell1:cell_end_of_range, number or “text”)
- Same as COUNTIF but for multiple criteria: =COUNTIFS(cell1:cell_end_of_range, “criterion 1,” cell1:cell_end_of_range, “criterion 2)
The COUNTIF and COUNTIFS functions are especially helpful for turning categorical data into discrete data (i.e., data made up of words into counts of those words). This will be important if you wanted to make a bar graph, for instance.
A lot of this is easier with other proprietary software like Stata or SPSS. That stuff costs money, though, eventually (free for now at Pitt, at least Stata is). Programming languages like Python and R are free, so that’s part of the reason why I will show you how to use programs I wrote (and encourage you to learn this stuff at some point in you future if you don’t already know some of it).
Below are some steps for working with this program.
Basic Visualization in Excel
Seeing visuals of your data early on can be really helpful to give you a broad idea of the shape of your data–where things cluster, how varied your data are.
Some of the more basic visualizations are just easier to manipulate in Word and Excel compared to stuff in Python I will show you later. You can do bar graphs, pie charts, and line graphs easier in Excel–see this resource. You can also do scatter plots and histograms, but I think it is a little easier in the Python program we were will be working from that I’ll show you later.
In Word, you are limited to making tables–but tables can be very effective visuals.
Other programs that just generally do stuff with images can also be useful: PowerPoint, InDesign, Photoshop, etc. Something to mess around with, especially as you get toward the end of the semester. I’ll also show you how to use Tableau toward the end of the semester.
Going Forward
CSV files can be easily handled by the programs I’m going to share with you in the coming weeks to do more advanced analysis and visualization.
As you look for data, please only work with files that are already .csv files, can be easily turned into such files (you may need help from me on that–this usually won’t work), or you are operating from a database on a website and never work with any files at all.
One final caveat: if you have some experience working with data already, please do whatever you like. If you are comfortable working with JSON or like to work with statistical analysis programs you already know like SPSS or SAS or whatever, go for it. Same goes for the level of statistics you might use. If you know how to do a multiple regression, go for it. I just won’t expect anyone to do anything beyond manipulating csv files in Excel and in the programs I write or do any sort of statistical analysis beyond descriptive statistics.
Task
In a comment below, choose one of the following options:
- Try out one of the Excel formulas on a data set you selected that you shared on Discord and tell me what you find (make sure you give a little background on what the data set is)
- Talk about experience you have in data management or analysis that you think could be helpful this semester
- Ask any questions or share any reservations you have about doing this kind of work.
After commenting, click on the button below to continue:
In the past I have worked with R for a statistics class. That was probably my first time working closely with programming. At first, I felt it was a bit confusing but I think working with datasets becomes easier when you do more over time. I also felt that showing the data in a graph was more helpful to understand it better overall. I think my experience with R will be helpful for me to understand and learn new programs for working on different kind of data I may choose to work on in the future.
When it comes to Excel, I’m clueless Just looking at the logo overwhelms me sometimes. I use it for work but very very very limited just because I have the tendency of pressing one thing and then all of the sudden everything goes missing or the formula gets messed up. I remember I took a CIS class and there was some sort of software we were using but I was lost the whole semester so I don’t even remember the name of that software. I feel like if I have good and precise tutorial then I won’t have a problem.
Excel seems to be my close friend recently. I have worked on Excel completing projects as classes’ requirements through semesters. Baruch requires students to earn 5% of class’s final grade working on such a learning program called “SAM” (some of you guys may know about it) in certain classes, like classes I took in the past FIN 3000, CIS 2200, and MGT 3121. In my opinion, I think creating PivotTable and using COUNTIFS function would be very helpful as they summarize the story and gather informations or data together, much helpful when you can sort information on PivotTable help making everything clear.
I am very familiar with Microsoft excel and have also used Microsoft access at times. I have had to use Microsoft excel for various classes, and use it all the time at work.
As far as other softwares go for manipulating data, I have no experience.
I use Excel daily for work at my office but I do not really have to use too many functions. I am a beginner when it comes to software functions I usually google if I need to do something for excel or try to figure it out myself but I think I might need extra help with this and more clarification. Maybe next class we can speak about this and also as you mentioned it could be really beneficial to have this knowledge and free resources to learn out there so any additional links would be appreciated!
I took an AIS class with data analytics. We use pivot tables in excel, and also I think that will be helpful in this project because we can be able to group and summarize large amounts of data that will be easier to report and analyze. Did descriptive statistics and regressional analysis. I also had a little experience with tableau, but just essential. We use it to do forecasting. Never use python before, so I am looking forward to learning it.
Excel is like one of my “best friends” during this semester because I almost all my classes require me to use excel. Also, I work at an accounting firm, so I use excel almost everyday. I am pretty comfortable with Excel, but don’t know every formula, but I do know the basics. I am comfortable with making graphs and pie charts on Excel as I learned that last semester. Also, I am learning many new tricks, so if anyone needs any help I am happy to help!
So I don’t have that much experience with excel but for some reason three of my I have to use some sort of graph and have to interpret data. I also accepted a job that will use graphs to produce results. I’m not sure I like it though cause I’m using a mac and I don’t like the trackpad.
I’ve been using excel since high school and in college I use it in so many of my courses. At the financial institution that I an interning at, we use excel pretty much every single day. I get sent spreadsheets where my boss will tell me the information she wants out of it and I will clean it up for her and send it back. I used to tutor a CIS course at the school I transferred from that covered Word, Access, and Excel, so I am pretty comfortable in excel and I know quite a few functions. I took a statistics course, so I am familiar a few concepts relating to that.
I have some experience with excel. I’ve been using it since senior year in high school. I’ve been using it a lot for my classes too. I know some formulas but not all though. I know how to create pivot charts, graphs etc. It’s like each semester, there’s at least 2 classes that requires me to use excel so now i’m pretty use to it.
I worked before with excel, I am not an expert but I kind of know where to look for the formulas, other tools, and how to manage the excel sheet. Throughout high school I did many projects, and we used excel as our main programming; in my personal life, I use excel as a format to track my expenses, earns, and saving money. Also, I just used excel on my last semester because I took psy stats, and we used it a lot to make the data graphs, and some calculation using the excel formulas.
Excel has been a key skill in many of my classes. When it comes to organizing data, finding relationships within data, performing calculations within data sets and displaying data in the form of graphs or charts, it is a very useful software. I have an intermediate level ability with it. Some of the more complex data management tools in excel, while I don’t know second hand, I do have access to step by step guides and have some prior experience. While an employee of a major bank, I was given assignments using excel.
On experience I had involving data that I think is relevant to the course was last year when I made a bunch of graphs using data I collected for a presentation. Basically, in professional sports it is assumed that the more a team spends on salary, the better they seem to do. But since I never had seen data to back it up, I decided to compile it myself for that presentation. The results varied for each sport but for the most part, the general assumption seemed to be true.
I am not very familiar with excel and haven’t used it for schoolwork but I am excited to learn since I know its commonly used. At work most of my colleagues use excel spreadsheets and express how useful it is so I’m happy to explore excel and put it to use during this class and strengthen those skills.
I’ve used excel before for school projects but I was still lost and needed guidance. I know its very commonly used and if the whole class can help each other that would be great. I want to get used to excel more since it is used in a lot of workspaces and it is a good skill to pick up. I look forward to anyone that is willing to help out with this!
I am very familiar with Excel because I’ve been using it since 6th grade and during my college years I’ve had many classes like Accounting, Management, Finance and Statistics that required using Excel for most of the assignments. Excel is a very useful software for budgeting, tracking data and creating charts. During this whole time using Excel I can say that I’m pretty used to it and I can work pretty comfortably.
I personally do not feel fully comfortable working with Excel, I always found it confusing however because I know it is one of the essential tools and crucial when it comes to working with any data I think I will benefit from going more in depth with it and expanding my knowledge by practicing and learning more about it. Although I have an experience with exploring all its features through courses like Finance, Accounting and Management I believe by conducting research on the topics that would truly interest me rather than doing it just for SAM I can actually develop curiosity which will help me to ace my skills.
I have used excel for my previous classes like accounting and management. Initially, I was amazed to see, how we can retain enormous amount of data and being able to sort to specific data. I know the basic knowledge for the program, but it’s been a year already so I have to go back to drawing board. It would be a good opportunity for me to use, excel for this class and use the data for my research paper.
I’ve been using excel for the past few years so I have a good working knowledge of the program although I’m not very familiar with all formulas and advanced excel techniques like VLookup and Pivot tables.
I have used Excel in my previous economics and accounting classes. It’s amazing what it can do. Excel can provide very convenient functions in data analysis. It quickly transforms tabular data into graphs to improve visibility. Because not everyone is sensitive to changes in numbers. On the other hand, easy customizability allows you to change the data to your favorite style.
Excel is a really useful tool. However, we are not best friends. I have learned to use excel in my two CIS classes and I’ve learned all the basics and a little more. But when it comes deeper management of information I am not really good at it. I am familiar with some formulas but not all.
I’ve taken many classes that prioritize excel and honestly it’s been a life saving application with certain courses. Courses such as statistics have honestly brutalized my existence but excel makes it way less painful. It’s a very useful application and I’ve used it pretty thoroughly although I somehow still feel lost as there’s so many different formulas and ways to use the application. Overall, I really enjoy using the application when it comes to finance and statistics courses. Last semester, I took a statistics economics course and I studied a data set on beer consumption at a university in Sao Paulo, and used linear regression to understand the data set and what it all meant. Excel is for sure a very useful application to use in understanding data and organizing it in a thorough manner.