Welcome to Session Four of the Excel 2013 Essential Skills video course.
This 6 minute preview is designed to enable you to quickly review all of the skills you're going to master in Session Four. If you already have some Excel experience, this 6 minute preview may enable you to skip over some, or all, of the lessons in this session.
So, let's start the clock ticking and begin the preview.
In the first lesson, you're going to format dates using both a simple date format and a more complex custom format.
In the second lesson, you're going to understand date serial numbers. You'll learn that Excel regards the date serial 1 as belonging to the 1st of January 1900, and you'll also discover that, in Excel, every time's a date and every date's a time.
In the third lesson, you'll format numbers using the built-in number formats. That's the Comma format, the Accounting Number format and the Percentage format.
In the fourth lesson, you'll create custom number formats. Custom number formats are a mystery to most Excel users, but you'll create this custom format string, which will display a value with thousand comma separators and two decimal places.
In the fifth lesson, you'll change the horizontal alignment of this worksheet so that it looks like this. And then you'll go on to explore all of the horizontal alignment options in Excel.
In the sixth lesson, you'll learn how to merge cells. In this example, we've merged cells A23 to G23 into one large cell. And we've also wrapped the text within the cell. You'll also learn how to view large quantities of text by customizing the formula bar.
In the seventh lesson, you'll vertically align the contents of this cell. At the moment it's bottom-aligned, and you'll top-align it. And then you'll go on to explore all of the vertical alignment options in Excel.
In the eighth lesson, you'll understand themes. Themes are the key to producing beautiful, consistently formatted workbooks. You'll discover that a theme consists of a Font Set, a Color Set and an Effects Set. And then these three elements are combined to produce a theme. You'll also understand why you should never use certain colors in Excel.
In the ninth lesson, you'll learn to use cell styles from the Cell Styles Gallery. You'll use the Cell Styles Gallery to format this worksheet.
In the tenth lesson, you'll add a gradient effect to cells. You'll use the Fill Effects dialog to produce this gradient transition: from blue on the left, smoothly into orange on the right.
In the eleventh lesson, you'll add borders and lines to this worksheet, to turn it into this worksheet.
In the twelfth lesson, you'll create your own custom theme. There's 29 standard themes in Excel, but sometimes you'll need a theme that represents your own corporate fonts and colors, so you'll create your own custom Color Set, and a custom Font Set, and an Effects Set, and then combine them all into a corporate theme.
In the thirteenth lesson, you'll create your own custom cell styles. Here's the normal Cells Styles dialog, and in this case you can see I've added a custom style called: Blue Note.
In the fourteenth lesson, you'll use a master style book to merge styles. You'll first create three custom styles, and then place them into a master style book. You'll then use this master style book to make the same styles available in another workbook.
In the fifteenth lesson, you'll use simple conditional formatting to shade every cell that is less than 5000 in light red.
And in the sixteenth lesson, you'll manage multiple conditional formats, using the Rules Manager. You'll first create three custom formats and then use the Rules Manager to set the order in which they're applied. And this will result in the following worksheet, where cells are shaded red, yellow or green depending upon their value.
In the seventeenth lesson, you'll bring data alive with visualizations. You'll create these Bar and Color Scale visualizations. And then you'll create this custom Three Flags visualization. You'll then go on to create this visualization, where the blue bars relate to the values in the cells to the left of each bar.
In the eighteenth lesson, you'll create the most complex type of conditional format: a formula-driven conditional format. Normally, you only change the appearance of a single cell, based upon its contents. But in this lesson, you'll learn how to format other cells based upon the value in an adjacent cell, and you'll do it using this formula-driven conditional format.
In the nineteenth lesson, you'll insert some Sparklines. Those are the graphics shown here in columns I, J and K.
And in the twentieth lesson, you'll learn how to apply a common vertical axis. In this example, each vertical axis is based upon the values in a single row. But in this example, there's a common vertical axis that's applied to every row. And you'll also learn how to change the color of certain of the bars in a Bar Sparkline.
In the twenty-first lesson, you'll apply a date axis to a Sparkline group. In this example, there are some missing dates, but there's no gaps in the Sparkline. You'll then apply a date axis to produce this worksheet, in which there are gaps for the missing dates.
In the twenty-second lesson, you'll use the Format Painter: one of the most powerful tools in Office. You'll take this partially-formatted worksheet and convert it to this fully-formatted worksheet, using only the Format Painter.
In the twenty-third lesson, you'll learn how to rotate text. You'll create this worksheet, where text is rotated in column A and in row 3.
And, as with all lessons, there's a session exercise to test your understanding of the new skills you've learned.
And you're now ready to begin Session Four: Making your worksheets look professional.