When you work with data, spreadsheets can be a handy way to organize everything in an easy-to-understand format. Spreadsheets are ideal for setting budgets, tracking progress on projects and planning. In the pre-technology era, businesses used paper-based ledgers to track these things, but now Microsoft Excel is the tool of choice. Fortunately, creating and updating Excel spreadsheets is relatively straightforward.
How to Make a Spreadsheet
A spreadsheet has the same setup whether it's on paper or your computer screen. You have rows that start at the top and continue for the duration of the data. There are also columns, which begin at the left side of the spreadsheet and continue to the right as far across as you need. The point where a row meets a corresponding column is called a cell.
In Microsoft Excel, each row is given a number, while each column has a letter. So the first cell is A1. If you give each column a header, the first row is reserved for that purpose, requiring you to input a title for each column. If you're monitoring a budget, for example, the first column might be the products, while column B is the money you spent and column C, the tax you paid, followed by the total price in column D.
Making an Excel Spreadsheet
If you have Microsoft Office, Excel is included in the package. Go to your list of applications and choose Microsoft Excel from the list. Select File and New to open a new spreadsheet with the default title of Book1. At the bottom of the screen, is a tab that reads Sheet1.
As with Microsoft Word, you have a ribbon at the top of the screen that allows you to customize the document and format the cells. Under Home, you can bold text or change the font type and size. You can also change text alignment within each cell to left-aligned, right-aligned or centered.
Workbooks Versus Worksheets
One thing that can be confusing initially is the terminology. Each new document you open in Excel is called a workbook. Within each workbook, you create a worksheet, which you'll see as a tab at the bottom of the screen. By default, a new worksheet is called Sheet1, but you can right-click on it and choose Rename to give it the name you'd like.
To create a new worksheet, click the + symbol next to the worksheet tab at the bottom of the screen. You can insert a worksheet between two you've already created by right-clicking a tab and choosing Insert Sheet. You can move sheets around by clicking on the desired tab and dragging it to the right or left of where it is currently located.
Using the Sum Feature
One of the most useful features of an Excel spreadsheet is its AutoSum feature. To see how this formula works, type a series of numbers in a column and then click on the blank cell beneath the last number. On the ribbon, click Formulas and AutoSum. You'll see the formula for the AutoSum pop up in the last box – for example, =SUM(A1:A4). When you click Enter, the formula totals the rows above it and enters the sum into the box that holds the formula.
You can also write your own formulas. As you grow more comfortable with this feature, you can extend what you can do within the application. In the AutoSum formula =SUM(A1:A4), for instance, the formula is directing Excel to total the items in rows A1 to A4.
Pivot Tables to Organize Data
As you begin working with larger volumes of data, you'll feel an increasing need to manage everything more efficiently. A pivot table is built into Excel as a way to easily organize information. Instead of manually sorting through rows and rows of information, you can set things up to quickly get to the data you need.
With a pivot table, you aren't reorganizing the Excel sheet or changing the data within it. Instead, you're turning the data around in a different way – hence the name "pivot" – to look at it from a different direction. You can either input the information manually or let Excel do the work for you.
Creating a Pivot Table
If you want to create a pivot table for your Excel sheet, the easiest way is to let Microsoft recommend one for you. Under Insert, click the drop-down arrow next to Tables and choose Recommend PivotTables. At that point, Microsoft presents you with several options for organizing your data.
If you're feeling adventurous, you can create your own pivot table based on the data you've entered. Under Insert, go to the drop-down arrow next to Tables and select PivotTable. You can choose to either insert your pivot table in the existing worksheet or start a new worksheet with the information. After you select Enter, you'll be able to start building your report.
Creating a Dashboard in Excel
Another useful Excel feature is the dashboard, which can track your data in one place. As businesses trend toward data-driven decision-making, this type of easy-to-access information is becoming essential. The best way to ensure success as you're building a dashboard is first to plan what you hope to gain by doing so. The metrics you need to gather should be listed long before you start building.
When you're ready to start, import your data into Excel if it isn't already there. Then, open a new workbook and set up one or two extra worksheets by adding tabs at the bottom. The extra sheets are where you hide your extra data. Then add a Gantt chart by choosing Insert, Charts and the second option. To insert your data, right-click on the chart and choose Select Data.
Creating a Schedule in Excel
Among the many business uses for Excel is schedule creation. If you run a business with part-time employees who follow a weekly schedule, Excel can be useful. Following a brief Excel tutorial, you can start creating a schedule for your team.
To create a schedule, open Excel and input Schedules into the Search All Templates box in the top-right corner. Choose the template that best fits what you're trying to do. In this case, it's likely either Employee Shift Schedule or Weekly Employee Shift schedule. Once there, you can type over the fictitious names with your own employee names, add lines and delete headers so that you can change the times listed.
Creating a Macro in Excel
Macros are a handy Microsoft tool that lets you save time-consuming steps by designating simple commands to go with more complicated tasks you execute every day. Creating and using Macros is surprisingly easy, especially once you get the hang of it. First, you'll need to add the Developer tab by choosing Excel > Preferences > Ribbon & Toolbar. Check the Developer box in the list on the right and save the change.
To make a macro, choose Record Macro on the Developer tab. Name the macro, input the shortcut key you want to point to the action, and press OK. Immediately execute the action you want to recorded and then press Stop Recording in the Developer tab. Any time you want to perform that action, you merely press the keyword sequence you specified when you recorded the macro.
Make an Excel Pie Chart
Long lists of numbers can be boring. You can spice up any Excel spreadsheet by adding visual elements. A pie chart is a great way to show percentages, with each section of the pie representing a segment of the information you're conveying.
To create a pie chart, you first need to have the data in a spreadsheet. If you surveyed 100 people, make a list in one column of the answers and the number of people choosing each answer in the column directly next to it. Select the information you want to be included in the pie chart and choose Insert on the menu bar and Pie. Then select the style you prefer.
Make an Excel Bar Graph
A bar graph is better for showing how something has changed over time or for comparing various items against one another. If you've surveyed a large group of people on the topics they're most interested in, for example, you can use a bar graph to show how the men and women answered for each subject area.
After you input the information into an Excel sheet, select the data you want to include and choose Insert > Chart > Bar from the menu bar. The information is displayed as a bar chart automatically. You can change the layout of the new bar chart in the ribbon at the top of the screen, including adding chart elements, quickly adjusting the layout, and changing the colors that are being used to illustrate your data.
Create Mailing Labels in Excel
Many businesses find an Excel spreadsheet is a handy tool for organizing and managing large mailing lists. Although there are more complex database apps for businesses that have unwieldy mailing lists, Excel can work just fine for businesses that are still building and growing a customer database. It can also be a quick and painless way to make mailing labels out of a small list of names you've grabbed online or from responses to a promotion.
Before you can create mailing labels, you first need to build the mailing list in a way that can be easily made into addresses. If you want to print labels, though, you'll need to go to Microsoft Word and choose the type of mailing you'd like to do from the Start Mail Merge drop-down under Mailings. If you want to use the list you've set up in Excel, you click on the drop-down arrow next to Select Recipients and choose Use an Existing List. Then, connect your Excel document.
How to Print a Spreadsheet
No Excel tutorial would be complete without reviewing how to print the document you've created. Printing in Excel can be a little tricky, requiring a couple of extra steps if you want gridlines or you need to control the area of the document you want to be printed. Since one workbook can contain multiple worksheets, you also need to make sure you're printing only the sheets you need.
Before printing, select the worksheets you want to print. If you want to print multiple sheets, hold down the Shift key while clicking on the tabs. Choose File > Print > Show Details. Here you can choose whether to print active sheets or the entire workbook, as well as choosing the orientation. If you want to print gridlines or headers, though, you need to indicate that before you choose File > Print by selecting Sheet Options under the Page Layout tab and checking the boxes to print those items.
- Computer Hope: Spreadsheet
- Microsoft: Use AutoSum to Sum Numbers
- TechTarget: Pivot Table
- Office: Create a PivotTable to Analyze Worksheet Data
- SmartSheet: How to Create a Dashboard in Excel
- HubWorks: How to Create a Shift Schedule in Excel
- Microsoft: Quick Start: Create a Macro
- Microsoft: Add a Pie Chart
- Microsoft: Present Your Data in a Column Chart
- Microsoft: Select One or Multiple Worksheets
- Microsoft: Print a Worksheet or Workbook