Step by Step - Chapter 8 - How To

How to...Format Cells.
   Regular formatting.
      Go to the Home tab. If you want to change the size, color, font, or you want to add borders or shade the cell, use the buttons found the the Font group. If you want to change the actual style, go to the Styles group and click on Cell Styles. Choose the style that you want. Now your data label should be easier to read.
   Change row or column size.
      Highlight the row(s) or column(s) that you want to change. Either go to the Home tab, to the Cells group, click Format and choose the action that you want or right-click on the column or row and choose either row height or column height.
   Change the standard font for a workbook.
       Click the Microsoft Office Button and then click Excel Options. Go to the Popular tab and in the Use this font and Font size list boxes pick your new font.
How to...Use and modify styles.
   Using Styles.
      To use a regular cell style go to the Home tab. Go to the Styles group and then click Cell Styles. Choose the style that you want. To use style a table go to the Home tab. Go to the Styles group and then click Format as Table. Choose the style that you want.
   Conditional Formatting.
      Conditional Formatting is a way to apply a certain style to a cell that meets certain criteria. To apply it, go to the Home tab. Go to the Styles group and click on Conditional Formattting. Choose the style that you want. These preset styles already have a rule to them but you can always modify that rule. To modify the rule, go to the Home tab, go to the Styles group and click on Conditional Formatting. Then go to Manage Rules. From the dialog box you can choose which rule you want to modify and then click Edit Rule. You can also delete rules and add new rules.
   Create a Conditional Format.
      Select the cells that to which you want to apply the format and then go to the Home tab, then to the Styles group. Click Conditional Formatting. Select which kind of condition that you want to create. In the dialog box, choose the rules that you want to apply to the format.
   Creating New Styles.
      To create new styles go to the Home tab, and then go to the Styles group. If you want to create a new table style click on Format as Table and then click on New Table Style. From there you can choose what you want to add to your table style. If you want to create a new cell style click on Cell Styles and then click on New Cell Style. From there you can choose what you want to add to your cell style.
How to...Use and Modify Themes.
   Using Themes.
      To apply a theme, go to the Page Layout tab and then go to the Themes group. Click on Themes and choose the theme that you want. To use theme colors go to the same group but click on Colors instead. To use theme fonts go to the same group but click on Fonts instead. To use theme effects go to the same group but click on Effects instead.
   Creating Themes.
      To create a new theme you need to format your document a certain way that you like. Once formatted, go to the Page Layout tab and then go to the Themes group. Click on Themes and then click on Save Current Theme. Your format scheme will be saved.
How to...Change Number Formatting.
   To change the formatting of a number, highlight the cell and right-click. Go to Format Cells. In the dialog box you can choose which category you want to choose and then choose the specific formatting. Also, if you are choosing a formatting that includes decimals you can choose how many decimal places you want your number to have. Click OK to close.
   To create a new numeric format, go to the Home tab, go to the Number group and click the Number group's dialog expander. In the dialog box, go to the Number tab, then in the Category list click Custom. This will display custom number formats in the Type list. Choose the base format that you want and modify it in the Type box.
How to...Add Images to Documents.
   To add a picture, go to the Insert tab, and then in the Illustrations group, click Picture. You can also add clip art which is found in the same group.
   To change the image's style, go to the Design tab (which appears when the image is selected).
   When you want to resize the image you can click and drag the handle in any corner of the image.

What I Remember From Horrifying Excel Exam

Um...well here's what I remember:

1) One question asked for the average of a column based on another column which is based on another column.

2) One question asked to use the IF function to put in Yes or No if one cell was greater than a certain number which was based on whether or not another cell was greater than another number.

3) One question asked to copy and paste a comment.

4) One question asked to put a certain style on cells whose contents contained numbers greater than 950.

There was probably more but my brain blocked them out. Too horrifying...

How to Name Groups of Data

Steps:

