Microsoft Excel
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.