Have you ever seen a sophisticated, business-like spreadsheet and wondered, How did he or she make that? More often than not, the person used Microsoft Excel to organize his or her information into a professional spreadsheet. It is an instrument used both in school and in the working worlds (corporate, governmental, and educational), so it is imperative to have somewhat of a grasp on the basics of the program. Here, I will summarize some informative videos on the formatting basics of Excel, or changing the appearance of the numbers and titles. In regards to education and teaching, there are numerous examples of how teachers need to format numerical information in a way that molds to Excel. Comparing and finding the average of test scores, using formulas to average money matters at school, and science conversion charts for presentations in class all fall under the umbrella of educational uses for Excel.
As useful as it is, Microsoft Excel as a tool can be quite confusing. All of the formatting options, such as changing the appearance of numbers by displaying decimals or currency signs and adjusting columns can give anyone a headache. The goal of this post is to have readers become more comfortable with Microsoft Excel and to go more in-depth into a lynda.com course of Excel 2010 Essential Training. Furthermore, Chapter 4 titled Essential Formatting will be highlighted and explained in this text; using the 1st, 2nd, and 3rd videos of the chapter. Excel 2010 is solely for Windows users, but Mac users may use Chapter 5 titled Formatting Worksheet Elements in Excel for Mac 2011 Essential Training from lynda.com that covers the same content.
Formatting numbers and dates in a spreadsheet is the topic of the first video. When you first create a spreadsheet, the numbers are bare, with no formatting, as shown in the image to the left. There are no decimal places, commas used as placeholders, or currency symbols adjoining the numbers. This format can be useful for some applications, but the majority of the time you will want some sort of formatting, such as a currency sign or another symbol, with the numbers.
For formatting any kind of number (like dates, percentages, times, or currency), the Number group of the Home tab ribbon will be your companion. Using the tools in the Number group, you can choose from multiple currency signs, like the dollar sign, pound, euro, or yen, to your inputted numbers. Also, large numbers are able to be formatted with commas in order to make them easier to read, as well as decimal representations of numbers formatted with percent signs instead (e.g. 0.50 displayed at 50%). Pressing the arrow button on the bottom right of the Number section gives even more possibilities for formatting the numbers and dates, shown in this image to the right. Options such as 12-hour vs. 24-hour time, different fonts, border choices, and alignment abound in this formatting opportunity.
When it comes to wanting the day’s date in your spreadsheet, there is a very simple way to do that portrayed in the first video. When you have a date written in the spreadsheet
already, you can go to the Format cells dialog box (Ctrl+1 is the shortcut for Windows users) from the Number group on the Home tab and format that date. Both the Time and Custom tabs in the Format cells dialog box offer different ways to format the date and time on the spreadsheet, as shown with the dates and time in the spreadsheet of the image to the right. If you don’t feel like choosing from the offered choices, you may manually format the date/time by using variables in the “Type” box of the Custom section. “h” for hour, “m” for minute, and “s” for second are the three variables you may need.
In the second video, it explains how to apply different fonts to the current numbers as well as how to add background colors and borders, as seen in this image. Highlighting one or multiple cells allows you to format these cells in any way you want. There is a myriad of colors and fonts to choose from, for both the fill (cell background) color and the font color.
To format font, background colors, and borders, the Font section on the Home ribbon is the place to look. There, it gives multiple options for the font theme, size, color, options to bold or italicize the words, and to fill the cell with a background color. After playing around with these different choices, you can add a border to one or all of the cells. Using the square, box-like “Apply Borders” button, also in the Font section, you can place a border around any cell(s) that you highlight in the spreadsheet. There is a drop-down box for the “Apply Borders” button that has several options for which kind of border to add to the cells. For large titles and text that you want centered in one cell or across multiple cells, there is the “Merge and Center” button in the Alignment group of the Home tab. Like in the image above of the formatted spreadsheet, you can highlight across multiple cells and use the button to center the title across all of those cells. The drop-down box of the “Merge and Center” button also allows for unmerging cells.
The final video consists of learning how to adjust columns, rows, and texts. In the image to the right, many of the different alternatives to the usual column and text adjustments are shown. For example, text can be placed at the top, center, or bottom of a cell using the “Align __” option in the Alignment tab. The title in this example is middle aligned, and placed in the center of the merged cell. Text can also be rotated to any degree angle in the Format Cells dialog box from the arrow button at the bottom right of the tab. Each grade number category has been rotated 90 degrees to become vertical in order to save some space with column width.
Have you ever keyed numbers or totaled a column in Microsoft Excel and come across the multiple pound signs in the cell? As in the image above, it is easy to freak out when Excel seems to randomly change your numbers. However, the reason for this is to tell you that the columns are too narrow. To alleviate this issue, you can simply rest your cursor on the border between two column letters, like B and C. The cursor should now become a double-headed arrow. To change the width manually, you can click and drag the column to your own width. Or if you would rather have the column just as wide as necessary, you can double-click in-between the two columns and Microsoft Excel will automatically change the width. After doing this, the column will be the perfect width, with no pound signs, as in the image to the right. These are just a few examples of what can be changed with Microsoft Excel in regards to these adjustments.
Microsoft Excel is an incredibly useful tool for a multiple of reasons. Both in school and professional life, making a well-presented, informative spreadsheet is an important skill to have. So tell me: has this post taught you something about formatting in Microsoft Excel? Do you now feel more comfortable with formatting with commas and decimals and adding color to your spreadsheet? Also, if you would like to learn more, feel free to visit Winna P.’s post about creating charts titled Excel: Creating Basic Charts Quickly and Anna B.’s post about using formulas and calculations titled Excel: Using Formulas and Functions for Calculations. And to do many of these same things in Google Spreadsheets, visit Victoria B.’s post titled Creating Spreadsheets in Google Drive! Please feel free to comment and give me your feedback! Happy formatting!