This is summary of my favorite features that I like to use in Excel.
I find that using these advanced features and functions makes spreadsheets much easier to make and use.
Topics covered include:
- Keyboard shortcuts
- Equation functions
- Add-Ins
- Automatic importing of "External Data" from the Internet
- Macros
- Visual Basic editing of Macros
Keyboard shortcuts:
I prefer to use these keyboard shortcuts instead of wasting time hunting for the icons with the mouse and developing a sore "mouse agrivated" wrist.
Used in many programs including Excel:
Ctrl+A Select All
Ctrl+C Copy
Ctrl+V Paste
Ctrl+X Cut
Ctrl+Alt+V Paste Special
Ctrl-B Bold
Ctrl-I Italics
Ctrl-U Underline
Ctrl+Z Undo (last typing)
Ctrl+Y Redo (undo's last undo)
Ctrl+F Find
Ctrl+P Print
Ctrl+S Save
Excel short cuts:
Ctrl + Add row or column
Ctrl - Delete row or column
Ctrl; Date
Ctrl Shift; Date & Time
Ctrl ‘ Copy cell from above
Ctrl Space Select column
Ctrl Home Select cell A1
Ctrl End Select last row and column cell
Ctrl Page Down Move to the next sheet
Ctrl Page Up Move to the previous sheet
Ctrl K Insert Hyperlink
Shift arrow Select next cells
Shift End arrow Select to end
Shift Space Select row
Alt = Autosum select cells
Alt 167 (Adds º such as 150º) using number pad to type numbers
while holding Alt
Alt Enter Start a new line in the same cell
=today() Puts today's Date in selected cell
=now() Puts Date and Time in selected cell
Double click cell edge Jumps to beginning or end
F1 Help
F2 Edit cell
F4 Repeat last action
F7 Spell check
Shift-clicking the worksheet tabs
Select multiple worksheets in Excel by at the bottom of the screen
New entries go onto all selected worksheets
Equation Functions:
Excel has an extensive and very powerful set of functions that cover these categories:
Add-in and automation
Cube
Database and list management
Date and time
Engineering
Financial
Information
Logical
Lookup and reference
Math and trigonometry
Statistical
Text and data
Below is the link to the list of Microsoft Excel functions:
Below is the link to the list of Microsoft Excel functions:
http://office.microsoft.com/en-us/excel-help/list-of-worksheet-functions-by-category-HP005204211.aspx
There are two easy ways to find a function-
- Click on the fx symbol to the left of the formula bar
- Press F1 to go to help
Example: following the fx steps after searching for average and count, you can create the simple equations:
=AVERAGE(A12:A17)
which puts the average of the values in cells A12 thru A17 in the cell where the equation is located
=Count(A12:A17)
which counts how many cells have data in the range of A12 thru A17
Other very basic functions include: SUM, MAX, MIN,
There are hundreds of functions, here are some I have used regularly:
Date and time functions
SECOND Converts a serial number to a second
TIME Returns the serial number of a particular time
TODAY Returns the serial number of today's date
WEEKDAY Converts a serial number to a day of the week
WEEKNUM Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY Returns the serial number of the date before or after a specified number of workdays
YEAR Converts a serial number to a year
Financial functions
FV Returns the future value of an investment
IPMT Returns the interest payment for an investment for a given period
IRR Returns the internal rate of return for a series of cash flows
NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
PV Returns the present value of an investment
Information functions
CELL Returns information about the formatting, location, or contents of a cell
ISERROR Returns TRUE if the value is any error value
ISNUMBER Returns TRUE if the value is a number
ISTEXT Returns TRUE if the value is text
SECOND Converts a serial number to a second
TIME Returns the serial number of a particular time
TODAY Returns the serial number of today's date
WEEKDAY Converts a serial number to a day of the week
WEEKNUM Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY Returns the serial number of the date before or after a specified number of workdays
YEAR Converts a serial number to a year
Financial functions
FV Returns the future value of an investment
IPMT Returns the interest payment for an investment for a given period
IRR Returns the internal rate of return for a series of cash flows
NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate
PV Returns the present value of an investment
Information functions
CELL Returns information about the formatting, location, or contents of a cell
ISERROR Returns TRUE if the value is any error value
ISNUMBER Returns TRUE if the value is a number
ISTEXT Returns TRUE if the value is text
Logical functions
AND Returns TRUE if all of its arguments are TRUE
IF Specifies a logical test to perform
NOT Reverses the logic of its argument
OR Returns TRUE if any argument is TRUE
Lookup and reference functions
ADDRESS Returns a reference as text to a single cell in a worksheet
HLOOKUP Looks in the top row of an array and returns the value of the indicated cell
HYPERLINK Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDIRECT Returns a reference indicated by a text value
LOOKUP Looks up values in a vector or array
TRANSPOSE Returns the transpose of an array
VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
Math and trigonometry functions
ABS Returns the absolute value of a number
ATAN2 Returns the arctangent from x- and y-coordinates
COS Returns the cosine of a number
FLOOR Rounds a number down, toward zero
RANDBETWEEN Returns a random number between the numbers you specify
ROUNDDOWN Rounds a number down, toward zero
ROUNDUP Rounds a number up, away from zero
SIN Returns the sine of the given angle
SQRT Returns a positive square root
SUM Adds its arguments
SUMIF Adds the cells specified by a given criteria
TAN Returns the tangent of a number
ATAN2 Returns the arctangent from x- and y-coordinates
COS Returns the cosine of a number
FLOOR Rounds a number down, toward zero
RANDBETWEEN Returns a random number between the numbers you specify
ROUNDDOWN Rounds a number down, toward zero
ROUNDUP Rounds a number up, away from zero
SIN Returns the sine of the given angle
SQRT Returns a positive square root
SUM Adds its arguments
SUMIF Adds the cells specified by a given criteria
TAN Returns the tangent of a number
Statistical functions
AVERAGE Returns the average of its arguments
CONFIDENCE Returns the confidence interval for a population mean
COUNT Counts how many numbers are in the list of arguments
COUNTIF Counts the number of non blank cells within a range that meet the given
MAX Returns the maximum value in a list of arguments
MEDIAN Returns the median of the given numbers
PERCENTILE Returns the k-th percentile of values in a range
STDEV Estimates standard deviation based on a sample
TTEST Returns the probability associated with a Student's t-test
VAR Estimates variance based on a sample
CONFIDENCE Returns the confidence interval for a population mean
COUNT Counts how many numbers are in the list of arguments
COUNTIF Counts the number of non blank cells within a range that meet the given
MAX Returns the maximum value in a list of arguments
MEDIAN Returns the median of the given numbers
PERCENTILE Returns the k-th percentile of values in a range
STDEV Estimates standard deviation based on a sample
TTEST Returns the probability associated with a Student's t-test
VAR Estimates variance based on a sample
Text functions
CHAR Returns the character specified by the code number
EXACT Checks to see if two text values are identical
LEFT, LEFTB Returns the leftmost characters from a text value
TEXT Formats a number and converts it to text
VALUE Converts a text argument to a number
Here are some equation examples:
Adds three groups of sums:
=SUM(F4:F5)+SUM(F8:F12)+SUM(F15:F22)
This takes the value in cell H34 then subtracts H149 from it, the INDIRECT allows you to insert or delete cells around cell H34 without causing reference errors:
=INDIRECT("H34")-H149
This looks at the test in cell A18 and finds the same text in the range of cells G4 to G24 then returns the value from the same row and the cell in the 5th column of the range G4 to K24:
=VLOOKUP(A18,G4:K24,5,FALSE)
Counts the numbers of words beginning with "m" in the column X:
=COUNTIF(X:X,"m*")
Calculation of the 95% confidence interval minimum:
=IF((J28-1.96*SQRT((J28*(1-J28))/H29))<0,0,(J28-1.96*SQRT((J28*(1-J28))/H29)))
Calculation of the standard deviation of Voltages:
=DSTDEVP(dataVMED,"VMED",AB1:AB2)
Calculation of the Cpk finding the min of two values:
=MIN((Z2-I14)/(3*I16), (I14-AA2)/(3*I16))
Equations can also easily pull data from other spreadsheets on a network using "Paste Link" and then editing the equations:
='\\Serv-003\projectmgr\[DesignQueue.xls]DEQ'!G8
Sometimes to add logic to check for errors to show the correct calculations:
=IF(IF(ISERROR(VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,3,FALSE)),"",VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,3,FALSE))=0,"",IF(ISERROR(VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,4,FALSE)),"",VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,4,FALSE)))
Logic in equations can be quite complex, here is a long equation (one of 50,000 in this spreadsheet) I created to determine the critical path and capacity in a production process:
=IF(OR(AND($C22="Prod 1",MAX('Assembly'!$G22,'Drilling'!$K22)+$A$12<=J$14),AND($C22="Prod 2",MAX('Assembly'!$G22,'Drilling 2'!$K22)+$A$12<=J$14)),IF(J$16>=SUM(J$17:J21)+(IF(SUM($I22:I22)>=$H22,0,(IF($H22<=J$15,$H22,(IF($H22-SUM($I22:I22)>J$15,J$15,($H22-SUM($I22:I22)))))))),(IF(SUM($I22:I22)>=$H22,0,(IF($H22<=J$15,$H22,(IF($H22-SUM($I22:I22)>J$15,J$15,($H22-SUM($I22:I22)))))))),0),0)
EXACT Checks to see if two text values are identical
LEFT, LEFTB Returns the leftmost characters from a text value
TEXT Formats a number and converts it to text
VALUE Converts a text argument to a number
Here are some equation examples:
Adds three groups of sums:
=SUM(F4:F5)+SUM(F8:F12)+SUM(F15:F22)
This takes the value in cell H34 then subtracts H149 from it, the INDIRECT allows you to insert or delete cells around cell H34 without causing reference errors:
=INDIRECT("H34")-H149
This looks at the test in cell A18 and finds the same text in the range of cells G4 to G24 then returns the value from the same row and the cell in the 5th column of the range G4 to K24:
=VLOOKUP(A18,G4:K24,5,FALSE)
Counts the numbers of words beginning with "m" in the column X:
=COUNTIF(X:X,"m*")
Calculation of the 95% confidence interval minimum:
=IF((J28-1.96*SQRT((J28*(1-J28))/H29))<0,0,(J28-1.96*SQRT((J28*(1-J28))/H29)))
Calculation of the standard deviation of Voltages:
=DSTDEVP(dataVMED,"VMED",AB1:AB2)
Calculation of the Cpk finding the min of two values:
=MIN((Z2-I14)/(3*I16), (I14-AA2)/(3*I16))
Equations can also easily pull data from other spreadsheets on a network using "Paste Link" and then editing the equations:
='\\Serv-003\projectmgr\[DesignQueue.xls]DEQ'!G8
Sometimes to add logic to check for errors to show the correct calculations:
=IF(IF(ISERROR(VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,3,FALSE)),"",VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,3,FALSE))=0,"",IF(ISERROR(VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,4,FALSE)),"",VLOOKUP($D19,'Engr Rel'!$D$6:$H$76,4,FALSE)))
Logic in equations can be quite complex, here is a long equation (one of 50,000 in this spreadsheet) I created to determine the critical path and capacity in a production process:
=IF(OR(AND($C22="Prod 1",MAX('Assembly'!$G22,'Drilling'!$K22)+$A$12<=J$14),AND($C22="Prod 2",MAX('Assembly'!$G22,'Drilling 2'!$K22)+$A$12<=J$14)),IF(J$16>=SUM(J$17:J21)+(IF(SUM($I22:I22)>=$H22,0,(IF($H22<=J$15,$H22,(IF($H22-SUM($I22:I22)>J$15,J$15,($H22-SUM($I22:I22)))))))),(IF(SUM($I22:I22)>=$H22,0,(IF($H22<=J$15,$H22,(IF($H22-SUM($I22:I22)>J$15,J$15,($H22-SUM($I22:I22)))))))),0),0)
Add-Ins:
An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (Visual Basic (VBA) in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. The file contains code (Visual Basic (VBA) in the case of an .xla Add-In) that adds additional functionality to Excel, usually in the form of new functions.
There are some standard Excel Add-Ins included with Excel that can be turned on to add great functionality.
To enable Add-Ins:
click on the Upper Left corner icon in Excel 2007
click on Excel Options on the bottom of the window
click on Add-Ins
Click on Excel Add-Ins in the Manage box on the bottom of the screen, this set is useful:
When there is a set of data to do standard statistical analysis on, click on Data Analysis, then Descriptive Statistics:
Select the input range, output range, and what statistics to calculate etc...
Saves a lot of time creating equation, here is the output:
Automatic importing of "External Data" from the Internet:
Here is how to automatically pull data, such as stock prices, directly from the Internet using Excel 2007.
Click on the Data tab
Click Existing Connections in the Get External Data section
Click on MSN MoneyCentral Investor Major Indices
Click on MSN MoneyCentral Investor Major Indices
The following data is linked to the spreadsheet:
Select data From the Web and pull in a section of a web page such as http://www.google.com/finance that has the numbers to link too, click the table to link to, click Import at the bottom:
In the spreadsheet the cell range data is shown and is linked to the Imported Web page:
In the spreadsheet the cell range data is shown and is linked to the Imported Web page:
Set how the data is refreshed by clicking on Connections on the Data tab then Properties:
Macros:
Macros enable users to quickly do repeated steps by recording a sequence then playing back the sequence of steps later when needed.
The steps to record a Macro:
Click on the Developer tab in Excel 2007
Click on Record Macro
Type the name of your Macro
Type in a letter in the shortcut key box like "D"
(do not use a letter that use for other functions like Ctrl+S)
Select were to store the Macro, Personal Macro Workbook allows the use of the Macro on any spreadsheets on the computer.
Perform the steps to record
Click the developer tab, click Stop Recording, the Macro is recorded and can now be used
Run can also run the Macro buy clicking on the Developer tab, clicking on Macros, select the Macro, the click Run
Visual Basic editing of Macros:
Now that a Macro is created, edit it and add functionality:
On the Developer tab in Excel 2007, click Visual Basic- tis is the Excel Visual Basic Editor.
The Macros are saved are under the Modules folder, click and find one.
The Keyboard Shortcut at the top of the file such as:
Sub FirsttryMacro()
' ' FirsttryMacro Macro
' Keyboard Shortcut: Ctrl+d
The Subroutine commands are recognizable and the commands can be edit easily. Change a few statements, do File Save, then click the Window Close, or the Excel Icon to close the Visual Basic screen. Try modifying a Macro.
Copy and paste commands from different Macros creating a more complex Macro.
There is also a help box on the tool bar.
Save a Macro, as an Add-In, by Saving and selecting Save as type: .xlam or .xla .