1.   Select the cells that you want to include in your range.
2.   Go to the Formulas tab and then go to the Defined Names group.
3.   Click Define Name which will display the New Name dialog box.
4.   Once there, type a name in the Name field.
5.   Make sure that the cells that you selected are in the Refers to field.
6.   If you want to add a comment, type it in the Comment field.
7.   Choose if you want to make the name available in just one worksheet or the entire workbook.
8.   Once your information is verified, click OK.

A faster way to name the range is to type the name in the Name box next to the formula bar.

Extras

If the range has a label that you want to use as the name follow these directions.
1.   Go to the Formula tab and then go to the Defined Names group.
2.   Click Create from Selection which will display the Create Names from Selection dialog box.
3.   In the dialog box, choose which label you want according to where it is. For example, if the label in is the
      top row, choose the top row check box.

If you want to change or manage the named ranges follow these directions.
1.   Go to the Formula tab and then go to the Defined Names group.
2.   Click Name manager which will display the Name Manager dialog box.

Now you can either choose New, Edit, Delete, or change the cell reference in the Refers to field.

1.   Clicking New will open the New Name dialog box.
2.   Clicking Edit will oopen the Edit Name dialog box which is a version of the New Name dialog box.
3.   Click on a named range and click Delete to delete it.
4.   In the Refers to field, you can change what cells you want to be included in the selected named range.

Chapter 6 Quiz

What is the simplest way to enter data in a worksheet? Click a cell and type a value.



Describe the purpose of AutoFill and FillSeries. AutoFill is used to automatically copy or continue data from the selected cells into the ones that you fill using the fill handle. FillSeries is used to continue recognized elements to make a series. For example, you have January and February. You use the fill handle to continue the series which will be March, April, and so on.


List the available AutoFill Options that appear when you add data using AutoFill.


1. Copy Cells   2. Fill Series   3. Fill Formatting Only   4. Fill Without Formatting


What is a Pick from Drop-down List for in a spreadsheet? It gives you a shortcut menu that includes recently used values so that you don’t have to type them again.


Where does the Paste Options button appear and what is it used for in a spreadsheet? It appears right next to data that you have just pasted. It allows you to choose if you want the data use the destination’s theme, or if you want it to only be the value. You can also choose if you want it to be the value but with number formatting, and you can also just paste the formatting only. Other options include Values and Source Formatting and Keep Source Column Widths.


How do you locate specific data on a worksheet? Use the Find & Select button. From there you can choose if you want to find a value or a certain type of formatting.


What does the Replace table enable you to do in a worksheet? When you enter a value in the Find what field you can use the Replace field to replace the value in the Find what field with the value in the Replace field.


List three proofing and research tools available in Excel 2007


1. Spelling Check   2. Thesaurus   3. Translator


How do you create a data table in Excel 2007? Select the first cell in the range of cells that you want to change into a data table. Make sure that there aren’t any empty cells, or extra cells next to the table. Go to the Format as table button and choose the style that you want. Excel will automatically change the range of cells into a data table.


What is the purpose of the Total row in a table? It allows you to sum up the data in your table. You can use the down arrow to choose which function you want the total row to use.

Step by Step Chapter 6

Chapter 6 Intro
   Excel allows you to visualize and present information using charts, graphics, and formatting.
   You can organize data into tables.
   Tables allow you to analyze and store data quickly and easily.
   You can enter a data series; repeat one or more values; and control how Excel formats the cells, columns, and rows.
   You can also check the spelling of worksheet text.
   You can use a Thesaurus and translate words to other languages.

