Identifying Your Purpose for Using Excel

Hello, my name is Jennifer (aka @DataDiva) and I’m an Excel addict. Thank you for your concern and I’d like to say I’ve been clean for *checks file history* two hours but as soon as I finish this blog post, I’m going to use Excel to organize and sort a list of teacher emails. Excel is one of the greatest tools available to educators and I’m thrilled to see this blog exploring all the ways it can be used. In addition to the great resources that Science Goddess is offering, I strongly recommend installing a tool called ASAP Utilities.  It’s free and fantastic. Think of it like hundreds of macros designed to make your Excel work easier. I promise it will save you hours of time.

Developing your skills with Excel is very much a form of literacy. And like an emergent reader, one of the skills you’ll need to become comfortable is identifying your purpose for using Excel. When we work with young readers, we generally provide three reasons why an author has created a text: to entertain, inform, or persuade. Once they have a handle on how to recognize an author’s purpose, it can make it easier for the reader to engage with a text or know what to trust or what to question. In order to keep this at blog- and not chapter-length, I’m going to touch on a few issues you should think about as you open Excel and start your data work. Although an educator can use Excel to answer an infinite number of questions or organize a plethora of data, generally speaking, there are three basic purposes for using Excel.

Purpose	What the mindset of this purpose might look like
1.       To organize and analyze information	“I am going to use Excel to organize and analyze that I already have. I want to make charts and graphs and may need to do some calculations.”
2.       To share information with others	“I am going to share raw data with other people. It’s important to me (and them) how the information is presented and formatted.”
3.       To collect evidence from others	“I need people to give me information in a certain way. I will then organize and analyze the data after I collect it.”

If your purpose is to organize and analyze information, don’t worry about formatting.

click to enlarge

Your data don’t have to look pretty or appealing. Use color options provided through conditional formatting to indicate patterns in your data, rather than decoration. Focus instead of keeping your data clean and logically arranged. Don’t spend your time centering and bolding and rather invest your energy in taking advantage of all of the tools Excel offers. Label your columns in row A so you can access the plethora of filter and sort options. If you’re working with people’s names, combine them in one cell (Yup, ASAP Utilities can do that for you) unless you’re doing a mail merge where you need the first name to be a separate field. Generally speaking, you want to focus on keeping your data simple, clean, and accurate. This means formatting numbers as numbers, text as text, etc.  I prefer to start with charts and graphs on the same page as my data and then once I’m happy with the layout, move them to their own sheet.

Sharing raw data is different than sharing a chart or display. Consider the items in the Data Display Checklist when sharing data visually. If you’re sharing numbers, take advantage of linking cells. The display below contains the same data as the previous image, except they’ve been formatted to share with a teacher. Note that the actual numbers are linked back to my original data sheet. (I set the options for Excel to show formulas rather than data in the example below)

click to enlarge

If you need to collect information from others and are using Excel as the means to collect the data, linking cells can be a life saver. A good rule of thumb is to separate your data entry from your data analysis whenever possible. It can be incredibly frustrating to find that someone has typed over your carefully constructed formulas. It’s worth it to explore setting up drop-down option boxes or locking or hiding cells.

The most important take away is that spreadsheets will look different, depending on their purpose. You can quickly and easily copy sheets between books or indicate your purpose in your file name (such as English data_analysis.xls versus English data_collection.xls). While there’s no wrong way to use Excel recognizing and identifying your purpose when you begin can save a headache down the road.

Many thanks to Jennifer for supplying our first guest post here at Excel for Educators.  She describes herself as a "Defender of Quality Rubrics. Advocate for learner-centered ed & informed use of data - at same time. More geek than diva, fan of alliteration. Straight ally." You can follow her on Twitter or visit her Quality Rubrics wiki.

I hope Jennifer will be back to share more ideas. Do you have something to post here? Let me know.