Microsoft Excel

Ace your homework & exams now with Quizwiz!

absolute references

remain constant no matter where they are copied. this reference is designated in a formula by the addition of a dollar sign. it can precede the column reference, the row reference, or both

IF function

returns one value if a condition you specify is TRUE and another value if that condition is FALSE

AVERAGE

returns the arithmetic mean of a range of values

Rename Worksheet tab

right click the tab and select rename

change worksheet tab color

right click the worksheet tab

change worksheet order

right click the worksheet tab and select Move or Copy. Another way is to click and drag

apply cell formats

they are applied from the Font group on the Home tab and include such formatting as Bold, Italics, Fonts, Size, Color, etc. Applying cell formats help emphasize the contents

add additional data series

this expands a chart. simply click the Select Data feature on the Chart Tools Design tab in the Data group and select the Data Series you want to add

add a worksheet to an existing workbook

this is done by clicking the new worksheet symbol located to the right of the last worksheet tab at the bottom of the workbook. You can change the name of the worksheet by right-clicking it and selecting rename

inspect a workbook for accessibility issues

Check Accessibility allows you to locate and correct content that people with disabilities might find difficult to read. This useful tool is accessed by clicking the Check for Issues button in the Info category under the File tab.

COUNTIF

Counts number of cells within a range that meet a single specified criterion

remove duplicate records

Excel makes it easy to locate matching records in a table and Remove Duplicates. This feature works similar to the filtering tool but allows Excel to identify duplicate records so you can choose to remove all but the first occurrence of the record. Remove Duplicates works only on tables and is accessed from teh Table Tools Design tab, in the Tools group

Replace data

The Replace features in Excel allows you to search your workbook and quickly change data or text. This feature can also be used to locate formatting and replace it. The Replace features are found in the Editing group on the Home tab as part of the Find & Select feature.

$A$2

The column and the row do not change when copied. absolute.

change workbook views

You can do this from the View tab, and change it to Normal, Page Break Preview, or Page Layout. You can also create Custom Views. The default view is Normal.

Check Compatibility

allows you to identify features not supported by earlier versions of word. This useful tool is accessed by clicking the Check for Issues button in the Info category under the File tab.

split

allows you to view two parts of a worksheet at the same time, which is convenient when working on one large area of a worksheet whole looking at another.

cell reference

an address of a cell or a range of cells on a worksheet and can be used in a formula so that Excel can find the values or data that you want that formula to calculate

apply conditional formatting

applied from the styles group on the home tab

cell styles

are predefined combinations format, font, size, color, and effect

Print All or Part of a Workbook

by default, Excel will only Print Active Sheets; however, you can choose to Print Entire Workbook or Print Selection. To do this, go to Print under the File tab, then choose which you want to print.

change window views

can be changed from the Window group on the View tab. From here you can open a New Window and Arrange All of them. The Freeze Panes feature allows you to select a range of rows or columns and freeze them in place.

move charts to a chart sheet

sometimes you may want to display a chart on its own worksheet. Excel provides and easy way to do this by clicking Move Chart from the Location group in the Chart Tools Design tab.

resize charts

there are two basic ways to resize a chart. one is to select the chart then grab the resizing handles and drag them to the desired size. another way is to change the Shape Width and Shape Height settings in the Size group on the Chart Tools Format tab

insert and remove hyperlinks

you can link to either a cell reference or named element within the workbook or outside the workbook, such as to a website URL

it is possible to populate an Excel worksheet by importing data from either a

.txt file, .csv file, or other external sources. importing options are all found on the Data tab in the Get External Data group

AVERAGEIF

=AVERAGEIF(range, criteria, [average_range])

filter records

Autofilters are the little down arrows located in each column heading of a table. these filters can be shown or hidden from the Sort & Filter feature in the Editing group on the Home tab, or from the Sort & Filter group on the Data tab.

outline data

Data Outlines are created from the Outline group on the Data tab. An outline is created using the Group feature. Data is grouped by something common in a row or column, such as a category. After a Data Outline is created, the groups of rows or columns can be collapsed and later expanded for visual organization. You can also add a subtotal to each grouping. Both Data Outlines and Subtotals are located in the Outline group on the Data tab.

switch between rows and columns in source data

On the Chart Tools Design Tab in the Data group are two notable feature in the Data group: Switch Row/Column and Select Data. Switching the orientation of the chart from Rows/Columns and Columns/Rows changes the chart to analyze the data from a different perspective

hide or unhide columns and rows

This is done from the Format feature in the Cells group on the Home tab. Another way is to select the columns and rows and right click then select hide or unhide.

analyze data by using Quick Analysis

Whenever you select a group of cells and then right click the Quick Analysis menu will appear. From this menu you can select Formatting, Charts, Totals, Tables, or Sparklines to analyze your data.

display repeating row and column titles on multipage worksheets

You may want to Repeat Row or Column Titles if the data on your worksheet spans more than one page. This makes your worksheets easier to read. Excel has a quick and easy method to repeat a selection of rows or columns on every printed page using Print Titles in the Page Setup group on the Page Layout tab.