Entering and Revising Data
   Easiest way to enter data is to click a cell and type a value.
   You can enter a sequence by putting in the first element of the series and then using AutoFill to fill in the rest of the series.
   Grab the fill handle at the bottom-right corner of the cell and drag it to where you want to fill your data.
   FillSeries allows you to enter 2 values in a series and then use the fill handle to extend the series.
   If you drag the handle up or to the left, Excel will use values before the value that you are using to fill. If you have January, the other values would be December, November, October, etc.
   You can hold the Control key while you drag the handle so that the series won't be continued but instead copied.
   Data entry techniques include: AutoComplete, AutoFill, FillSeries, Pick from Drop-down list, and the Control and Enter key combination.
   Auto Complete finishes a word if a similar value exists when you type in the first few letters of a word.
   Pick from Drop-down list gives you a shortcut menu that include recently used values that you could pick from.
   To use Control+Enter combination you select a range of cells that you want to contain the same data, then type the data into the active cell and press Control+Enter.
   The AutoFill Options appear next to data that you just added using AutoFill.
   You can choose Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, or Fill Days, Weekdays, etc. from the AutoFill Options.
   Copy Cells copies the contents of the selected cells and puts them into the area that was being filled.
   Fill Series continues the series with the next item in the sequence.
   Fill Formatting Only applies the formatting of the selected cells to the area that was being filled.
   Fill Without Formatting is exactly like the Fill Series option only without the formatting.
   Fill Days, Weekdays, etc. is the option that appears if you began the series with days, weekdays, or other.

Moving Data Withing a Workbook
   You can move cells and worksheets.
   When a cell is highlighted, it is called the active cell.
   When multiple cells are selected, they are called a cell range.
   You can cut, copy, delete, or change the format of selected cells.
   You can move columns and rows.
   You do that by clicking and dragging the column header or row header.
   The Paste Options button appears next to data that you just copied and pasted.
   Paste Options include: Use Destination Theme, Match Destination Formatting, Keep Source Formatting, Values Only, Values and Number Formatting, Values and Source Formatting, Keep Source Column Widths, and Formatting Only.
  
Finding and Replacing Data
   Use that Find & Select button to easily find data in large worksheets.
   You can go to the Options section to choose specific formatting to find.
   The Find what field contains the value that you want to find.
   The Find All button selects every cell that matches the value in the Find what field.
   The Find Next button finds the next occurence of the value in the Find what field.
   The Replace with field contains the value that you want to put in to replace the value in the Find what field.
   The Replace All button replaces all of the values that match the Find what field.
   The Replace button replace the next value that matches the Find what field.
   The Options button displays additional options on what to Find and Replace.
   The Format button displays the Find Format dialog box and allows you to find values based on formatting.
   Within list box allows you choose whether to search in the active worksheet or the entire workbook.
   The Search list box allows you to select whether to search by rows or columns.
   The Look in list box allows you to select whether to search cell formulas or values.
   The Match case check box makes sure that values have the same capitalization.
   The Match entire cell contents check box makes sure that the values are exactly the same.
Correcting and Expanding Upon Worksheet Data
   You should check and correct data that you enter.
   You can use the spelling checker to automatically check spelling for you.
   You can replace it with the proper word from the list of suggestions or ignore the mispelling.
   You can add new words to the Excel custom dictionary.
   You can use the Thesaurus to find synonyms and antonyms.
   You can translate text from one language to another.
Defining a Table
   You can format a range of cells as a table.
   You can add total rows and other types of rows in the Design tab.
   You need to make sure that there aren't any empty cell when creating a data table.
   You also need the header and data below the header.
   There also can't be any data next to the area that you want to change into a table.
   To add data to a table, select a cell either below a row or to the right of a column and type a value.
   You can turn of the AutoCorrect Options.
   You can add rows and columns to a table.
   You can delete rows and columns.
   You can choose what function you want to use in the total row.
   You use the down arrow that appears in the total row.
   You can change the name of your table.
   You can change your table back to regular text.

Step by Step: Chapter 5 - Section 4 & 5

