MS Excel - Tips

EXCEL TIPS

HIDE AND UNHIDE COMMAND
Why do we need to know this?
Allows us to hide and unhide particular rows or columns.
Simplifies working with the spreadsheet.
Prevent certain information from being seen.
How do we use this feature?
Select the row(s) or column(s) to be hidden / unhidden.
Select Format : Row : Hide/Unhide or Format : Column : Hide/Unhide
MOVING AROUND A SPREADSHEET WITH CTRL, SHIFT, AND ARROW KEYS
Why do we need to know this?
Save lots of time.
Move the first or last cell of a contiguous data block without scrolling.
How do we use this feature?
Ctrl-Arrow : Move to the first/last data cell in the arrow direction.
Ctrl-Shift-Arrow: Selects the cells between the current cell and the first/last data cell.
NAME CELLS/RANGES
Why do we need to know this?
Allows specific cells or cell ranges to be referred to by name.
Allows us to write equations such as = Quantity*Cost instead of =$B$12*$C$4
How do we use this feature?
Select the cell or cell range.
Select Insert: Name: Define from the menu bar.
SORT COMMAND
Why do we need to know this?
Correctly sorting a series of rows or columns without disassociating the data is critical to many modeling efforts.
How do we use this feature?
To sort by single category, just click into column, NEVER highlight column (would destroy table integrity)
To use multiple criteria, click any cell of data table, select Data…Sort
Data table will be selected.
Indicate if have Header row, which will not be included in sort
Select Options to use Custom lists (create first)
Select Tools/Options/Custom Lists to create specialized sort orders, e.g.
To sort months and weekdays according to their calendar order instead of their alphabetic order
To rearrange lists in a specific order (such as High/Medium/Low entries)
TOGGLING AMONG RELATIONAL AND ABSOLUTE REFERENCES
Why do we need to know this?
Saves lots of time.
How do we use this feature?
F4 key toggles through the different options.
FILL DOWN AND FILL RIGHT COMMANDS
Why do we need to know this?
Saves lots of time.
Allows for copying of cell content to contiguous cells with a single keystroke.
How do we use this feature?
Select the cell with the content to be copied and drag to select the cells to which the content should be copied.
Ctrl-R to fill right.
Ctrl-D to fill down.
Double-check the formulas for absolute vs. relative references.
IF FUNCTION
Why do we need to know this?
Conditional comparisons are used in virtually all spreadsheets.
Knowing how to use IF in a nested manner and in combination with other functions will save hours of time.
How do we use this feature?
IF(Comparison,TrueAction,FalseAction)
IF(Comparison,TrueAction,) ==> Cell shows 0 if condition is false.
IF(Comparison,TrueAction,””) ==> Cell shows blank if condition is false.
AND AND OR FUNCTIONS
Why do we need to know this?
Used with the IF function to enable more complicated logical comparisons.
How do we use this feature?
AND(Comparison 1,Comparison2,Comparison3,…)
OR(Comparison 1,Comparison2,Comparison3,…)
SUM AND SUMIF FUNCTIONS
Why do we need to know this?
SUM is used in virtually all spreadsheets
SUMIF can save lots of time in most spreadsheets if we know how to use the function.
How do we use this feature?
SUM (Range1, Range2, Value1,…)
SUMIF(Range,”Comparison”,SumRange)
If a SumRange IS NOT specified, SUMIF sums the cells meeting the Comparison criteria in the specified Range
If a SumRange IS specified, SUMIF sums the cells in SumRange where the corresponding cells in Range meets the Comparison criteria
NOTE: The “” signs must be used for the Comparison value
SUBTOTALS AND TOTALS
Why do we need to know this?
Want to add lines with subtotals in our P&L or balance sheet, but still need to run the total over all numbers? Don’t want to get confused with nested subtotals and totals in your spreadsheet?
How do we use this feature?
Instead of ‘=sum(range)’ add ‘=subtotal(9,range)’ where you need a subtotal or total.
We may nest this function as we like. Excel keeps track of everything
SUMPRODUCT FUNCTION
Why do we need to know this?
If we need to multiply two columns and need the sum of the multiplication, sumproduct comes easy.
How do we use this feature?
Insert =sumproduct(range1,range2)
NPV FUNCTION
Why do we need to know this?
We can create our own discounting table and then calculate the NPV of our cash flow series or just use the NPV function
How do we use this feature?
Insert =NPV(discount rate,cash flow numbers,...)
The discount rate is in percent
The cash flow numbers are either an array or individual numbers in individual cells
Attention: The first cash flow number is in period 1, e.g. the end of the period. If we have for example an initial investment in period 0, just type =NPV(…)+period 0 payment in our calculation
COUNT FUNCTIONS
Why do we need to know this?
Prevents us from wasting time counting items manually or creating dummy variables to count such items
How do we use this feature?
COUNT(Range1,Range2,Value1,...) ==> count the number of cells containing numbers
COUNTA(Range1,Range2,Value1,...) ==> count the number of non-empty cells
COUNTBLANK(Range) ==> count the number of empty cells in the range
COUNTIF(Range,”Criteria”) ==> count the number of cells in the Range containing the Criteria.
NOTE: The “” signs must be used for the Criteria value
ROUND, ROUNDUP AND ROUNDDOWN FUNCTIONS
Why do we need to know this?
Many situations exist when we need to have exact numbers instead of various fractions in our calculations (e.g., there cannot be 536.235 bank branches)
How do we use this feature?
ROUND(Number,Digits) ==> Round the number (or cell) to the specified number of digits
If Digit = 0, then Number is rounded to nearest integer
If Digit > 0, then Number is rounded to the specified number of decimal places
If Digit < 0, then Number is rounded to the specified number of digits left of the decimal place
ROUNDDOWN(Number,Digits) and ROUNDUP(Number,Digits) work the same way as ROUND, but the direction of rounding is specified by the function
VLOOKUP AND HLOOKUP FUNCTIONS
Why do we need to know this?
Allows us to automatically lookup a particular cell of data from a larger data range. This is especially useful when we have
A large data section that contains information for multiple records somewhere on the spreadsheet (e.g., a small database)
A calculation area somewhere else, and we need to refer to some specific data elements for specific records.
How do we use this feature?
VLOOKUP and HLOOKUP allows us to find a specific cell of data in a larger data range.
Use VLOOKUP when each row contains a separate record and the associated columns contain data for that one record
Use HLOOKUP when each column contains a separate record
VLOOKUP(SearchValue,Range,ColumnNumber,Error) ==> look for a value in the row specified by SearchValue and the column specified by ColumnNumber
SearchValue indicates the “match key” (i.e., find the row that contains the SearchValue in the first column)
Range specifies the cells containing the data
ColumnNumber specifies the column that contains the data element you want
Error determines what happens when Excel does not find the exact SearchValue you want. FALSE leads Excel to display a #N/A when an exact match cannot be found. TRUE leads Excel to display the next smaller value than SearchValue
HLOOKUP(SearchValue,Range,RowNumber,Error) ==> look for a value in the column specified by SearchValue and the row specified by RowNumber.
NOTE: The 1st column of data must be sorted in ascending order when using VLOOKUP, and the 1st row of data must be sorted if using HLOOKUP
INSERT FUNCTION COMMAND
Why do we need to know this?
What do we do if we do not know what functions are available or how to enter the arguments for a function?
How do we use this feature?
Select the cell
Select Insert : Function from the menu bar
PASTE SPECIAL COMMAND
Why do we need to know this?
Saves lots of time
Retyping formulas
Converts formulas into values
Reformatting cells
Transposing cells (i.e., convert row-entered data blocks into column-entered ones
How do we use this feature?
Copy the cells of interest
Place the cursor where we want to past the information
Select Edit : Paste Special from the menu bar
Select the appropriate options from the dialog box that appears
AUDITING FEATURES
Why do we need to know this?
Quickly find the cells referenced by a formula and/or quickly find which cells reference a particular cell of interest
How do we use this feature?
Select View : Toolbars : Customize from the menu bar. Check the Auditing box from the Toolbars tab
Click on the cell of interest
Select the Trace Precedents or Trace Dependents icon from the Auditing Toolbar
SPLIT WINDOWS AND FREEZE PANES
Why do we need to know this?
Splitting a window allows us to work on multiple parts of a large spreadsheet simultaneously.
Freezing the pane allows us to always keep one part of the spreadsheet (e.g., column or row labels) visible.
How do we use this feature?
Drag the split horizontal and split vertical icons to the desires positions
Click on the freeze pane icon from the tool bar to freeze the panes.
GOAL SEEK ADD-IN
Why do we need to know this?
Easily find what one input variable needs to be to achieve some desired result in a calculation
How do we use this feature?
Select the calculated cell
Select Tools : Goal Seek from the menu bar
Enter the desired resulting calculation into the “To Value” form in the dialog that appears
Enter the input cell in the “By changing cell:” form
SOLVER ADD-IN
Why do we need to know this?
Allows us to use linear programming to find the optimal inputs to achieve some desired calculation result (e.g., maximize revenues by increasing daily tickets, increasing store size, average sale/ticket, etc. simultaneously)
Use Solver instead of Goal Seek when:
We need to place constraints on the input variable (e.g., cannot open a store for more than 24 hours a day)
More than 1 input variables are involved
We want to minimize or maximize the resulting calculation in addition to just setting the calculation to a predetermined value
How do we use this feature?
Select the final calculated cell, then select Tools : Solver from the menu bar
Select what we want to do from the “Equal to” section (i.e., maximize, minimize, or set to a specific value)
Reference the input cells (note, separate cells by using a comma or “:” if cells are contiguous.
If the input values have constraints, click on Add to enter the constraints
Click on Solve
DATA TABLES COMMAND
Why do we need to know this?
Simplest way to run sensitivity analyses
How do we use this feature?
Input the values which we want to test for a particular variable on separate rows (e.g., A6:A13)
In the cell above and to the right of the first sensitivity value, reference the final result of your calculations (e.g., A5 = C3)
Select the cells containing the calculation and input variables (e.g., A5:B13)
Select Data : Tables from the menu bar
Input the cell referenced by the formula in the “Column input cell” (e.g., A2). This example uses in “Column input cell” because the value to test in the sensitivity analysis are arranged in a single column
SCENARIOS ADD-IN
Why do we need to know this?
If we have created a model and need to run various scenarios. Then use the scenario function under the tools menu. Keeps our inputs and outputs from the model nicely together
How do we use this feature?
Assign names to the excel cells that act as input parameters for our model
Start the scenario function by selecting Tools: Scenarios from the menu bar.
Click Add to enter our first scenario
Create a name
Select ALL cells that will be our input to the model.
Assign the desired scenario value to each input parameter.
Add more scenarios as needed
When finished click on summary and select scenario summary (the pivot table is not so helpful)
PIVOT TABLES
Why do we need to know this?
Most powerful tool to arrange huge amounts of data in a more structured way than pure sorting. In particular helpful to run quick sums, averages, distributions, etc. in combination with a structure criteria, e.g. total number and average sales per store size band
How do we use this feature?
Select Data: PivotTable Report…
Step 1: Microsoft Excel list
Step 2: Select the relevant data area
Step 3: Drag and drop data elements on row and column (this is your table structure), the data you want to analyze on the data area
Step 4: Just press Finish
PROTECTING CELLS AND WORKSHEETS
Why do we need to know this?
Sometimes we want to give our Excel file to someone else and prevent them from changing the formulas for seeing some hidden cells
How do we use this feature?
Protecting a spreadsheet or workbook involves two steps
Designating which cells to be locked or hidden
Protecting the spreadsheet or workbook
Note several weird peculiarities:
The default for all cells in a spreadsheet if LOCKED. So if you want the receiver of your worksheet to change the content of a cell, unlock the cell before protecting the spreadsheet
The formulas in a cell can be seen even if the spreadsheet is lock -- UNLESS you hide that cell before protecting the spreadsheet
To lock/unlock and hide/unhide a cell, select the cell(s) and select Format: Cell. Select the Protection tab when the dialog box appears
To protect/unprotect a spreadsheet, select Tools : Protection : Protect Sheet
EDITING MULTIPLE WORKSHEETS SIMULTANEOUSLY
Why do we need to know this?
Avoid having to redo our work on multiple spreadsheets in a single workbook
How do we use this feature?
Select the first spreadsheet to be edited
Hold the Ctrl key while clicking on the additional spreadsheets
Do our editing
CONDITIONAL FORMATTING
Why do we need to know this?
Sometimes we would to color the output of cells in different colors, e.g. negative numbers in red, positive numbers in black, or add a frame, etc.
How do we use this feature?
Mark the relevant fields and select Format: Conditional Formatting
Select the criteria for the format and adjust the format. We can actually change the font, the border and the color
Click on Add to select additional criteria for the formatting
AUTOFILTER COMMAND
Why do we need to know this?
We have a huge pile of data and quickly want to find some specific information, e.g. all sets that meet criteria or the top 10 items etc.
How do we use this feature?
Click into our table or better mark the data area and select Data: Filter: Autofilter
Using the drop-down boxes per item allows us to display only specific filtered information
Selecting multiple matches (up to 3 maximum with autofilter) we can narrow down our search
OR
add our own criteria for filtering by clicking on the custom criteria
CUSTOMIZE TOOL BARS
Why do we need to know this?
How many icons on the tool bar to use regularly?
How often do we have to use the menu bar or mouse to do something we wish were accessible with a single click?
How do we use this feature?
Select View : Toolbars : Customize
Click on the Commands tab
Drag items on and off the toolbar as our wish
OR
Right click toolbar area
Select Customize
Select Commands tab in Customize dialog box
From appropriate menu, find the command for which we want to add button
Drag button to location on toolbar
Other favorites ...
Paste values
Select visible cells
Save as
Show comment (toggles it)
Set print area
Page setup
Merge cells
Auto filter
Paste values
CHANGING DEFAULT WORKBOOK
Why do we need to know this?
How often do we use the menu bar to change the normal font or number formats?
We can create the basic number and font formats you use regularly, save it as a template, and have Excel use that template every time we create a new workbook
How do we use this feature?
Create a workbook with the formatting we use regularly and save it under the name “Book” and Template format
Move the “Book” template to the Microsoft Office : Office : Xlstart folder
GROUP/UNGROUP PARTS OF SPREADSHEETS
Why do we need to know this?
How often would we like to hide or unhide parts of a complex spreadsheet?
If answer is “very often”. We will like to group/ungroup function instead of the hide/unhide command, since we will be able to toggle between hidden or displayed columns or rows.
How do we use this feature?
Mark the row or column that we would like to “fold”, i.e. hide for the moment.
Click on Data: Group and Outline: Group
To “fold” click now on the “minus” sign outside of our column or row
We may also group or ungroup hierarchically
SWITCH OFF THE MICROSOFT ACTORS
Why do we need to know this?
Also find the Microsoft Actors more disturbing than helpful?
Always popping up at the wrong moment
How do we use this feature?
Excel 97
Start the Windows Explorer
Go to the directory Program Files: Microsoft Office: Office: Actors
Rename the directory “Actors” to “Dead Actors”
Excel 2000
Go to Tools : Options : Edit and switch off „Provide feedback with animation“
CLEAN UP TEXT
Why do we need to know this?
Often clients have data on their mainframe. The best we can get for our PC is a text file dump. This trick will help us see through the data „mess“ we‘ve received.
How do we use this feature?
One easy method to split text into separate columns is the Data/Text to Column Wizard
Select the cells
Select Data/Text to Column
Check that Excel choose correct setting, change as needed
Be sure to supply the destination
Click finish
NOTE:
Be sure that enough empty columns for our conversion at the destination or Excel will OVERWRITE the contents of the cells
Split data appears in 2 columns.