ch. 1 exell

Chart Style -

-When you select a chart, a Chart Tools contextual tab is activated. There are two important tabs here. Design & Format. The Design tab holds 5 subgroups related to the layout & style of the chart. In the Chart Styles subgroup, you can from a gallery of Chart Styles.

Absolute Reference

A cell address in a formula that will not change when you copy the formula to another location. Dollar signs in the cell reference indicate when it's absolute.

Data source

A file that contains information to be merged in the main document during a mail merge.

Table Style

A set of predefined formats that you can apply to all the worksheet data.

My table has headers

A table header is a row at the top of a table used to label each column. to create a Header in Excel: Open the Excel spreadsheet. Use your mouse to select the cells that contain the information for the table. Click the "Insert" tab > Locate the "Tables" group. Click "Table". ... If you have column headings, check the box "My table has headers". Verify that the range is correct > Click [OK].

Export a Table

A table, query, form, or report. The data, including the formatting. The workbook is overwritten by the exported data. All existing worksheets are removed, and a new worksheet having the same name as the exported object is created. The data in the Excel worksheet inherits the format settings of the source object.

Defined Name

A word or string of characters in Excel that represents a cell, a range of cells, a formula, or a constant value; also referred to as simply a name.

Convert to Range

After you create an Excel table, you may only want the table style without the table functionality. To stop working with your data in a table without losing any table style formatting that you applied, you can convert the table to a regular range of data on the worksheet. Click anywhere in the table and then go to Table Tools > Design on the Ribbon. >> In the Tools group, click Convert to Range OR Right-click the table, then in the shortcut menu, click Table > Convert to Range.

Table Properties

All Excel tables have a style applied by default, but you can change this at any time. Select any cell in the table and use the Table Styles menu on the Table Tools tab of the ribbon. *** To use a table without formatting, select the first style in the styles menu, which is called "None".

Chart Layouts

An arrangement that specifies which elements are included in a chart and where they are placed.

Formula Bar

As you enter data in a cell, it simultaneously appears in the formula bar, which is located above the worksheet.

Remove all table functionality

Below are the steps to remove the Excel table formatting: Select any cell in the Excel table, Click the Design tab (this is a contextual tab and only appears when you click any cell in the table), In Table Styles, click on the More icon (the one at the bottom of the small scrollbar), and then click on the CLEAR option.

Number Filter

By specifying conditions, you can create custom filters that narrow down the data in the exact way that you want. You do this by building a filter. Point to either Number Filters or Text Filters in the list. A menu appears that allows you to filter on various conditions. Choose a condition and then select or enter criteria. Click the And button to combine criteria (that is, two or more criteria that must both be met), and the Or button to require only one of multiple conditions to be met. Click OK to apply the filter and get the results you expect.

. Criteria

Criteria are logical expressions that return TRUE or FALSE, or their numerical equivalents,1 or 0.

Recommended Charts

Excel analyzes your selected data & recommends the chart that would best suit your needs to illustrate that data. Although it's a good idea to choose the recommended chart, you still have the option to make another choice.

Function

Excel includes six new or improved functions, as follows: IFS, SWITCH, TEXTJOIN, CONCAT,MAXIFS, and MINIFS. IFS and SWITCH are simplified versions of the nested IF function, whicheliminates the need for more complicated nested functions.

Clipboard

Fills the selected range of cells in the spreadsheet with a series of numbers, characters, or dates.

Fill Series

Fills the selected range of cells in the spreadsheet with a series of numbers, characters, or dates.

Text Filter -

Filter a range of data. Select any cell within the range. Select Data > Filter. Select the column header arrow. Select Text Filters or Number Filters, and then select a comparison, like Between. Enter the filter criteria and select OK.

All Charts -

From this tab, you can choose from 15 different chart types; When selected, each chart type reveals a variety of styles for that option.

Switch row/Column Data

In the Data subgroup you can select the Switch Row/Column icon to swap the data of the axis from X to Y.

Logical Test

Logical tests are everywhere and answer a question such as: Is my salary higher than mycolleague's? To create a test, you can use one of the following symbols: = equal to, > greater than,>= greater than or equal to, < lower than, <= lower or equal t0, <> not equal to

Primary Vertical

Much like a chart title you can add, axis titles help the people who view the chart understand what the data is about. Here's how you add axis titles: If you see the Viewing button on the ribbon, click it, and then click Editing. If you see the Editing button on the ribbon, you are ready to add axis titles. Click anywhere in the chart to show the Chart button on the ribbon. Click Chart > Axis Titles, point to Primary Horizontal Axis Title or Primary Vertical Axis Title, and then click the axis title option you want.

Number Formats

Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. The most important thing to understand about number formats is that they only affect how a number looks—they have no effect on the actual value stored by Excel.

AVERAGE

Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

Banded Columns & Rows

Shading alternate rows in a large spreadsheet can improve legibility. Excel's Conditional Formatting feature allows you to apply cell shading to every other row in a worksheet range using the Modulus ( =mod ) function. Select the range of cells or rows or columns to format.

Insert Slicer

Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is currently displayed. Create a slicer to filter data: Click anywhere in the table or PivotTable. On the Home tab, go to Insert > Slicer. In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select OK. A slicer will be created for every field that you selected.

TEXTJOIN

TEXTJOIN is a new function, available in Office 365 and Excel 2019. The TEXTJOIN functionconcatenates values from one or more text strings or ranges that contain text. Unlike theCONCATENATE function, TEXTJOIN lets you specify a range of cells and a delimiter to use whenjoining text values.

