excel project 2 formulas, functions, formatting and web queries
TRANSCRIPT
![Page 1: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/1.jpg)
Excel Project 2
FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES
![Page 2: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/2.jpg)
FORMULAS
• Formula - mathematical equation assigned to a cell that Excel uses to calculate a result–
• Order of Operations• Move from left to right in a formula• First negation, then all percentages, exponentials,
multiplication, division, addition, and last subtraction• To override the order of operations in Excel formulas, use:
• Parentheses
• Point Mode – allows you to select cells by using the mouse to point on the desired cell
![Page 3: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/3.jpg)
FUNCTIONS
• Function – takes a value or values, performs an operation, and returns a result to the cell• Arguments – the values that you use with a function• Average - Sums up the numbers in the specified
range and then divides the sum by the number of nonzero cells in the range
![Page 4: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/4.jpg)
MAXIMUM & MINIMUM VALUE
Three ways to find the maximum value in a range are:1. Manually type in the formula =MAX(range) or =MIN(range)
2. Arrow on the AutoCalculate or AutoSum button
3. Insert function (Fx) button on the formula bar
![Page 5: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/5.jpg)
FORMATTING• Currency Style – formatting style that applies floating dollar
signs and adds commas• Accounting Style – formatting style that places the dollar
sign at the far left in a fixed location, displays negatives in parentheses, and changes zeros to dashes
• Comma Style – inserts a comma every three positions to the left of the decimal point and causes numbers to be displayed to the nearest hundredths
• The easiest way to change a decimal number to a percent to click on the percent style (%) button
![Page 6: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/6.jpg)
FORMATTING
• The best way to include text in the same cell, but on two separate lines is to use:• Alt +Enter
• In order to select non-adjacent data, highlight the desired data, and hold down the:• Ctrl key
![Page 7: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/7.jpg)
FORMATTING
• Condition – is made up of two values and a relational operator, is true or false for each cell in the range
• Conditional Formatting – applies formatting to cells that only meet certain conditions that you specify
• Fill Color – allows you to change the color of the cell
![Page 8: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/8.jpg)
FORMATTING
• Increase Decimal – this button allows you to display additional decimal places in the cell
• Decrease Decimal - this button allows you to remove additional decimal places in the cell
• Best Fit – when the width of the column is adjusted to fit the widest entry
![Page 9: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/9.jpg)
FIXED $ SIGN
• When the dollar signs line up in a row to the far left of the cell
• This type of dollar sign occurs when the accounting style is applied
![Page 10: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/10.jpg)
FLOATING $ SIGN
• When the dollar sign displays directly next to the number in the cell
• This type of dollar sign occurs when the currency style is applied
![Page 11: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/11.jpg)
WEB QUERY
• Allows you to import data stored on a Web site
![Page 12: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/12.jpg)
PRINTING
• Values Version – shows the results of the formulas you have entered
• Formula version – allows you to view the actual formulas used• Hold the Crtl + ~ keys to see the formula version
of the worksheet
![Page 13: Excel Project 2 FORMULAS, FUNCTIONS, FORMATTING AND WEB QUERIES](https://reader036.vdocuments.us/reader036/viewer/2022062422/56649e875503460f94b8b3bb/html5/thumbnails/13.jpg)
PRINTING SPREADSHEETS
• In order to fit the spreadsheet data on one page, you need to change from portrait to landscape and choose the “Fit to one” option in the page setup dialog box under Print Preview