BUAD 242- Excel

sortimng data

You can sort just by one field or by multiple fields. To sort multiple fields. To do multiple levels of sorting click the Sort button in the Sort & Filter group on the Data tab. Add your first sort and then click "Add Level."

data series

a group of related points that display in a row or column of a worksheet

data point

a numeric value that describes a single cell in a chart or worksheet.

value

are numbers that represent a quantity or a measurable amount. Excel usually distinguishes between text and value data based on what you enter. The primary difference between text and value entries is that value entries can be the basis of calculations, whereas text cannot

paste

cell contents and all formatting from copied cells

keep source formatting

cell contents and formatting from copied cells

line chart

chart displays lines connecting data points to show trends over EQUAL periods of time.

combo chart

chart is a type of chart that combines two chart types, such as column and line charts.

pie chart

chart shows each data point as a percentage of the whole data series. Each slice of the pie represents a data point. This is great for percentages.

To unfreeze panes,

click the Freeze Panes button in the Window group and Select "Unfreeze Panes." Note: This option only appears if you have frozen rows and/or columns.

paste

click the Paste button in the Clipboard group on the Home tab.

formula

combines cell references, arithmetic operations, values, and/or functions used in a calculation. -transform static numbers into meaningful results that update as values change

to auto fill

complete a series of TEXT do the following: Type the first label in the starting cell (e.g. January in cell A1) Point to the Fill Handle until the pointer changes to a dark black plus sign. Drag down the fill handle to fill all the others cells with content (through cell A12)

picture

creates a picture image of the copied data; pasted data is not editable

linked picture

creates a picture with a reference to the copied cells; if the original cell content change, so does the picture

Paste Link

creates a reference to the source cells (such as =$G$15), not the cell contents; preserves numbers formatting but not text formatting

bar chart

displays values across categories using horizontal columns. There are several varieties of bar charts, as well.

column charts

displays values across categories using vertical columns. There are several varieties of column charts.

record

each row represents______once data is converted to a table

#REF!

formula that contains a reference to a cell that was deleted or replaced with other data

formulas & numer formatting

formulas and number formatting, such as Currency, but no font formatting, such as font color, fill, color, or borders

formulas

formulas, but not formatting, from copied cells

#N/A

function is missing one or more required argument

#NULL! Error

incorrect range inspector, such as a semicolon instead of a colon withing a reference to a range

#VALUE!

incorrect type of data used

chart style

is a collection of formatting designed by excel that controls the color of the chart area, plot area, and data series. Styles include such things as flat, 3-D, or beveled and affect not only the chart but the series data as well

a table

is a group of related data organized in a series of rows and columns that is managed separately from any other data on the worksheet.

what is a function?

is a predefined computation that simplifies a formula that contains complex calculations. Excel is preprogrammed with more than 400 functions in 14 categories. You can create your own functions and store them in VBA code

what is excel

is a spreadsheet tool developed by Microsoft that is used to collect, store, organize, interpret and manage data.

CHART

is a visual representation of numerical data that reveals trends or patterns to help people make informed decisions

spreadsheet

is an electronic file that contains a grid of columns and rows to organize data. A spreadsheet may display data, formulas, or the results of calculations to enable interpretations of the data for making decisions.

hyperlink

is an electronic link that, when clicked, goes to another location in the same or a different worksheet, opens another file, opens a webpage in a Web browser, or opens an email client and inserts an email address into the To box.

conditional formatting

is applied if you want to highlight some data based on parameters. For example if certain cells are over $10,000. This kind of formatting uses the same logical test as an IF function. It applies formatting if the condition is true and does not if it is false.

cell

is the intersection of a column and a row. Cells are referred to by their cell address (i.e. C6 is the cell in column C row 6) Cells can be color coded, display text, numbers and the results of calculations, based on what you want to accomplish. An Active Cell is one that is currently opened for editing

sorting

is the process of changing the order of records (rows). To ____ data correctly you MUST select the entire table or range of data, not simply the column you are trying to rearrange. If you do not select the entire range, only one row will be sorted and the other data will remain in its place.

freeze panes

keeps both rows and columns above and to the left of the active cell visible as you scroll through a worksheet

freeze top row

keeps only the top row visible as you scroll through a worksheet

functions

must use right syntax (is the set of rules showing what you must type to make a function work. For example, where you need commas or parenthesis.) -Functions begin with an equals sign (=). Function names describe the purpose of the function. Functions have specified numbers of arguments. Arguments specify the inputs to a formula. If a function requires more than one argument, they are separated by commas.

formatting

number and text formatting only from the copied cells, no cell contents

copy data on Mac

press command +V or click on the copy button in Clipboard group on the Home Tab

Lookup value

the cell reference that contains the value to "look up."

Column_index_number

the column number in which the return value is located. Note: Start counting with 1 in the left most column of the Table_array.

FV(optional):

the future value of the loan. Note: this is assumed to be 0.

rate

the interest rate per payment period. (Example 12% Annual Percentage Rate (APR) is 1% per period)

