Formatting a Microsoft Excel spreadsheet

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.

Spreadsheet before formatting of numbers

Spreadsheet before any formatting

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.

Format cells dialog box

Spreadsheet with Format cells dialog box on screen

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

Formatted date and time with Format cells dialog box

Spreadsheet with formatting of date and time with Format cells dialog box open

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.

Spreadsheet with font, background color, and borders formatted

Spreadsheet with font, background color, and borders formatted

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.

Spreadsheet with adjusted columns and text

Spreadsheet with adjusted columns, rows, and text

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.

Fully formatted spreadsheet

Fully formatted spreadsheet

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!

Advertisements
Image | This entry was posted in lynda.com, MS Excel, Spreadsheets. Bookmark the permalink.

12 Responses to Formatting a Microsoft Excel spreadsheet

  1. Sara R. says:

    Very informative post, Maria! I do not have much experience with Excel, because it tends to confuse me. I remember taking a class in the seventh grade that taught us about the various tools that can be used within this program, but since I had not been required to use Excel since that class, I forgot them quickly. Now, thanks to your post, I am a lot more likely to use this and am more comfortable with doing so. I really liked your bit on how you can add color and borders to the spreadsheets–I had no idea that that was an option. Also, I am so glad that you linked to the other posts–that was so helpful! Great job!

  2. Tabitha C. says:

    Wow, this is such an informative post! In job interviews, I have been asked how good I am with technology. I always say I am proficient in Microsoft Office, but when I say that I’m usually just thinking of Word and PowerPoint. Excel usually gives me a headache because it just seems so “adult” to me. Your post made me a little less scared of it! I also thought your screenshots were really well done with your example worked in all the way through of fundraising by grade level. My favorite element was probably that you can turn text different ways to make charts easier to read or better laid out. Thanks so much!

  3. Angie E. says:

    Hey Maria! Great job! LIke Sara and Tabitha, I’ve been a bit intimidated by Excel, but feel much more comfortable with it now. Thanks for pointing out all the ways it will help us as future educators at the beginning of your post. I never knew there were so many cool ways to format the spreadsheets. Also thanks for explaining how the program tells you your columns are too narrow. I would definitely freak out if my numbers started changing. I know I’ll have to get more comfortable with Excel sooner or later, and your post certainly makes it much more accessible.

  4. Steph G. says:

    Maria, this was a fabulous blog post! I know that personally, Excel always overwhelms me a little bit because I don’t fully understand how to use it, but your explanation helped a lot! I always knew that you could create equations, but I always forgot the steps or did something wrong. Also, I had no idea how much you could customize a spreadsheet in Excel to look like your screenshots, which looked awesome by the way! After writing this post do you feel more comfortable in using Excel?

  5. Victoria B. says:

    Maria, your blog post was so helpful! I remember the first time I ever tried to use Microsoft Excel was in middle school. It was a pretty awful experience. I tried working with it some in high school as well and I could just never seem to understand it. Your post was so helpful in explaining how to actually use Excel effectively, without wanting to pull your hair out. I had no idea that Excel could do so many things. I now know that you can actually do plenty to format your text so that it is more aesthetically pleasing. Thanks for your help!

  6. Winna P. says:

    This post is so informative and fun! When you mentioned that sometimes it seems like Excel changes numbers randomly, I can totally relate. My mom’s an accountant and can type away in an Excel spreadsheet, entering numbers left and right with such ease. It was always so confusing to me until after reading this! My favorite part of your post was learning how to edit the visuals of the spreadsheet. I think this skill is important especially for us future teachers. It would be more effective in a classroom to use a visually pleasing spreadsheet. This post is very well explained and I now understand Excel better. I can apply all this information throughout my next years here at Elon, but I can’t wait to one day use these skills in my own classroom someday soon!

  7. Mary F. says:

    It was so helpful and made me want to read more when you first said different ways this could help us as future teachers. I thought the part on visuals was so helpful and informative. As future teachers and students that is a skill that we really need! We need to make presentations look good for classes now and we’ll need to make everything pleasing for children to look at to keep their attention and make sure it’s not too confusing. Thanks so much for all this new information!

  8. Alexia M. says:

    Maria, it has been a VERY long time since I last used excel. The last time I used it and actually knew the different parts of it was probably in middle school when I took a computer course, so this post showed me a lot about the benefits that excel can actually have even now. I don’t think I realized that using excel would be beneficial for creating tables with cool designs and colors, which I have to do sometimes for various reasons. I definitely feel more comfortable about using this program!

  9. Katie H. says:

    That was a very informative post Maria! Microsoft Excel terrifies me and if I never had to use it I probably never would, but I know that is not the case. I love knowing that there are blogs like yours that provide helpful information on how to use the program and even give links to other helpful sources. I never knew that you can format the content on a spreadsheet to include different colors and fonts, similar to the styles on Microsoft Word. This would be a helpful tool to create flyers for events around school or even documenting lists in class, allowing the students to customize each of their lists. Thanks for the helpful post that shows how easy using Microsoft Excel actually is!

  10. Chelsea S. says:

    Thank you, Maria for such a wonderful post! I haven’t really explored too much into Excel but when I have used it all of my pages look so boring! Just even adding a border or a splash of color to a certain area of the page shows variety and makes the page look more enticing! Excel sheets would be wonderful to use for students who are keeping track of measurements of a plant or another class project I might have them do!

  11. Anna B. says:

    Hey Maria! Great post!! I honestly knew next to none of this information before, so this post was incredibly helpful!! I did not know that you could change the background color of cells, put the date on your spreadsheet, and I always got so confused when those pound signs showed up!! But now I know all about formatting and how to make a boring ‘ole spreadsheet visually pleasing! This will be so helpful when I am a teacher because when I make spreadsheets for my students, colleagues, or myself, I can make them look nice and professional. Thanks for this information!

  12. Laura H. says:

    This is such a complicated process, and you did a great job of explaining it Maria! I have never had a microsoft excel spreadsheet look pretty, mine are normally just the standard white, but now I know how to spruce them up thanks to you! This is a great post!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s