Modify Worksheets
   You can change the width of a column and the height of a row in a worksheet.
   Increasing the width of a column or the height of a row can make it easier to modify data.
   You can change the width of multiple columns at the same time. You can do the same for the height of rows.
   You can insert rows and columns.
   Inserting rows and/or columns can make a worksheet less crowded and easier to work with.
   If you go to the Home tab, go to the Cells group, and then click the Insert button's down arrow, you can insert a row, a column, a cell, or a worksheet.
   New rows are inserted above the active cell.
   New columns are inserted to the left of the active cell.
   When these items (not including the worksheet) are inserted in a worksheet with existing formatting, the Insert Options button appears.
   You can choose to: Format Same as Above, Format Same as Below, Format Same as Left, Format Same as Right, and Clear Formatting.
   Format Same as Above applies the format of the row above to the inserted row.
   Format Same as Below applies the format of the row below to the inserted row.
   Format Same as Left applies the format of the column to the left to the inserted column.
   Format Same as Right applies the format of the column to the right to the inserted column.
   Clear Formatting applies the default format to the inserted row or column.
   You can delete rows and columns.
   You right click on the row or column head and then click Delete.
   You can hide multiple rows or columns.
   Go to the Home tab, go to the Cells group, click the Format button, point to Hide & Unhide, and then click either Hide Rows or Hide Columns.
   Do the same but click either Unhide Rows or Unhide Columns to unhide.
   When you insert a cell, Excel asks you how you want the surrounding cells to be shifted when you insert the new cell.
   You can delete cells.
   You can move a group of cells to another location in your worksheet.
   Select them, then point to the selection's border. When it changes to a four-way arrow, you can drag and drop it.

Customize the Excel 2007 Program Window
   You can change the program window, which includes the user interface.
   Zooming In on a Worksheet
   You can increase the size of an object by zooming in.
   You can change the zoom level by typing in a cusom magnification level in the Zoom control. At the bottom-right corner.
   Clicking the plus sign (Zoom In control) increases the size by 10 percent.
   Clicking the minus sign (Zoom Out control) decreases the size by 10 percent.
   You can use the slider to change the size.
   The View tab's Zoom group contains the Zoom to Selection button. It zooms into the contents of any selected cell (up to the maximum level of 400 percent)
   Arranging Multiple Workbook Windows
   You can have multiple windows open in one window of Excel.
   You can easily switch between windows using the Switch Windows button.
   You can have a formula in one workbook reference another workbook.
   You don't need to update the data in the other workbook if the formual is referenced.
   Adding Button to the Quick Access Toolbar
   When you use certain commands more frequently than others, you can add them to the Quick Access Toolbar.
   The Quick Access Toolbar is located at the top-left corner.
   The drop-down arrow is where you can customize the Quick Access Toolbar.
   You can go to the Microsoft Office Button and go to the Excel Options to change the Quick Access Toolbar.
   You can also change the button position on the toolbar.
   You can choose if you want your changes to be in all or just the current workbook.

Step by Step Book

Intro
   When Excel is opened, a blank workbook is used.
   The blank workbook has 3 worksheets.
   You can add or delete, hide or unhide, and copy or move worksheets.
   You can change the document's properties.
   You can customize the window to fit your style.
   You can look at multiple workbook windows in just one window.
   In Excel 2007, you don't have to look for some commands, you can just put them in the Quick Access Toolbar.

Create Workbooks
   Create a new workbook if you want to store something that's not related to another workbook.
   Default new workbooks have 3 worksheets.
   You can add and delete worksheets.
   You use the Microsoft Office Button to create a new workbook.
   When you start Excel, a default new workbook opens.
   You can use the Microsoft Office Button to open another workbook.
   You should save workbooks after making changes.
   When you save a file, you overwrite the previous copy of it.
   You use Save As to save another copy of the original file.
   You can save your file as a different format in the Save As dialog box.
   You can save your file in a different location in the Save As dialog box.
   You can add more information to make the file easier to find by changing the properties.
   You can search for the title, author, keywords, etc.
   In the Microsoft Office Button, go to Prepare and click Properties to change the Document Properties.
   You can change more properties by going to the Document Properties arrow and then clicking on Advanced Properties.
   In the Custom tab of the advanced Properties you can create your own property.
  
Modify Workbooks
   Workbooks are used mainly for business activities.
   They can record information on those activities.
   Default new workbooks have 3 worksheets.
   You can add and delete worksheets.
   Worksheets' default names are Sheet1, Sheet2, Sheet3, and so on.
   You can change worksheet names.
   You can copy worksheets from one workbook to another.
   You can move and copy worksheets in a workbook.
   You can change the order of worksheets.
   You can change the tab color of worksheets.
   You can hide and unhide worksheets.