Type(optional):

the number 1 or 0 indicates when payments are due. 0 or omitted, at the end of the period or 1, at the beginning of the period

PV

the present value of the loan. Note: The pmt() function will show a negative result. If you want to show a positive number, place a - in front of the pv.

Grouping worksheets

use control/shift keys to group worksheets -is the process of selecting two or more worksheets so that you can perform the same action at the same time on all selected worksheets.

Charting basics- formatting axes

Double Click on the axis to open the Format Axis task pane. Click on the Axis Options icon and then change accordingly You can change Upper and Lower Bounds, Units, Display Units You can change where the tick marks are (click Tick Marks) You can change where the label is (click Label) You can change the category, number of decimal places, how a negative number displays, etc (click Number)

=IF(logical_test,[value if true],[value if false])

Every if function must have (at least) two possible outcomes.

#NUM!

Invalid numeric values in a formula or function

Freeze First Column

Keeps only the first column visible as you scroll through a worksheet

percentage

Numbers displayed as percentages show the % symbol and can be shown with or without decimal places. Percentages are values and are aligned at the RIGHT side of a cell.

To display multiple sheets of the same workbook at the same time.

On the View Tab in the Windows group select "New Window" then select "Arrange all" to view options for multiple sheet viewing.

field

Once data is converted to a table, each column represents a field

paste special options

Paste, formulas, formulas & number formatting, keep source formatting, no borders, keep source column widths, transpose,values,

VLOOKUP

(lookup_value,table_array,col_index_num,[range_lookup]) -function accepts a value and looks for that value in the LEFT column of a specified table. It then returns a value located in the SAME ROW from a specified column where it found the FIRST match of its lookup value.

PMT

(rate,nper,pv,[fv],[type])

To eliminate this problem, again, there are a variety of options.

Place your cursor between columns (in this case between the L and M) and double click to auto adjust. Highlight the column and click Format in the Cells group on the Home tab and select AutoFit Column Width. ** You can also adjust to a specific width. Same logic applies for row heights.

modifying the data source

Click Select Data in the Data group on the Design tab to open the Select Data Source dialog box. Sometimes in order to get what you want you need to click "Switch Row/Column". If your chart looks weird TRY THIS FIRST!!!!!

To ungroup worksheets:

Click a sheet tab for a sheet that is not grouped. Right-click a sheet tab and select Ungroup Sheets.

INSERT A ROW OR COLUMN

Click the insert button from the Cells group on the Home tab

INSERT A CELL

Click the insert button from the Cells group on the Home tab. Once you select "Insert Cells" a popup box will appear. Select the appropriate solution from the popup box and press OK.

data table

Creating your data as a Table instead of a Range has several benefits: -Column headings remain without having to freeze pains -Filter arrows let you sort and filter with ease -Table styles easily format tables -Calculated columns copy down automatically -A calculated row lets you implement a variety of summary functions

Currency

Currency formats are used for general monetary values and includes decimal places and $. Negative numbers can be shown in a variety of ways. Numbers displayed as currency are right aligned and show the $ next to the number. Currency cells are values and are aligned at the RIGHT side of a cell.

text

Displaying a value as text (even if it is a number) will prompt Excel to display exactly what is entered. Note - Formulas requiring numbers will not function correctly when referenced to a cell displayed as text. Text aligns at the LEFT side of a cell

hide/unhide a row or column

Select the cells in the column or row you want to hide. Click Format in the Cells group on the Home tab. Click Hide or Unhide. Additionally you can select the cells and right click to hide or unhide from the dropdown menu

Fill across worksheets

Sometimes, you have already made changes to one worksheet and want to apply those changes across worksheets. For this, you want FILL ACROSS WORKSHEETS.

Worksheet

Within the workbook is where you'll find documents called worksheets. Also known as spreadsheets, you can have multiple worksheets nestled in a workbook. Tabs at the bottom of the of the screen will indicate which of your worksheets you are currently working on

Auto Fill Options

Button that allows you to copy values with/without formatting or the format only -is a feature used to help you complete a series of values.

date

-can be displayed in a variety of fashions. - are captured in Excel as numbers with the number 1 representing January 1, 1900. This allows Excel to perform mathematical operations on dates. -are values and are aligned at the RIGHT side of a cell.

to remove duplicates

-click within table and then click design tab -click remove duplicates in the in the tools group to display the Remove Duplicate dialog box or click Data Table and click Remove duplicates in the Data tools group to open the Remove Duplicate dialog box

#NAME?

-misspelled or invalid range name -misspelled or invalid function name such as VLOKUP instead of VLOOKUP- ---parentheses missing for functions -omitted quotation marks around text, such as using text instead of =IF (A4="text", A5,A6) -missing colon

sort data options:

-text: sort A-Z or Z-A (alphabetical order) -dates: oldest to newest or newest to oldest -value: smallest to largest or largest to smallest -color: sort by cell color or sort by font color

to display and use total row, complete the following steps:

