EXCEL- BASICS 2 Flashcards
LOGIC AND ERRORS
CENTRALLY ALIGNED
NOT EQUAL OPERATOR
<>
AND FUNCTION
ALL LOGIC MUST BE TRUE
OR FUNCTION
ONE LOGIC MUST BE TRUE
LOGICAL BUILT-IN FUNCTIONS
- ISTEXT
- ISNUMBER
- ISLOGICAL
- ISERROR
- ISERR
- ISNA
- ISBLANK
- ISNONTEXT
ISERR VS. ISERROR
ISERR- NO #N/A
TYPES OF ERROR
- # N/A
- # VALUE!
- # REF!
- # DIV/0!
- # NUM!
- # NAME?
- # NULL!
N/A
NOT AVAILABLE
REF!
REFERENCE PROBLEM LIKE DELETED
DIV/0!
DIVIDE BY 0
NUM!
TOO LARGE OR SMALL NUMBER
VALUE!
WRONG OPERATIONS ON TEXT OR NUMBERS
NULL!
NO INTERSECTION
NAME?
WHAT NAME YOU HAVE IN A FORMULA
INSTEAD OF DRAGGING FOR AUTOMATIC FILLING
DOUBLE CLICK ON THE CORNER
ADVANTAGE OF IF FUNCTION
REPLACE TRUE/FALSE ANSWERS
SPELL CHECK
IN EDIT MODE ONLY
CONDITIONAL FORMATTING
UNDER HOME-> STYLE-> NEW RULE
DATE FUNCTIONS
- TODAY
- NOW
- YEAR
- MONTH
- DAY
- DATE
- EDATE
TODAY
- NO ARGUMENT
2. TODAY’S DATE
NOW
- NO ARGUMENT
2. TODAY’S DATE & TIME
ADVANTAGE OF DATE
DAY ARGUMENT CAN BE GIVEN ANY VALUE
EDATE
ADDING OR SUBTRACTING MONTHS
TRIM FUNCTION
Get Rid of All Spaces Except for Single Spaces Between Words
PROPER FUNCTION
GET RID OF CAPITAL LETTERS EXCEPT FOR FIRST LETTERS OF EACH WORD
FIND AND SEARCH FUNCTION
- FIND CASE-SENSITIVE
- SEARCH NOT CASE-SENSITIVE
- BOTH FINDS POSITION
- FIND_TEXT IN DOUBLE QUOTES
SUBSTITUTE FUNCTION
- REPLACING ONE TEXT WITH ANOTHER TEXT
2. INSTANCE_NUM MEANS WHICH OCCURRENCE TO REPLACE; BY DEFAULT IT REPLACE ALL OCCURRENCE
REPLACE FUNCTION
REPLACE THE PART WITH SOMETHING ELSE
LEFT FUNCTION
GET LEFT PART ONLY
TEXTS CONCATENATED USING
- &
2. CONCATENATE FUNCTION
DATA SETUP
- FIELD NAMES
- NO BLANK FIELD NAMES
- NO BLANK COLUMNS (FIELDS)
- NO BLANK ROWS (RECORDS)
- BLANK ROW/ COLUMNS SEPARATING ONE LIST FROM OTHER
NEED OF DATA SETUP
EASY TO SORT, FILTER, PIVOT, SUBTOTAL
BLANK ROW/ COLUMNS SEPARATING ONE LIST FROM OTHER
OTHERWISE BECOMES PART OF THE DATA EARLIER
CTRL+*
HIGHLIGHTS THE CURRENT DATA SET
CTRL+T OR CTRL+L
INSERT TABLE
TABLE
- FORMATTING
- SORT AND FILTER APPEARS AUTOMATICALLY
- CAN “CONVERT TO RANGE” IN HOME RIBBON
- USE OF TAB ON LAST RECORD TO ADD NEW RECORD
- USE OF NAME OF TABLE AND USE OF SQUARE BRACKETS TO GET THE APPROPRIATE FIELD IN THAT TABLE
- NAME CAN BE USED IN OTHER WORKSHEETS AS WELL
FORMATTING OF TABLES
- CAN HAVE NEW STYLE
- IN CUSTOM IN DESIGN->QUICK STYLES OR IN HOME-> FORMAT AS TABLE
- CAN BE ALSO SET AS DEFAULT
CTRL+HOME
JUMPS TO A1
TABLE IS DYNAMIC
ANY FORMULA AUTOMATICALLY UPDATES IF NEW RECORD IS ADDED LIKE IN SUM, CHARTS, PIVOT ETC.
CTRL+F3
NAME MANAGER
SHIFT+F11
NEW WORKSHEET
SORT
- CLICK ON ONE CELL IN DATA LIST
- SORT IN HOME OR DATA RIBBON
- ALL OTHER COLUMNS ARE ALSO ARRANGED
- ONLY USE ONE CELL TO START SORT OR EXPAND THE SELECTION
CTRL+SHIFT+~
GETS NUMBER TO GENERAL FORMAT
TO GET THE ORIGINAL DATA AFTER SORTING
INSERT ORDER FIELD
MAJOR SORT & MINOR SORT
- FIRST WE HAVE THE MAJOR SORT
- THEN SOME MINOR SORT IS DONE WITHIN MAJOR SORT IN OTHER FIELD
- CAN DO IT IN CUSTOM SORT
MAJOR SORT & MINOR SORT
- WITHOUT CUSTOM SORT, MINOR SORT IS FIRST
2. WITH CUSTOM SORT, MINOR SORT IS LAST
HORIZONTAL SORT
USING CUSTOM SORT
SORTING CAN BE DONE BY COLORS ALSO
- WITH TABLE
2. WITHOUT TABLE
ADDING A COLUMN TO A TABLE
- AUTOMATICALLY GENERATES THE FIELD
2. AUTOMATICALLY FILLS WHOLE WITHOUT DRAGGING OR DOUBLE CLICKING
RANDOM SORT
USING RAND () FUNCTION
FILTERING
- BY HIDING THE ROWS- ROWS’ HEADER TURNS BLUE
- FILTER SYMBOL ON FIELD
- COPIES ONLY ROWS WHICH ARE VISIBLE
- CLEAR FILTER BUTTON- EVEN MULTIPLE FILTERING
- BY COLOR ALSO
- MULTIPLE FILTERING POSSIBLE
- TEXT FILTERS, DATE FILTERS, NUMBER FILTERS, CUSTOM FILTERS
CTRL+SHIFT+L
FILTER
PIVOT TABLE
- COLUMN HEADER
- ROW HEADER
- PIVOT TABLE OPTION: CLASSICAL DISPLAY VERSUS 2007 DISPLAY
- DRAG OR RIGHT CLICK
- CAN SUMMARIZE BY MONTHS ETC. AUTOMATICALLY WITHOUT USING DATE FUNCTIONS: RIGHT CLICK & GROUP
- PIVOT TABLE OPTIONS TOOLS
- DESIGN- PIVOT TABLE STYLE OPTIONS
- DOUBLE CLICK ON A CELL IN PIVOT AND YOU GET ALL THE DATA RELATED TO THAT CELL IN A NEW SHEET
- EASY TO PIVOT
VALUE FIELD SETTINGS
E.G. TO CHANGE SUM OF SALES AS WELL AS FORMATTING
