Top 30 Excel 2016 Tips, Tricks, Shortcuts, Functions & Formulas

This video covers at least 30 functions, formulas, shortcuts, and tips for Excel. It introduces autofill, flash fill, shortcut keys, sorting and filtering data, mathematical operations, and statistical functions like median and mode. The section also mentions creating charts, using forecast functions, and more.

Autofill allows users to quickly fill cells with repetitive or sequential data such as months, names, or patterns. The section explains how to extend patterns for numbers and dates, and how to create arithmetic and geometric sequences.

Flash Fill helps split or combine data across columns. By typing in examples, Excel can automatically fill in the rest. The section provides examples of separating full names into first and last names and using flash fill to automate the process.

Describes quick save (Ctrl+S), quick print (Ctrl+P), and creating new workbooks (Ctrl+N). It also covers how to add new sheets and use shortcuts to streamline these processes.

Combining text from multiple columns into one using the CONCATENATE and TEXTJOIN functions. The section demonstrates how to join first and last names into a single column and includes the use of delimiters like spaces.

Explains how to split data from one column into multiple columns using delimiters or fixed width. Examples include splitting names with spaces and separating parts of a string into different columns.

Converting vertical data into horizontal data and vice versa using the transpose function. It shows how to transpose single columns as well as multiple columns of data.

Sorting data alphabetically or numerically. Demonstrates sorting a list of names while keeping related data intact and sorting a list of numbers from largest to smallest.

Creating filters to display specific data within a table. Examples include filtering by text, numbers within a range, and using the table format to manage filter options efficiently.

Keeping specific rows and columns visible while scrolling through a worksheet. The section covers freezing the top row, the first column, and combinations of rows and columns.

Using LEFT, MID, and RIGHT functions to extract specific parts of a text string. Examples include splitting item numbers into location codes, manufacturer codes, and sizes.

Calculating the sum of a range of cells using the AutoSum function. Demonstrates simple addition, adding multiple numbers, and other ways to sum data using formulas.

Finding the average of a data set using the AVERAGE function. Shows how to calculate averages for a range of cells and specific sets of numbers.

Different COUNT functions: COUNT (counts cells with numbers), COUNTA (counts non-empty cells), and COUNTBLANK (counts empty cells). Examples illustrate how to use these functions with various types of data.

Performing basic math operations (addition, subtraction, multiplication, division) in Excel. Examples show how to use these operations within cells and extend patterns for multiple calculations.

Finding the maximum and minimum values in a range using the MAX and MIN functions. Also covers calculating the range, median, mode, and standard deviation for statistical analysis.

Using MEDIAN, MODE, and STDEV functions for statistical analysis. Examples include finding median values, mode (most frequent value), and standard deviation for a sample set of data.

Displaying formulas in cells to see how calculations are made. Explains using the Show Formulas button and the keyboard shortcut (Ctrl+Tilde) to toggle between formula view and result view.

Using VLOOKUP to search for and retrieve data from a table based on a specific value. Examples include looking up sales data, email addresses, phone numbers, and ages for sales agents.

Creating drop-down lists for data entry using Data Validation. Demonstrates how to set up a list of names that can be selected from a drop-down menu.

Common Excel shortcut keys for formatting and navigation. Examples include bolding text (Ctrl+B), italicizing (Ctrl+I), underlining (Ctrl+U), and creating strikethroughs (Ctrl+5).

Inserting line breaks within a cell to display text on multiple lines. The section shows using Alt+Enter to create line breaks in a cell.

Automatically adjusting the width of columns to fit the content. The section demonstrates selecting columns and using the AutoFit feature to resize them.

Inserting new rows and columns into a worksheet. Shows how to right-click to insert columns and rows, and how to move rows and columns using the Shift key.

Moving rows and columns within a worksheet. Demonstrates selecting rows or columns, holding the Shift key, and dragging to move them to a new location.

Using the Find and Replace functions to search for and replace text within a worksheet. Shows how to find specific text and replace it with new text.

Changing text case using LOWER, UPPER, and PROPER functions. Examples include converting text to lowercase, uppercase, and capitalizing the first letter of each word.

Using pre-made calendar templates in Excel. Explains how to access and customize calendar templates, and how to print them.

Applying conditional formatting to cells based on their values. Shows how to highlight cells with specific criteria, use data bars, color scales, and create custom formatting rules.

Creating scatter charts and displaying trendline equations. Explains how to select data, insert a scatter chart, add trendlines, and display equations on the chart. Also covers calculating slope and intercept using functions.