Data Labels

Text that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.

COUNTBLANK

The Excel COUNTBLANK function returns a count of empty cells in a range. Cells thatcontain text, numbers, errors, etc. are not counted. Formulas that return empty text are counted.

IF

The IF function is one of the most popular functions in Excel, and it allows you to make logicalcomparisons between a value and what you expect. So an IF statement can have two results. Thefirst result is if your comparison is True, the second if your comparison is False.

SUM

The SUM function adds values. You can add individual values, cell references or ranges or a mix

Name Box

The box is next to the formula bar, and usually displays the address of the cell that youare in.

Header Row

The first row of the table that is formatted differently and should be repeated for tables that continue beyond one page.

Chart elements

The optional parts of a chart, such as the legend, title, data labels, data table, axes, axis labels, and so on.

COUNTIF-

Use COUNTIF, one of the statistical functions, to count the number of cells that meet acriterion; for example, to count the number of times a particular city appears in a customer list.In its simplest form, COUNTIF says: =COUNTIF(Where do you want to look?, What do you want tolook for?) For example: =COUNTIF(A2:A5,"London"), =COUNTIF(A2:A5,A4)

Concatenate

When you concatenate cells in Excel, you combine only the contents of those cells. Inother words, concatenation in Excel is the process of joining two or more values together. Thismethod is often used to combine a few pieces of text that reside in different cells (technically, theseare called text strings or simply strings) or insert a formula-calculated value in the middle of sometext.

Table Tools Contextual tab

Whenever you select a cell inside a table, you will notice a new tab appear in the ribbon labelled Table Tools Design. This is a contextual tab and only appears when a table is selected. When the active cell moves outside the table, the tab will disappear again.

Function Library

Which command group on the Formulas Ribbon allows a user to choose from various categories of functions to use in a worksheet?

Sort & Filter -

Which command on the Home Ribbon do you use to organize data in ascending or descending order?

Mixed Reference

a cell reference that contains both relative and absolute references

Paste Special

a feature gives you more control of how the content is displayed or functions when pasted from the clipboard.

Fill Handle

a feature in Excel that enables you to auto-complete a list in a row/column by simply dragging (+ ) it using your mouse.

Merge and Center

a feature in excel that combines multiple cells and centers the contents of the first cell. You can merge columns and rows too.

AutoFill -

a feature that helps you quickly enter sequential data. If you are entering a predictable series (e.g. 1, 2, 3...; days of the week; hours of the day) you can use the AutoFill command to automatically extend the sequence.

AUTOSUM-

a function that automatically adds the values in the cells directly above or to the left of the active cell

Legend

a narrative handed down from the past, containing historical elements and usually supernatural elements

COUNT

a simple tally of the number of occurrences of a behavior

Sparklines

a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.

Paste

add content from your clipboard to your chosen destination.

Horizonal Axis

also known as the x axis, of a chart displays text labels instead of numeric intervals

Formula AutoComplete

an Excel feature which, after typing an = (equal sign) and the beginning letter or letters of a function name, displays a list of function names that match the typed letter(s)

Transpose

change the data layout from rows to columns or vice-versa

Win/Loss

chart displays positive and negative values with different colors which can help you to view the trends of multiple data.

Custom Sort

click the sort and filter button in the editing group in the home tab and then click this option at the drop down List to display the sort dialog box

Text Function

he TEXT function lets you change the way a number appears by applying formatting to itwith format codes. It's useful in situations where you want to display numbers in a more readableformat, or you want to combine numbers with text or symbols.

Alignment

how your text or numbers are positioned in the cell. ... You can align vertically, meaning towards the top, the middle or the bottom. And you can also align horizontally, meaning to the left, the center or to the right. Excel actually has its own defaults for alignment.

Format Painter

lets you copy all of the formatting from one object and apply it to another one - think of it as copying and pasting for formatting. Select the text or graphic that has the formatting that you want to copy.

Name Range

makes it easier to identify what group of cells you are referencing in your formulaand why.

Wrap Text

means displaying the cell contents on multiple lines, rather than one long line.

Chart tile

name of your chart (try to make descriptive

Sort Criteria

ort by using levels. Sorting levels: Select a cell in the column you want to sort by. Click the Data tab, then select the Sort command. The Sort dialog box will appear. Click Add Level to add another column to sort by. Select the next column you want to sort by, then click OK. The worksheet will be sorted according to the selected order.

Copy

puts a copy of the selection on clipboard so you can paste it in another location.

Orientation

refers to how output is printed on the page. If you change the orientation, the onscreen page breaks adjust automatically to accommodate the new paper orientation.

Operator

region of chromosome in an operon to which the repressor binds when the operon is "turned off"

Cut

removes the selection and puts it on the clipboard so you can paste it to another location.

MAX

returns the greatest value from the numbers provided.

MIN

returns the smallest value from the numbers provided.

. Clustered column -

shows a simple comparison between sets of numbers using vertical columns

Range -

the difference between the highest and lowest scores in a distribution

Relative Reference

the value changes for the calculation. Ex: If you copy the cell range B6:M6 to N7, itwill automatically change to B7:M7 This is a default setting in Excel.

Y-Axis

the vertical axis on a coordinate plane

Table Style Options

this can change appearance of any table and examples are the Header Row, Total Row, Banded Rows, First Column, Last Column, and Banded Columns

Autofill

used to automatically fill a series of cells based on the numbers in the first two cells.

X-Axis

vertical labels (up and down)