Making Excel Work For You

Big Data provides invaluable information which allows major companies and organizations to understand their existing and potential customers and to make more informed business decisions. But data doesn’t have to be big to be useful. As long as it’s accurate, relevant, and well organized, everyday information and data can help you make decisions if you know what you are trying to understand. This is probably the most important idea to remember when thinking about data – no matter how big or small the data you are working with, it’s important to identify what you want to do with it in order to learn from it. Are you tailoring a marketing message? Presenting findings on a report? Keeping track of an account? Once you know the purpose of your data, you can begin to organize it to tell the clearest story possible.

Design your data

Now that you are familiar with Excel and some key concepts in managing data, big or small, you can take your spreadsheet skills to the next level. As you begin to build in Excel or other spreadsheet software like Google Sheets, you will need to translate your thinking about how you want to use your data into the design of your spreadsheet. What information goes in a row? What do you place in a column? How do you organize, label or sort your data? How do you format your spreadsheet to make your data easy to follow and interpret?

Data truly does tell a story – a story about people, trends, behavior, risks and possibilities. This week we will focus on how to get the most out of your spreadsheets to help you tell the story you need them to.

Making Excel work for you

There’s an old adage that information is power. When it comes to massive data sets, what makes them powerful is the ability for readers to understand, trust, and interpret them. These same key ideas apply to how you design and manage your data in a spreadsheet. This week we’ll have a chance to spend more time with Microsoft Excel. You might find that Excel has a steeper learning curve than Microsoft Word. In other words, it might take a little longer to get comfortable with the terminology and the interface, because there are fewer obvious parallels to paper and pen. However, now that you have a handle on the terminology and have had some practice with Excel’s workbooks and worksheets, we can begin to think about how to use Excel’s feature to design your spreadsheets to help your data tell it’s story.

First, let’s check in on our problem solving process. Last week, you learned about implementing your plan. Let’s finish that step now.

The Problem-Solving Process

Wrapping Up Steps 3 And 4 – Create A Plan And Try Your Plan

Now you’re ready to put your plan into action. Whether you’re working on your own, as part of a group, or as a group leader, you need to be organized and reliable. Being organized will help you work more productively. As you determine your plan, make sure to include the risks and contingencies and develop options to handle these. You should also start to think about ways of getting feedback along the way to see if your solution is heading in the right direction. After additional practice in Excel this week, you will be turning in Assignment 3, in which you will create an Excel workbook to map out the steps and costs involved in your plan.

Designing your data

For any tool to be really useful it’s not enough to know how it works, you also have to know what to do with it, and in particular you have to know what you want to do with it. It’s the same with Excel. Half the challenge of creating an effective spreadsheet is thinking critically about what you need it to do. What are your rows? What are your columns? What information do they need to contain, and how do they intersect? Basically, what is the story you need your data to tell? Is it a calendar that needs to track deadlines? Is it a spreadsheet that someone like Deidre Jeffries would use to track buyers, pricing, and orders? Before we go into specific formatting options, here are a few tips and key ideas to think about before you begin creating your spreadsheet.

What is your spreadsheet documenting? In other words, what is it for? What are you trying to measure: Dates? Time? Money? Knowing this will help you make some key formatting decisions and create formulas early on so you don’t have to repeat them cell-by-cell.

How will it be used? Understanding the purpose of your spreadsheet will help you determine what data to include. Do you need to check the sheet daily to see moment-by-moment reporting or is it there to give you a high level overview? Think of a calendar – they can be organized by year, month, week, day, or even down to the hour. You choose which format to use depending on what you need to do. For example, if you are tracking major holidays, an annual calendar is fine. If you are tracking daily appointments, you need to focus in on each day. You have to apply the same kind of thinking when designing a spreadsheet.

Who will be using it? This can help you prioritize data. If a spreadsheet is for a clothing buyer, they will want to see pricing, shipping and delivery information. If it is for a clothing seller, they may need the same information, but might be more interested in the number of pieces sold and if/when payment was received.

Formatting

Here is one of the places your experience with Word will pay off. Many features in Excel operate in the same way as they do in Word, including:

• Typeface
• Font
• Font size
• Color
• Copy
• Paste
• Alignment

Now let’s explore a few features specific to Microsoft Excel. Excel offers tons of ways to customize workbooks and worksheets to suit your needs, which makes it a versatile personal and professional tool.

Formatting columns and rows: The width of columns and the height of rows have a default size. You can adjust the width and height of cells to fit your data. When you enter data that is longer than the width of the column, three things could happen:

