HOSP 187 UNIT 2 EXAM STUDY GUIDE
o Excel lets you control the position of content between a cell's left and right borders, known as the horizontal alignment _____ is the standard type of alignment; it aligns cells to the right id they hold numbers or dates and to the left if they hold text ______ - tells excel to always line up content with the left edge of the cell. You can also choose an indent value to add some extra space between the content and the left border _______ tells excel to always center content between left and right edges of cell ______ - tells excel to always line up content with the right edge of the cell. You can also choose an indent value to add some extra space between the content and the right border _____ - copies content multiple times across the width of the cell, which is almost never what you want _______ - same as Left if the cell content fits on a single line. When you expand more than one line, excel justifies every line except the last one _______ - is the same as center - if the cell contains a numeric value or a single word. If you add more than one word, excel enlarges the spaces between words so that the text content fills the cell perfectly
General Left (indent) Center Right (indent) Fill Justify Distributed (indent)
_______ - or the 20/80 Rule (NOT A GENERAL RULE) o For many events, roughly ___% of the effects come from ___% of the causes o Common rule in ______ that people believe in
Pareto Principle 80, 20 business
The basic principle behind cell value formatting is that the cell value that excel stores doesn't necessarily ...
match the cell value it displays
Number Format is like the General format but with 3 refinements
It uses a fixed number of decimal place (always line up) Let's you use commas as a separator between groups of 3 digits You can choose to have negative numbers displayed with the negative sign, in parentheses or in red lettering
Round () o Function needs two arguments - - to the right of the decimal point. Here's what it looks like • =ROUND(number_to_round, number_of_digits) o If you specify 0 for the number of digits, then Excel rounds to the nearest whole number o Round to the nearest 10,100,1000 and son on by using negative numbers for the second argument o For example, if you use -2 for the number of digits, then Excel rounds two digits to the left of the decimal place o Always rounds the positive values 1-4 down and 5-9 get rounded up.
The actual number you want to round The number of digits you want to keep
LARGE(), SMALL(), Functions o Requires two arguments: - - o The list position is where the item would fall if the list were ordered from the largest to the smallest [for LARGE ()] or from smallest to largest [for SMALL()]
The range you want to search The item's position in the list
Currency Formate closely matches the number format, but has two differences
You can choose a currency symbol from an extensive list Currency format always includes commas
Excel gives you 2 basic ways to use multiple rules
You can create rules that format different subsections of data. This lets you apply several different layers of conditional formatting to highlight different values You can create rules that overlap
Special characters (3)
1. Choose insert symbols symbol 2. Choose the font and subset (the group of symbols you want to explore) 3. Select the character, and then click Insert
Modifying Themes (6 STEPS)
1. From page layout themes themes gallery, choose the theme you want to use as a starting point 2. Choose your favorite body and heading font by going to Page Layout themes fonts create new theme fonts 3. Choose your fonts, enter a name for your font combination and then click save 4. Choose your favorite colors by going to page layout themes colors create new theme colors 5. Choose your colors, enter a name for your color combination and then click save 6. Optionally, save your work to a .thmx file by choosing page layout themes themes save current theme
Highlighting Specific Values o Highlight specific values o Highlight values based on where they fall in a series (4 STEPS)
1. In the caloric intake column, select the cells 2. Choose home styles conditional formatting highlight cells rules greaten than 3. Choose the type of formatting from the list box on the right 4. Click ok
Drawing borders by hand (6 steps)
1. Look in the ribbon's home font section for the border button 2. Click the border button, choose line style, and then pick the type of line you want 3. Click the border button, choose line color 4. Click the border button, and then choose draw border 5. Using the border pencil, click a grid line where you want to place your border 6. To stop drawing, head back to the border menu, and then choose draw border again
Transferring styles between workbooks (4 STEPS)
1. Open both files in excel 2. Go to the destination workbook 3. Choose home styles cell styles merge styles 4. Select the file that has the styles you want to copy into your active workbook and then click OK
Borders and fillers (5 steps)
1. Select the cells you want to fill or outline 2. Select home cells format format cells, or just right click the selection, and then choose format cells 3. Head directly to the border tab (if you don't want to apply any borders, skip straight to step 4) 4. Click the fill tab 5. Click OK to apply your changes
Changing the cell value format (4 steps)
1. Select the cells you want to format 2. Select home cells format format cells or just right click the selection, and then choose format cells 3. Set the format options 4. Click ok
Creating a custom format (7 Steps)
1. Select the cells you want to format 2. Select home cells format format cells, or just right click the selection and then choose format cells 3. Choose a format that's similar to the format you want to use 4. At the bottom of the category list, click custom 5. Enter your custom string 6. Click OK to commit your changes 7. Use the custom format you've created, select one or more cells, show the format cells dialog box, and then select your new custom format
The alignment tab lets you rotate content in a cell up to ____ degrees
180
Themes: A package of styles o Font ... ... o Colors ... o Effects ...
Body font Heading font 12 key colors To choose a theme, choose page layout themes themes to see a gallery of choices
SUM (): _______ o The wildly popular SUM () function adds everything in it o It takes over 200 arguments ... Sum (X1, X1...... X200) o Each argument be a single cell reference, a range of cells or a number o Sum () ignores cells with blank and a text content but adds dates =SUM (30,6897.583) =SUM (A1,A2) =SUM (A2:A12) RANGE =SUM (A2:A12,B5,429,1.5000)
Summing up numbers
Timesavers: o __________ - which provides a quick and dirty way to transfer formatting from one cell to another o _____ - which let you standardize your favorite formatting choices so you can use them again o ______ - which give you a toolkit with a collection of ready to use styles that can jazz up the dullest worksheet o ______ - which gets excel to do the hard work of finding values you're interested in and then highlighting them with custom formatting
The format painter Styles Themes Conditional formatting
o _____ controls the position of the content between a cell's top and bottom border ____ - tells excel that the first line of text should start at the top of the cell ______ - tells excel to center the block of text between top and bottom ______ - tells excel that the last line of the text should end at the bottom of the cell ______ - same as top for a single line of text. When you have more than one line of text, excel increases the spaces between each line so that the text fills the cell completely _______ - is the same as center - if the cell contains a numeric value or a single word. If you add more than one word, excel enlarges the spaces between words so that the text content fills the cell perfectly
Vertical alignment Top Center Bottom justify Distributed
AB() o Gives the _______ of a value The absolute value is the number stripped of any _____ sign. Thus the absolute value of -3 is 3. Here's how that function would look =ABS(-3) The ABS() function _____ change a positive number. For ex, the absolute value of 8 is 8. Absolute value calculations work equally well with whole numbers or fractional values. Often, people us ABS() to make sure that a number isn't negative
absolute negative value doesn't
Cell values
controls the way excel displays numbers, dates and times
The format Painter o Let's you ... (3 steps)
copy all of the cell's format settings - including fonts, colors, fill, borders, and even the number format - from one cell to another 1. Move to a cell that has the formatting you want to copy 2. Choose home clipboard format painter to switch into "format painting" mode 3. Click the cell where you want to apply the format
Cell appearance
cosmetic details like color, typeface, alignment and borders
Accounting Format o Modeled on the _____ (uses a slightly different alignment) o Let's you choose the _____, uses _____ and has a fixed number of _____ o Symbols always at the _____ of the cell o Always shows negative numbers in _____ o Never shows a 0 , will instead use a ____
currency format currency symbol, commas, decimal places far left parentheses dash (-)
Styles and themes o Let you create a ... o Steps (3)
customized collection of format settings, give that collection a name, and store it in a spreadsheet file name, and store it in a spreadsheet file
General is the only format other than Text that ...
doesn't limit your data to a fixed number of decimal places
Using Auto-sum (sigma sign) o When you click the auto-sum button, Excel makes an _____ about what cells you want to total o If you're at the end of a row, then Excel assumes ... o If you're at the bottom of a column of numbers, Excel assumes ... o When you click ____, Excel offers you several more auto functions
educate guess you want to add all the numeric values in all the columns on the left you want to add these values instead. down the arrow next to the Auto-sum button
Formatting numbers o In the ________, the number tab lets you control how excel displays numeric data in a cell o Excel uses number formats when the cell contains _________ o If you enter three numbers separated by dashes or forward slashes, excel assumes you're entering a data, and adjusts the number format to _______ o If you choose the __________, then you choose from dozens of currency symbols. o When you use the number format, you can choose to add __________
format cells dialog box only numeric info Date currency format commas or parentheses
Fraction Format displays your number as a _____ instead of a number with decimal places o People often use the fraction format for ___
fraction stock market quotes
Fonts and color o To change font settings, first ... Font style Font size Various font attributes, like italics, underlining, bold Font color
highlight the cells you want for format, choose home cells format Format cells, and then click the font tab
Modifying Styles o To modify a style, choose ... o To duplicate a style, choose ... o To delete style you don't want anymore, choose ...
home styles cell styles, find the style you want in the gallery, right click it, and then choose modify home styles cell styles, find the style you want in the gallery, right click ,and then choose duplicate home styles cell styles, find the style, right click it and then choose delete
Rounding numbers o The process by which you adjust fractional numbers so they're less precise but more manageable Modify the number format of the cell Using the function gives you ...
more control
Number formatting codes o Use 0 to indicate a number that ... o The question mark works similar, but it turned into ... o The # symbol lets you indicate where ...
must be wherever the 0 is place spaces instead of zeros a number can exist but doesn't have to
The _________ is surprisingly intuitive. The format only cells that contain rule is by far the most versatile (lets you pick out specific numbers, dates, blank cells, cells with errors and so on)
new formatting rule dialog box
Counta Function o The counta function counts the number of .. For example: how many students should have received a grade in the last semester
nonblank cells in a range
Two aspects of formatting are fundamental in any worksheet:
o Cell appearance o Cell values
The conditional formatting rules manager isn't just for reordering your rules. It also lets you: (3)
o Create rules (click new rule) o Modify rules (select a rule in the list, and then click edit rule) o Delete rules (select a rule, and then click delete rule)
Two rules work well with values that change frequently:
o Format only top or bottom ranked value o Format only values that are above or below average
Formatting Dates and Times o To format dates and times, first ... Excel has ___ types of date and time formats: • Format that take the regional settings of the spreadsheet viewer's computer into account (smallest) o Includes two date formats (a compact, number only format and a long, more descriptive format) and one time format • Formats that ignore the regional settings of the individual computers (more extensive) o Select a region from the locale list, you select the appropriate date or time format
open the format cells dialog box (Home cells format format cells). Choose date or time from the column on the left, and then choose the format from the list on the right. Date and time both provide a slew of options two
Percentage Format displays fractional numbers as ____
percentages
Sign () o Indicates whether a number is .... Returns 1 if the number is positive 0 if the number is 0 (or the cell is blank) -1 if the number's negative o Sign is not used too often, because it's usually just as easy to use a condition like a1<0 to check whether a number's negative
positive or negative
Count Function o The count function counts the number of cells in a ... For example: how many students received a grade in the last exam
range containing numbers
Custom styles o Styles are efficiency monsters in a few ways They let you ___ your formatting easily, just by applying the style They free you from worry about being ___, because the style includes all the formatting you want Excel automatically ___ styles with your spreadsheet file, and you can transfer styles from one workbook to another If you decide you need to change a style, you need just a few mouse clicks. Then, excel automatically adjusts every cell that uses the modified style 6 STEPS
reuse inconsistency saves 1. Begin by moving to a cell in your worksheet that has the formatting you want to use for your style 2. Select home styles cell styles new cell styles 3. In the "style name" box, type a name for your new style 4. Choose the style options you want to set 5. Click format to specify the formatting options for the style 6. Click ok to close the style window
The ____ is packed with useful conditional formatting choices -STEPS
ribbon o Home styles conditional formatting new rule
Scientific Format displays numbers using ____
scientific notion
The basic concept behind custom formats is that you define the format using a .... This format string tells excel how to format the ... You can also add fixed characters that ___, like the employee number format just describe
string of special characters and placeholders number or date, including details like how many decimal places it should include, and how it should treat negative numbers. never change
Text format simply displays a number as though it were ___
text
MAX () & MIN (): Finding Max and Min Values o The MAX () and MIN () functions pick ... o Accept over ___ cell references, rangers or numbers
the largest or smallest value out of a series of cells 200
