Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Preface
Chapter 1: Introduction
Microsoft Excel at a Glance
Menus
File Menu
Home Menu
Insert Menu
Page Layout Menu
Formulas Menu
Data Menu
Review Menu
View Menu
Cell, Worksheet, & Workbook
Chapter 2: Basic Data Types, Data Consistency, and Analysis Aided Tools in Excel
Basic Data Types
Number
Text
Boolean
Formula
Data Consistency
Naming Range
First approach to name a range
The second approach to name a range
Analysis Aided Tools
Insert Sheets
Rename Sheets
Sheet Tab Color
Duplicate Worksheet
Delete Worksheet
Linking Worksheet
Hide / Unhide Rows and Columns
Merging Cells
To Insert, Format, and Delete Cells, Rows or Columns
Chapter 3: Data Formatting
Format Data as Excel Tables
Create a Table
Benefits of Excel Tables
Format for printing and Email
Remove Duplicates
Text to Columns
Flash Fill
Data Validation
To Create a Drop Down List
Subtotal
To Insert Subtotal
To Remove Subtotal
Group and Outline
How to Insert Group and Outline?
To Remove Grouping
Chapter 4: Sorting and Filtering
Top to Bottom Sorting
Left to Right Sorting
Sort by Color
Filter
To Remove Filter
Advance Filtering
Chapter 5: Charts in Excel
Column Chart
Clustered Column
Stacked Column
100% Stacked Column
Line Chart
Bar Chart
Pie Chart
Combo Chart
Chapter 6: Business Analysis Aided Tools
Conditional Formatting
Built-in Conditional Formatting
Logical Formula
Conditional Formatting with Data Validation
Two ways Conditional Formatting
Three-ways Conditional Formatting
Clear Rules
Freezing Panes
Windows Splitting
Protect Worksheet and Workbook
How to Protect Cells that Contain formulas?
Chapter 7: Logical Function in Excel
Basics of IF Function
Combining IF with AND
Combining IF with OR
Nested IF Function
COUNTIF and COUNTIFS
COUNTIF
COUNTIFS
SUMIF and SUMIFS
SUMIF
SUMIFS
AVERAGEIF and AVERAGEIFS
AVERAGEIF
AVERAGEIFS
SUMPRODUCT
IFERROR
Chapter 8: Performing Lookup in Excel
LOOKUP Function and its Application
LOOKUP - Vector Form
LOOKUP - Array Form:
VLOOKUP and its Application
VLOOKUP for Exact Match
VLOOKUP for Approximately Match
HLOOKUP and its Application
Looking for Exact MATCH using HLOOKUP
Looking for Approximately Match using HLOOKUP
Chapter 9: Power Excel Data Functions
MATCH Function
INDEX Function
INDEX MATCH Function
CHOOSE Function
Chapter 10: Arrays in Excel
An Array and array Formula
Creating and Using an Array Formula
Conditional Evaluation in an Array Formula
Chapter 11: Excel Math Functions
The OFFSET Function
RAND and RANDBETWEEN
INDIRECT Function
ROUND, ROUNDUP, and ROUNDDOWN
ROUND
ROUNDUP
ROUNDDOWN
CEILING and FLOOR
COUNT, COUNTBLANK and COUNTA
RANK, RANK.AVG, and BREAK DUPLICATE
RANK
RANK.AVG
BREAK DUPLICATE
DATE, TODAY, DAY, MONTH, YEAR and DATEVALUE
TODAY
DAY
MONTH
YEAR
DATEVALUE
WORKDAY and WORKDAY.INTL
WORKDAY
WORKDAY.INTL
NETWORKDAYS and NETWORKDAYS.INTL
NETWORKDAYS
NETWORKDAYS.INTL
Database Functions in Excel
DAVERAGE
DCOUNT
DCOUNTA
DGET
DMAX
DMIN
DSUM
DPRODUCT
DSTDEV
DSTDEVP
DVAR
DVARP
Chapter 12: Excel Text Functions
LEN and TRIM Text Functions
LEN
TRIM
Using LEFT, MID and RIGHT for String Extraction
LEFT
MID
RIGHT
Use Extracting functions with SEARCH
Joining Data with CONCATENATE or AMPERSAND “&”
CONCATENATE
Ampersand
Changing the Case of Text in Cells (UPPER, LOWER and PROPER)
UPPER
LOWER
PROPER
FIND, REPLACE, SUBSTITUTE, and REPT
FIND
REPLACE
SUBSTITUTE
REPT
Chapter 13: IS Functions
ISNUMBER, ISEVEN, ISODD
ISNUMBER
ISEVEN
ISODD
ISTEXT and ISBLANK
ISTEXT
ISBLANK
ISFORMULA, ISLOGICAL
ISFORMULA
ISLOGICAL
Error Checking with ISERR, ISERROR, ISNA
Error Checking with ISERR
Error Checking with ISERROR
Error Checking with ISNA
Chapter 14: Sparklines
Create a Sparklines in Excel
Change the Design of Sparklines
Removing Sparklines from a Sheet
Chapter 15: Scenario Manager, Goal Seek, and Solver
Scenario Manager and its Application
Setting up Scenario and Entering Values to carry out What-If-Analysis
Getting a summary of all Scenarios
Goal Seek
Using Goal Seek to carry out What –If-Analysis
SOLVER
Activating Solver Add In
Using SOLVER to Carry out What if Analysis
Add constraints into Solver problem
Chapter 16: PivotTable, Slicer, and Timeline tool In Excel
PivotTable
Organize your source data
Create a PivotTable
Arrange / Rearrange Fields in a PivotTable
Show Different Calculations in PivotTable
Summarize Values By
SHOW VALUES AS
Format PivotTable Numbers
Applying Pivot Table Styles
Disabling and Enabling Grand Totals
Applying Report Layout
Making Use of the Report Filter Option
How to Refresh a PivotTable?
Grouping
Moving a Pivot Table
Removing a Pivot Table
The Slicer Tool
The Timeline
Chapter 17: Pivot Charts in Excel
Creating a Pivot Chart
Changing Chart Types Formats and Layouts
Filtering a Pivot Chart
Hiding Pivot Chart Field Buttons
Moving a Pivot Chart between Sheets
Deleting a Pivot Chart (With Care)
Chapter 18: CORRELATION and REGRESSION ANALYSIS
Activating Analysis ToolPak Add In
CORRELATION COEFFICIENT
REGRESSION ANALYSIS
R Square
Significance F and P-Values
Coefficients
Chapter 19: Macros in Excel
What is a Macro?
Activating Developer Tab
Creating Storing and Running your First Macro
Invoke Macro with Keyboard Shortcut
Using Form Button to Invoke Macro
Chapter 20: Power Query and Power Pivot
How to Activate Power Pivot and Power Query
Power Query
Importing Data Using Power Query
Power Pivot
Importing Data to Power Pivot
Bonus: Keyboard Shortcuts in Excel
← Prev
Back
Next →
← Prev
Back
Next →