Advanced Excel


CONDITIONAL FORMATTING alerts you when numbers meet or don’t meet predetermined conditions. The Excel function causes cells to change their appearance automatically when they meet certain circumstances. See the Gradebook, States & Capitals, and Crossword examples.

condtional_format.png


COMMENTS - A note or annotation that an author or reviewer adds to a cell in a worksheet. You can view each comment when you rest the pointer over the cell or view all comments at the same time by going to the View Menu and choosing Comments. Insert a comment by going to the Insert Menu and choosing Comment. Print options for comments are found in the Page Setup Menu under the Sheet Tab. Choose to have the comments printed on the spreadsheet or on a separate page at the end.

View comments by hovering over the red triangle.

Choose options to print comments here.
Comment_Sample.png
View comments by hovering over the red triangle.

Comments.png





CHARTING - Charts are visually appealing and make it easy for users to see comparisons, patterns, and trends in data. For instance, rather than having to analyze several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales.

Chart_Types.png



FORMULAS - Formulas are equations that perform calculations on values in your worksheet. Formulas can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks. When creating formulas, don't forget about the order of operations. "Please excuse my dear aunt Sally" still applies!

Formula.png



FUNCTIONS - A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.
  • =SUM(B6:B12) - returns the total of the amounts in the range of cells B6:B12
  • =AVERAGE(B6:B12) - returns the average of the numbers in the range of cells B6:B12
  • =MAX(B6:B12) - returns the highest value in the range of cells B6:B12
  • =COUNT(B6:B12) - returns the number of cells in the range B6:B12
  • These are just a few examples. There are many more that can be found by going to the Insert Menu and choosing Function.

Function.png

VLOOKUP FUNCTION - Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.

=VLOOKUP(lookup_value,table_array,col_index_num,)

Lookup_value is the value to be found in the first column of the array.
Table_array is the table of information in which data is looked up.
Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column.

Vlookup.png





Sample Files