If you use Microsoft Excel 2010 or Microsoft Excel 2007, you already know how simple it can be to build spreadsheets and to model or analyze virtually any type of data.
But were you aware that you can also connect and collaborate with peers with a click or quickly create attractive, customized charts and graphs perfect for use with any home or business application? It’s all in how you choose to use the resources at your fingertips.
Here are a few handy tips for getting the most from Excel.
Excel basics and formulas
Like an interactive sheet of graph paper, Excel spreadsheets are divided into rows and columns that intersect to create boxes known as cells. Columns are labeled alphabetically (A, B, C…), and rows are labeled numerically (1, 2, 3…). You can enter alphanumeric data into any cell and use formulas and functions to perform calculations on that data. Then you can create charts and graphs to display the results.
For example, to create a simple monthly household budget, just type “Budget” in cell A1, and press ENTER. In cells A3 to AX (X stands for the row number of the final cell you fill with data), enter common expense categories such as mortgage, rent, food, utilities, and entertainment. In cells B3 to BX, enter projected expenses. Two cells below BX (if X equals 15, this cell would be B17), enter the formula to total your expenses: =SUM(B3:B15). Or on the ribbon, on the Formulas tab, click the Insert Function button or AutoSum symbolin the ribbon to access this function. Then when you adjust any number in cells B3 to BX, you will see your expense total change automatically.
Note that Excel offers a full range of custom programmable formulas that can be inserted to organize and compute data. These instructions can be used to process simple functions such as adding, subtracting, and multiplying values or calculate dates, dollar amounts, and averages. Use more advanced formulas to generate auto-formatted findings, change text from uppercase to lowercase, or combine data from multiple columns into a cohesive whole.
Locking columns and rows
Freezing highlighted panes in the spreadsheet so that they remain visible while you enter data further down the page can be handy when you need to compare facts and figures. You can also split panes into multiple worksheets so that you to scroll in one pane while information displayed within the other remains static. To keep column titles and info in sight while scrolling, just follow these instructions for Microsoft Excel 2010 or Microsoft Excel 2007.
Creating charts and graphics
With Excel, you can create colorful pictures and graphs or generate eye-catching reports in minutes that help you identify usage or spending patterns at a glance.
From bar and pie charts to histograms and attention-grabbing Sparkline, Excel provides a variety of practical ways to visually represent data, which makes it easier to understand at a glance. These images offer a fast and intuitive way to display information and illustrate points more effectively. Chart options include:
- XY (Scatter >
Detailed PivotTable and PivotChart reports can also help you quickly summarize large amounts of data so that you can browse and assess information in a more streamlined way. To jazz up your spreadsheets, you can also use pictures, clip art, and other custom graphics. Learn how to use personalized images and shapes in Excel 2010 and Excel 2007.
Wrapping text and forcing line breaks
Sometimes, it’s necessary for text to appear on multiple lines within a cell, for example, when you enter people’s addresses. To create this effect, you can have Excel automatically wrap text, or you can manually enter a line break.
More hints and tips
- Access keyboard shortcuts to help you work with the data in your spreadsheets without having to scroll through menus. These one-stop guides help you become familiar with the basic keyboard shortcuts for Excel 2010 and Excel 2007.
- The ribbon, a graphical toolbar that makes it simple to build and use spreadsheets, is your key to creating useful spreadsheets. The ribbon saves you time and enables you access to a huge toolbox of visual and computational tricks. To learn more about the basic and advanced features it offers (including user customizability in Excel 2010), see the following 2010 edition video or this article on the function the Ribbon plays in Excel 2007.
- Conditional formatting allows you to automatically change the appearance of cells that meet specific, user-defined criteria. Conditional formatting lets you quickly identify important data points such as top-performing students or salesmen. Here’s how to take advantage of it this feature in Excel 2010 and Excel 2007.
- Using the Microsoft Excel Web App, you can access spreadsheets from anywhere with your web browser or easily share and collaborate in-real time with friends and colleagues. The Web App is a convenient solution to tackle group projects.
- Power Pivot for Excel 2010, a free downloadable add-on, offers even more muscle for performing calculations that involve large amounts of data. Users can also easily share spreadsheets which can help with heavy workloads.
- See these hidden gems to learn about additional helpful features in Excel 2007, including better ways to work with tabs, lists and groups. Take advantage of them to save time and increase productivity.