1. If there is no text in the cells to the right of the active cell, the text will spill into those cells.
2. If there is existing text in the cells to the right, some of the text in the active cell will not be displayed. However, the content will still be in the cell (and visible in the formula bar), even if all of it is not visible in the active cell.
3. If the data in the cell is all numbers, pound signs (###) will display in the cell. The data will still be in the cell, but will not be visible except in the formula bar.

Don’t be alarmed if this happens. You can make adjustments to fit your data.

To adjust the size of columns, you have a few options:

1. Click and drag the right column border to make it wider or narrower.
2. Change the measurement of the column using the Format tool on the top ribbon. Column width is expressed in the number of characters the column can fit.
3. Select Autofit, which makes the width of the column wide enough to fit the longest entry.
4. Select Wrap Text. Wrap Text is an option that will keep the existing width of the column but extend the row height to fit your data. In other words, instead of the text expanding in width, the text will wrap underneath and the row will get taller to accommodate the text. This option is helpful when you have long data entries but do not want long columns that will make seeing or printing the rest of the worksheet difficult or unwieldy.

To adjust the size of rows:

1. You can click and drag the bottom row border to make it taller or shorter.
2. Change the measurement of the row using the Format tool on the top ribbon. Row height is expressed in point size (like font size).
3. Select Autofit, which makes the height of the row tall enough to fit the longest entry.

Inserting Columns or Rows: You can insert columns or rows at any location without having to manually move data by right-clicking on a cell, and selecting “Insert,” then “Entire Row,” or “Entire Column.” When you add a column, existing data will automatically shift to the right. When you insert a row, existing data will automatically shift down.

Deleting Columns or Rows: You can delete columns or rows from any location without having to manually move data, by right-clicking on a cell, and selecting “Delete.” When you delete a column, existing data will automatically shift to the left. When you delete a row, existing data will automatically shift up.

Tip: Note the difference between the terms DELETE, and CLEAR, in Excel. DELETE removes the data and the cell. CLEAR removes the data only. The cell remains.

Formatting numbers: Excel offers various formats for numerical entries depending on what the numbers represent. For example, you can select a currency format. This will add dollar signs to all of your entries without you having to type them in. A percentage format will treat .95 as 95%. You can select the number of decimal places that will show for each numerical entry. Date options allow you to change the way dates are displayed.

Autocomplete: Tasks that involve entering a lot of repeating data are made easier in Excel. Autocomplete will use the data you’ve entered to predict what data you want to include in your new entries, and offer you suggestions. Selecting a suggested entry can save substantial time.

Editing Cells: It’s easy to make mistakes when you’re dealing with lots of details. It’s also easy to correct or undo mistakes in Excel. You can double-click on an active cell and simply replace the data by retyping the correct values, or you can click within the active cell to adjust only parts of the data.

Freeze: This is a helpful feature that allows you to freeze a specific row or column in a fixed position. This allows certain data, such as row or column headings, to always be visible even when you scroll up, down, left, or right.

Hide: Some projects require tracking a lot of information. However, not all the information needs to be viewed each time you open the worksheet. You can select specific rows or columns to hide in order to better focus on relevant information, or to print certain information. Hiding rows or columns does not delete the data stored in those areas. They can always be unhidden.

Formulas and functions

One of the main reasons to use Excel versus other applications is to take advantage of formulas. Remember, formulas are automated mathematical calculations that Excel can perform on the data you enter. To explore the different functions available in Excel, click on the Formulas ribbon. Excel provides multiple “ready-made” formulas that you can select from, or you can create one of your own.

To enter a new formula in Excel, start with an equal sign (=), and then follow it with the calculation you want to make using cell references and operator symbols. To enter the cell locations, you can manually key in the cell references, OR you can click and highlight the applicable cells.

Functions are a way of simplifying formulas. For example, typing “SUM” is a function for adding a selection of numbers together. =A1+A2+A3+A4+A5 is the same as =SUM(A1:A5)

Functions in Excel can automate calculations with numbers, or automate presenting values with dates and text. Here are just a few examples:

Need help finding a specific formula or function and putting it in action? Simple Internet searches can provide tons of instructions and tips.

Data entry

We now know that Excel is a great tool to use for tracking and organizing lots of information. We also should give some attention to entering data into Excel. While Excel can help us make smarter decisions and speed up tasks, its usefulness is based on starting with accurate data and building on it. Here are some quick tips for ensuring your Excel sheets are set up for success.

1) Remember to always think about the purpose of your workbook. What information and content do you need to keep track of and why?

2) Think about utility. By what criteria might you need to view, sort, and filter data?

Usually it makes sense to store data in smaller parts rather than larger ones. For example, if you were creating a contact list, it would be more organized to have five fields for 1) first name, 2) last name, 3) street address, 4) city, 5) state and zip instead of one field. If information is stored in smaller sections, it will be easier to sort, reorder, and filter.

3) Leave out irrelevant or redundant information. Make sure every category of data you include has a unique purpose. Including data that is contained elsewhere is useless and leads to confusion. Including data that is irrelevant wastes time.

4) Be aware of how your data relates to and builds on itself. Think about what happens if you need to correct or change an entry. If you have formulas in your worksheet, you want to make sure that if you change one value that any information dependent on that value will accordingly be updated.

Coming up: pitching to win

Once you have a plan in place and the data to support your decisions, you need to get others on board with your solution. Presentation software provides a way to visually present different kinds of information in manageable segments to an audience. Next week, we’ll start taking a look at Microsoft PowerPoint and the power of presentation.