1. click any cell in the table 2.click the design tab 3.click Total Row in the Table Style Options.

to insert a 3D reference, complete the following steps:

1. click the cell on the worksheet where you want to enter a formula 2.Type = 3. click the sheet tab that contains the cell you want to reference 4. Click the target cell that contains the value, label, or formula you want 5. press enter

to create an external reference btw cells in different workbooks, complete the following steps:

1. open the destination workbook and all sources workbooks 2.select the cell to hold the external reference 3. type = if you want to perform calculations or functions on the external references, type the expression or function 4. switch to the source workbook and click the sheet tab that contains the cells to which you want to link 5. select the cells to which you want to link and press enter

to apply conditional formatting, complete the following:

1. select the cells for which you want to apply a conditional format, click the Home tab, and click Conditional Formatting in the Style group. 2.

table array

: the range that contains the table where you want to find the "lookup_value." Note: THIS MUST BE ABSOLUTE REFERENCED AND YOU MUST BE LOOKING IN THE LEFT MOST COLUMN FOR THE LOOKUP VALUE

Nper

: the total number of payments. Note that loans are normally stated in years, but if paid monthly, for example, multiply years *12.

row

A horizontal alignment of cells. These are denoted by numbers ranging from 1 to 1,048,576.

formula

A sequence inside a cell that is used to produce a value. It must begin with an equal (=) sign. This could be a mathematical equation, cell references, functions or operator. A formula is also known as an expression

column

A vertical alignment of cells. These are denoted by letters ranging from A to XFD.

Table Aggregation - Making a Total Row

Aggregate functions include such functions as SUM(), AVERAGE(), MIN(), and MAX(). This effect can also be achieved in Excel with a Total Row.

HLOOKUP

An Excel function that looks up values that are displayed horizontally in a row.

3D Formulas and Functions

Syntax for a 3D formula: ='Worksheet Name'!RangeofCells -Note: Not all functions work with 3D references. Try these (they work great). SUM() AVERAGE() COUNT() MIN() MAX()

date function

TODAY() - Displays the current date in a cell NOW() - uses the computer's clock to display the current date and military time that you last opened (or recalculated) the workbook. -To update date and time, press F9 (windows), += (mac) or click the Formulas tab and click Calculate Now in the Calculations group. Additionally, you can double click on the cell and hit Enter to force the cell to recalculate.

Relative Cell Reference:

The default method of referencing in Excel. This type of reference indicates the relative location (2 cells up and 6 cells left), so that when you copy a formula with this kind of reference the referenced cells stay in the same RELATIVE position. Example: Formula from G2 is copied and pasted to G3 and G4.

Workbook

The workbook refers to an Excel spreadsheet file. The workbook houses all of the data that you have entered and allows you to sort or calculate the results.

Accountimg

This format displays numbers with currency symbols and decimal places. This format aligns $ and decimal point so that the cells display a consistent formatting. Accounting cells are values and are aligned at the RIGHT side of a cell

Range_lookup(optional):

This is an optional argument that shows whether or not you are searching for an EXACT MATCH or a RANGE OF VALUES. Default is set to true to look at a RANGE OF VALUES. If you need an exact match, you must set this. NOTE: This is the reason that the first column in your table should be sorted smallest to largest. Excel is searching for the Breakpoint (smallest number in range).

logical test for IF() functions

This test contains either a value or expression that the IF() statement evaluates to be true or false. EXAMPLES: E2>$G$13 (the value of what is in cell E2 is greater than the value of G13) = <> not equal to < less than > greater than <= less than or = to >= greater than or = to

Absolute Cell Reference:

This type of cell referencing provides a CONSTANT (Absolute) reference to a specific cell. When you copy a formula with an absolute reference, the reference to that cell DOES NOT CHANGE. The absolute reference appears with a $ in front of the row and column name. Example: Down payments in column E are calculated by house cost (Column D) * 15% (absolute reference to cell $B$4).

mixed cell reference

This type of reference combines an absolute and a relative cell reference. When copying this type of referencing in a formula, either the column or row will remain constant while the other part will change.

large datasets

To get to the end of a large data set select a cell (or row of cells) and press Ctrl+Shift+ -To go to a specific cell type the cells name in the NAME box and pressing Enter. You can also Go To a specific cell with the Go To command. Click Find & Select in the Editing group on the Home tab, press F5 or Ctrl +G

to create a table from a range already in workbook,

use the following steps: -Click within the range of data -Click the Insert Tab and click Table in the Tables group. -Select the range -Check or uncheck the "My table has headers" Click OK -You can also create a table by selecting the range and clicking the Quick Analysis button, clicking Tables in the gallery and then clicking Table.

A structured reference

uses a tag or table element, such as a field label to reference in a formula in Excel. These structured references use the @ symbol as part of their syntax.

flash fill

uses data in previous columns to determine what to fill in.

values & number formatting

values that are the results of formulas, not actual formulas, preserves number formatting, but not text formatting

values and source formatting

values that are the results of formulas, not actual formulas; preserves number and text formatting