Excel 101: 4 Tricks Everyone Should Know

If you work with raw data on a daily basis, you are probably more than familiar with Microsoft Excel. But do you really know how to use Excel to its fullest extent? My goal is to be a master of efficiency, and many of Excel’s native features allow me to achieve that and are only a click away.
I would like to make a habit out of posting Excel tips, so if this first installation seems rudimentary to you (it is Excel 101, after all), make sure you check back for more advanced tips over time. But don’t run off too fast, even Excel 101 might teach you a thing or two.

Although most features I will outline here will work in Excel 2003 and 2007, a few Excel tricks will be marked as one or the other. This doesn’t necessarily mean the tricks can only be done in 2007, but that it is much, much easier to do so in the newest version.
I have created a simple spreadsheet to help demonstrate the following Excel tricks:

• Automatically add subtotals (Excel 2003 & 2007) - If you have been manually summing columns with data broken out by day, month, region, or any other data type, you are about to save yourself a ton of time. Select all of your data, including your column headers, and go to the data tab at the top of your screen. In there you should see an option called Subtotal (this is in the “Outline” box in Excel 2007).
  
  Here I decided I wanted to sum the number of calls and sales I have made for every month. You can also choose other options, such as to count and average the selections.
  
  After you hit “OK” you will have new rows inserted in bold with subtotals already calculated.

• Use an outline to “wrap up” data (Excel 2003 & 2007) – Now that we’ve created subtotals and saved ourselves a ton of time, it would be a shame to waste even more time scrolling up and down our spreadsheet trying to find those subtotals. Maybe you noticed after you subtotaled your data that a new column appeared on the left hand side of your screen with a 1, 2, and 3 in boxes. This is called an outline and can be used to “wrap up” data at the click of a button. Hit the “2″ box, and your spreadsheet will roll up, only showing the new subtotals you have created.
  
  Hit the “3″ to show all of your information again.

• Use Conditional Formatting to enhance your data (Excel 2007 only) – This tip is marked 2007 only, though it is possible to recreate almost all conditional formatting in 2003 if you know intermediate and advanced Excel commands. On the Home tab, there is a button in the Styles section called conditional formatting.
  
  If you click there you will see options called Highlight Cell Rules, Data Bars, Icon Sets and more. Here I have selected Color Scales that will show which months are good (green) and which are bad (red) as far as sales go.
  
  You can apply many different rule sets to excel sheets. If you have multiple conditional formatting rules you would like to apply, make sure to use the “Manage Rules” option at the bottom of the Conditional Formatting menu to set priorities and to create more advanced formatting rules.

• Automatically Format as a Table & Make it Presentable (Excel 2007, though possible in Excel 2003) – If you need to share this data with your boss or a client, you probably want the data not only easy to read, but attractive as well. Excel 2007 has made it simple to do just that. After you’ve added subtotals to your data and everything is where it needs to be, select the data you have been working with. Right next to the Conditional Formatting button you will find the Format as a Table button.
  
  Here you will find a huge list of or styles and colors, from light to dark, that will automatically format your data so it looks presentable. But this isn’t all the Format as a Table option does. After you create your table, a new Tab at the top of your screen will be created called “Design.”
  
  Under here you can easily darken, bold, or highlight certain rows or columns, add a grand total row at the bottom, as well as work with a number of other options. You will also notice, now that you have a table, that each of your headers has a new drop-down list of ways to sort the data. Convenient, huh?
  

I think that is enough Excel learning for one day. If this is all new to you, you have a lot more to play with. Have fun with the conditional formatting and the table formats, as there is a huge list of options available to you.
Keep checking back for more installments of Excel