Basic Formulas in Excel

Formulas

• Basic Arithmetic Operations:
  • =A1+A2 : adds the values in cells A1 and A2
  • =A1-A2 : subtracts the value in cell A2 from A1
  • =A1*A2 : multiplies the values in cells A1 and A2
  • =A1/A2 : divides the value in cell A1 by A2
• Functions:
  • SUM(range) : calculates the sum of a range of cells
  • AVERAGE(range) : calculates the average of a range of cells
  • COUNT(range) : counts the number of cells in a range that contain numbers
  • IF(logical_test, [value_if_true], [value_if_false]) : performs a logical test and returns a value based on the result
• Cell References:
  • Absolute References: =A$1+A$2 (uses the exact column and row numbers)
  • Relative References: =A1+A2 (adjusts the column and row numbers based on the position of the formula)

Charts

• Types of Charts:
  • Column Chart: compares categorical data across different groups
  • Bar Chart: compares categorical data across different groups
  • Line Chart: shows trends over time or other continuous data
  • Pie Chart: displays how different categories contribute to a whole
• Chart Elements:
  • Title: the title of the chart
  • Legend: explains the meaning of each data series
  • Axis: labels the categories or values on the chart
  • Data Series: the individual data points or groups on the chart
• Customizing Charts:
  • Colors: change the colors of the data series and axes
  • Fonts: change the font style and size of the title, legend, and axis labels
  • Layout: adjust the position and size of the chart elements

Shortcuts

• Navigation:
  • Ctrl+Home: jumps to the beginning of the worksheet
  • Ctrl+End: jumps to the end of the worksheet
  • Ctrl+Page Up/Down: switches between worksheets
• Editing:
  • Ctrl+C: copies the selected cells
  • Ctrl+X: cuts the selected cells
  • Ctrl+V: pastes the copied or cut cells
  • Ctrl+Z: undoes the last action
• Formatting:
  • Ctrl+B: toggles bold font
  • Ctrl+I: toggles italic font
  • Ctrl+U: toggles underline

Pivot Tables

• Creating a Pivot Table:
  • Select the data range and go to Insert > PivotTable
  • Choose a cell to place the pivot table and click OK
• Pivot Table Fields:
  • Row Labels: groups the data by a specific field
  • Column Labels: groups the data by a specific field
  • Values: displays the data values
• Customizing Pivot Tables:
  • Filtering: filters the data to show only specific values
  • Grouping: groups the data by a specific field
  • Calculations: performs calculations on the data values

Formulas

• Arithmetic operations can be performed using formulas: =A1+A2 for addition, =A1-A2 for subtraction, =A1*A2 for multiplication, and =A1/A2 for division
• Functions are used for calculations: SUM for sum of a range, AVERAGE for average of a range, COUNT for counting cells with numbers, and IF for logical tests

Cell References

• Absolute references use dollar signs to lock column and row numbers: =A$1+A$2
• Relative references adjust column and row numbers based on formula position: =A1+A2

Charts

• Column charts compare categorical data across groups
• Bar charts compare categorical data across groups
• Line charts show trends over time or continuous data
• Pie charts display category contributions to a whole
• Chart elements include title, legend, axis, and data series
• Charts can be customized with colors, fonts, and layout adjustments

Shortcuts

• Navigation shortcuts: Ctrl+Home for beginning of worksheet, Ctrl+End for end of worksheet, Ctrl+Page Up/Down for switching worksheets
• Editing shortcuts: Ctrl+C for copying, Ctrl+X for cutting, Ctrl+V for pasting, and Ctrl+Z for undoing
• Formatting shortcuts: Ctrl+B for bold, Ctrl+I for italic, and Ctrl+U for underline

Pivot Tables

• Pivot tables are created by selecting data range and going to Insert > PivotTable
• Pivot table fields include row labels for grouping, column labels for grouping, and values for displaying data
• Pivot tables can be customized with filtering, grouping, and calculations

Formulas

• A formula is an expression that calculates a value using values from a range of cells.
• Formula structure consists of an operator and one or two arguments: =operator argument1 [argument2].
• Examples of formulas include:
  • =A1+B1 adds the values in cells A1 and B1
  • =A1*A2 multiplies the values in cells A1 and A2
  • =IF(A1>10, "Good", "Bad") checks if the value in cell A1 is greater than 10 and returns "Good" or "Bad" accordingly

Charts

• Charts are visual representations of data in a worksheet.
• Types of charts include:
  • Column charts
  • Bar charts
  • Line charts
  • Pie charts
  • Scatter charts
• Chart elements include:
  • Data series: the data being plotted
  • Axis: the x and y axes that provide context for the data
  • Legend: a key that explains the data series
• To create a chart, select the data range, go to the "Insert" tab, choose the chart type, and customize the chart as needed.

Pivot Tables

• Pivot tables are powerful tools for summarizing and analyzing data.
• Pivot table components include:
  • Data source: the range of cells that contains the data
  • Row labels: the categories that are displayed on the left side of the pivot table
  • Column labels: the categories that are displayed at the top of the pivot table
  • Values: the data that is being summarized
• Pivot table features include:
  • Filtering: narrow down the data to specific categories
  • Grouping: combine data into categories
  • Sorting: arrange data in ascending or descending order
  • Calculated fields: perform calculations on the data

Shortcuts

• Navigation shortcuts include:
  • Ctrl + Home: go to the beginning of the worksheet
  • Ctrl + End: go to the end of the worksheet
  • Ctrl + Page Up/Page Down: move to the previous/next worksheet
• Selection shortcuts include:
  • Ctrl + A: select all cells in the worksheet
  • Ctrl + I: select the entire row
  • Ctrl + K: select the entire column
• Formatting shortcuts include:
  • Ctrl + 1: format the selected cells as a number
  • Ctrl + Shift + %: format the selected cells as a percentage
  • Ctrl + Shift + ~: format the selected cells as a general number
• Formula shortcuts include:
  • = (equals sign): start a new formula
  • F2: edit the active cell
  • Ctrl + Shift + Enter: enter an array formula