Formulas and Charts in Microsoft Excel

Formulas

• Arithmetic operators: +, -, *, /, %, ^
• Comparison operators: =, <>, >, <, >= , <=
• Logical operators: AND, OR, NOT
• Functions:
  • Math and trigonometry: SUM, AVERAGE, COUNT, MAX, MIN, SIN, COS, TAN
  • Statistical: STDEV, VAR, MEDIAN, MODE
  • Text: LEN, LOWER, UPPER, PROPER, CONCATENATE
  • Date and time: TODAY, NOW, EOMONTH, WORKDAY

Charts

• Types of charts:
  • Column: used to compare categorical data
  • Bar: used to compare categorical data
  • Line: used to show trends over time
  • Pie: used to show proportions of a whole
  • Scatter: used to show relationships between two variables
• Chart elements:
  • Title: title of the chart
  • Legend: key to the data series
  • Axes: x-axis and y-axis
  • Data series: individual data points or ranges
• Customizing charts:
  • Colors: changing colors of the chart elements
  • Fonts: changing font styles and sizes
  • Layout: adjusting the arrangement of chart elements

Pivot Tables

• Creating a pivot table:
  • Selecting data: selecting the data range for the pivot table
  • Inserting a pivot table: inserting the pivot table into the worksheet
• Pivot table components:
  • Rows: fields that are displayed as rows
  • Columns: fields that are displayed as columns
  • Values: fields that are displayed as values
  • Filters: fields that are used to filter the data
• Pivot table analysis:
  • Summarizing data: using functions like SUM, AVERAGE, COUNT
  • Grouping data: grouping data by fields or dates
  • Filtering data: filtering data by fields or values

Shortcuts

• Navigation shortcuts:
  • Ctrl + Home: goes to the beginning of the worksheet
  • Ctrl + End: goes to the end of the worksheet
  • Ctrl + Page Up: moves to the previous worksheet
  • Ctrl + Page Down: moves to the next worksheet
• Selection shortcuts:
  • Ctrl + A: selects the entire worksheet
  • Ctrl + I: selects the entire row
  • Ctrl + K: selects the entire column
• Editing shortcuts:
  • Ctrl + C: copies the selected cells
  • Ctrl + X: cuts the selected cells
  • Ctrl + V: pastes the copied cells
  • Ctrl + Z: undoes the last action

Macros

• What are macros?: series of automated commands that can be recorded or written in VBA
• Types of macros:
  • Recorded macros: recorded using the Macro Recorder
  • VBA macros: written in Visual Basic for Applications
• Creating macros:
  • Recording a macro: recording a series of actions using the Macro Recorder
  • Writing a VBA macro: writing a macro in the Visual Basic Editor
• Running macros:
  • Running a recorded macro: running a recorded macro using the Macro dialog box
  • Running a VBA macro: running a VBA macro using the Visual Basic Editor or a button on the worksheet

Quiz on formulas and charts in Microsoft Excel including arithmetic operators, comparison operators, logical operators, and functions.