Microsoft Excel Tips & Tricks
PRESENTATION
OVERVIEW
• Creating Tables
• Transpose Data
• Remove Duplicates
• Text to Columns and Concatenating Data
• Creating and Formatting Charts
• Conditional Formatting
• Lookup Formulas
• The IF Function
• What-If Analysis
CREATE A TABLE IN EXCEL
• To make working with data easier, you can organize data in a
table format on a worksheet
• Tables provide easy filtering, in addition to calculated columns
and total rows, which make calculations simple
• Highlight Selection of Data
• Insert Table
CREATE A PIVOT TABLE IN EXCEL
• A pivot table is a program tool that allows you to reorganize and
summarize selected columns and rows of data in a spreadsheet
or database table to obtain a desired report
• Tool that allows you to reorganize and summarize data in a table
form
• Highlight Selection of Data
• Insert PivotTable
TRANSPOSE DATA
• Transpose data in excel to switch rows to columns or columns to
rows
• This is useful for when you would like to quickly view your data
in a different rotation
• Copy Paste Special Transpose
REMOVE DUPLICATES
• The Remove Duplicates feature in Excel allows you to quickly
eliminate all cells which have duplicated values within your
selection of data
• When you use the Remove Duplicates feature, the duplicate
data will be permanently deleted
• Before using the feature, it’s a good idea to copy the original
data from one place to another
• Data Remove Duplicates
TEXT TO COLUMNS
• To separate the contents of one cell into separate columns, you
can use the “Text to Columns” feature
• Data Text to Columns
CONCATENATE
• The CONCATENATE function refers to the joining together the
contents of two cells in a worksheet
• Formulas Insert Functions CONCATENATE
CREATING & FORMATTING CHARTS
• Charts are visual representations of worksheet data. Charts
often makes it easier to understand the data in a worksheet
because users can easily pick out patterns and trends illustrated
in the chart that are otherwise difficult to see.
• Highlight applicable Data
• Insert Charts
CONDITIONAL FORMATTING
• Conditional formatting is a feature of Excel which allows
you to apply a format to a cell or a range of cells based on
certain criteria.
• Home Conditional Formatting
CONDITIONAL FORMATTING
• Home Conditional Formatting
• Highlight Cells Rules
• Highlight cells that meet specific criteria
• Top/Bottom Rules
• Highlight cells that are within a numeric range
• Data Bars
• Compare cells with numeric values with Data Bars
LOOKUP FUNCTION
• The LOOKUP function returns a value from a range of data
• Formulas Insert Function VLOOKUP
IF FUNCTION
• The IF function returns one value if a condition you specify
evaluates to TRUE, and another value if that condition evaluates
to FALSE
• Set your own rules to label a cell as you want it to return a certain
value
WHAT-IF ANALYSIS
• What-if analysis is the process of changing the values in cells to
see how those changes will affect the outcome of formulas on
the worksheet
• Scenario Manager
• Worst Case/Best Base Scenarios
• Goal Seek
• How to get a desired result
MICROSOFT EXCEL TIPS & TRICKS
Questions?
Microsoft Excel Tips & Tricks
PRESENTATION