special format

accommodates phone numbers, zip codes, and social security numbers

Insert Subtotals

after data has been grouped, you can add a subtotal to each grouping. subtotals are located in the Outline group on the Data tab.

COUNTBLANK

counts cells that are empty

add or remove table rows and columns

do this from the right click menu

Go To Special

feature on the ribbon used to navigate to the last cell of the worksheet

Go To

feature used to find specific cell references

currency, data, and time format

formats for different countries

set a print area

from the Page Setup group under the Page Layout tab, you can define a specific print area

Insert header and footer

insert tab

change workbook themes

located in the page layout tab

General number format

no format

types of cell references

relative and absolute

Home tab

used to add sheets

Page layout, normal, page break preview

what are the 3 views

under the view tab

where is the free panes feature located

fraction format

defines the number of digits

custom format

defines your own format

number number format

displays comma separators and negative numbers

create new chart

go to the Insert tab in the Charts group

modify document properties

managed under Info under the File tab

apply number formats

they are applied from the Number group on the Home tab. click the dialog box launcher in the lower right corner of the group to see all of the format options available.

apply cell styles

they are selected from the gallery located in the Styles group on the Home tab

percentage and scientific format

specifies decimal places

Creating a workbook

The most frequent method is to start from a blank workbook. This allows complete control to insert and format data.

cut, copy, or paste data

These tools are located on the home tab in the Clipboard group. Excel elements that have been cut or copied remain on the Clipboard for repeat insertions until the workbook is closed

insert total rows

a Total Row automatically sums the values in a table. this feature is located in the Table Style Options group the Table Tools Design tab.

delimited

a character or group of characters used in computer applications to set apart individual items in a program or data set. some of the commonly used characters are commas.

save workbooks in alternative file formats

a file form such as .pdf or .xps protects a workbook from being altered as both these formats are read-only. There are also formats that are generic, such as .csv and.txt. Alternate file formats can be selected from either Save As pr Export under the File tab.

CONCATENATE function

a function that joins text strings together. For example, id you wanted to create and email alias by joining a person's first initial and lastname you would use this function

MIN function

a process that will return the minimal value in a set of values. any cell in the range that contains text or logical values will be ignored

configure table style options

after a table is created, you may want to modify the Table Style Options, such as banded columns or banded rows, adding a header row, or filter buttons. these options are enabled by clicking the box next to the desired option in the Table Style Options group on the Table Tools Design tab

SUM function

allows you to add the values you have set as your parameters. For example, the formula =SUM (A1:A5) add all the numbers contained through cells A1 to A5. Whereas, =SUM(A1, A3, A5) adds just the numbers contained in those cells

SUMIF function

allows you to add up those values in a selected data range that meet specific criteria. =SUMIF(CellRange, "Criteria")

UPPER, LOWER, and PROPER functions

change the case of text from uppercase to lowercase or propercase, which capitalizes only the first letter.

Customize Quick Access Toolbar

click the down arrow on the left end and selecting the items you'd like to add to the tool bar

COUNTA

counts number of cells in a range that are not empty

COUNT function

counts the number of cells in a range that contains numbers

display formulas

clicking Show Formulas in the Formula Auditing group of the Formulas tab displays the formulas contained within the cells of your worksheet instead of the data

inserting references

includes not just inserting pure relative or absolute references, but also a mix of the two, within the same worksheet and across different worksheets

MAX function

is used to find the largest number in each range of cells. it will only analyze numeric values from cells

format cells by using format painter

lets you dip into any cell to copy its formatting, then brush that formatting onto other text. the advantage to the Format Painter is you don't need to know what combinations of formatting have been applied to the existing cell, you simply copy all of it and apply it to the new cell

creating a workbook from a template or creating a blank workbook are found

on the file tab by selecting new

modify cell alignment and indentation

Alignment and Indentation are set from the Alignment group on the Home tab. From the ribbon, you can align the data within a cell to Align Top, Align Middle, Align Bottom, Align Left, Align Center, Align Right. By clicking the dialog box launcher in the lower left corner you have a few more options. You can also set Indentation from within this feature. Another type of alignment you can do within this feature is slant the data within a cell by setting the Orientation.

referencing multiple worksheets

Excel allows you to refer to any cell on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to another. To do this, you will simply need to begin the cell reference with worksheet name followed by an exclamation point (!). For example, if you want to reference cell A1 on Sheet1 , its cell reference would be Sheet1!A1. Note that if a worksheet contains a space, you will need to include single quotation marks (' ') around the name. For example, 'July Budget'!A1

Hide or unhide worksheets

Located on Format in the Cells group on the Home tab. However, the easiest way is to select the worksheet, right click and select hide. To show the worksheet, click unhide from the Format feature.

paste data by using special paste options

Paste Special allows you to paste only specific aspects of the data, such as only the Formulas, Values, Formats, Comments, Validation, Themes, or Column Widths. You can even paste columns as rows or paste rows as columns by using Transpose, or you can choose to Skip Blanks and not paste them.

wrap text within cells

Wrap Text is a useful setting that forces data in a cell that is too wide to fit the column width to wrap onto the next line in the cell. Wrap Text is located in the Alignment group on the Home tab.

relative references

change when a formula is copied to another cell. by default, all cells are this reference. when copied across multiple cells, they change based on the the relative position of rows and columns

copy and move worksheets

right click the worksheet tab and select Move or Copy. The default is simply to move , so if you want to make a copy you must enable the box, Create a Copy. You can also re-order worksheets by using a click-drag and dragging the worksheet to the position of where you want it.

template

sample workbook containing basic formatting and some content. The content is designed to be replaced or modified so that the workbook can be created quickly by simply adapting it to fit your needs.

fill cells by using Auto Fill

the AutoFill feature in Excel allows you to quickly create a series of entries based on the contents of just one or two cells. Auto Fill can populate a series of dates based on days of the week, months of the year, yearly quarters, dates, time, number series, abbreviations, and other options. Auto Fill is controlled by a right mouse click on the Fill Handle located in the lower right corner of the cell you want to copy.

create and Excel table from a cell range

the Format as Table feature, located in the Styles group on the Home tab, converts a Cell Range to a Table.

inspect a workbook for hidden properties or personal information

the Inspect Document feature helps you identify and remove hidden properties and personal information from your workbook before you distribute it. This useful tool is accessed by clicking the Check for Issues button in the Info category under the File tab.

modify page setup

the Page Layout tab includes features to change the Margins, page Orientation, page Size and several other settings.

apply styles to tables

the easiest way to change the color of a table is to change the Table Style. Table Styles are located in the Table Styles group on the Table Tools Design tab.

convert a table to a cell range

the feature Convert to Range is located in the Tools group on the Table Tools Design tab

quick access toolbar

the menu located in the upper-left corner of the Excel window

change magnification with zoom tools

the scroll bar in the bottom right corner allows you to quickly adjust it. you can also pull up the zoom dialog box by clicking in the zoom group under the View tab

Insert and Delete Cells

these features are located in the Cells group on the Home tab. They are also available on the right click menu

inserting and deleting columns and rows

these features are located in the Cells group on the Home tab. They are also available on the right click menu. Simply place the cursor in the target location and select whether to delete or insert a row or column

RIGHT, LEFT, and MID functions

these functions allows you to parse a cell of data to extract a specified number of characters. For example, you could use the LEFT function to extract the area codes from a list of phone numbers. (cell of interest, number of characters you want to extract) The RIGHT function works the same way. The MID function works the same way, except there is an additional field, Start_num which tells Excel how many characters from the left to begin extracting.

adjust row height and column width

to adjust you can simply click and drag either the top or bottom line in the left margin of the row. A more precise way to to change the size is from the Home tab, Cells group, Format feature. In the Cell Size section of the Format drop down menu select either Row Height, AutoFit Row Height, Column Width, AutoFit Column Width, and Default Width

Merge Cells

to span a cell horizontally across multiple columns, use Merge cells. This is a useful feature when you want to create a heading across multiple columns. You can also merge cells vertically down a column to span multiple rows. Either of these activities can be accomplished by selecting the cells to merge then selecting the specific type of merger you want from Merge & Center located in the Alignment group on the Home tab.

search for data within a workbook

using the Find and Select feature on the Home tab is one of the most common ways to locate specific data. The user simply selects Find and types in the data to be located. Data can also be replaced with the same tool by using the replace tab.

set print scaling

you can even Scale to Fit all rows on one page or all columns on one page. To do this, go to Print under the File tab, then choose either No Scaling, Fit Sheet on One Page, Fit All Columns on One Page or Fit All Rows on One Page. You can also choose to create Custom Scaling Options

navigate to a named cell, range, or workbook element

you can navigate to a named cell, range, or workbook by either typing or selecting the name in the Name Box located to the left of the Formula Bar

change sort order

you can use the auto filters to Change Sort Order from smallest to largest, largest to smallest, or by Color. you can also sort by using the Sort & Filter feature in the Editing group on the Home tab, or the Sort & Filter group on the Data tab

sort data in multiple columns

you sort data in multiple columns using the Sort&Filter feature in the Editing group on the Home tab.


Related study sets

NCLEX Questions - Fundamentals - Health and Physical Assessment (304)

View Set

math 2 - 2nd semester exam review

View Set

CoursePoint - Chapter 61: Management of Patients with Neurologic Dysfunction

View Set

Topic 1: Cardiovascular System Blood

View Set

Farma - antybiotyki cz. III (cefalosporyny) - (Krząścik od str. 20)

View Set

The Secular, Secularization, Secularism by Jose Casanova in Rethinking Secularism, (Pg. 54-74)

View Set

HW5: Homework - Ch. 5: Price Controls and Quotas: Meddling with Markets

View Set