الكورس مناسب لكل مستويات مستخدمي الاكسيل ( المبتديء والمحترف والمتوسط ) يشمل جميع اوامر ومعادلات الاكسيل – المعادلات البسيطه والبدائيه والمعادلات المعقده والتي يحتاجها المحترفين – يشمل ايضا العديد من تريكات الاكسيل التي يجهلها كثير من المستخدمين – اذا كنت لا تعلم في الاكسيل شيئا سوف
تخرج من الكورس بفضل الله محترف للاكسيل وعلي درايه تامه بكل تفاصيل الاكسيل .
– This course for all excel users levels ( primary and advanced levels ) including all excel details & functions & formulas .
– Throw the 70 Lectures you will find all your answers for any question which may face you when you are using excel .
– You will find many advanced tools ( Pivot table – Pivot chart – Pie Chart – Advanced Tables – Advanced equations – Advanced formulas )
– All Formatting Options
– All Insert options & Data options
– LECTURE 1
Why should I learn Excel
Excel Program interface
LECTURE 2
Create New File
Create New Template
Close Workbook
LECTURE 3
Create New Template
LECTURE 4
Customize your own tab
Add new tab and new group
LECTURE 5
Over Writing & Editing cell content
Editing formula & function
Using undo & Redo
Find & Replace
Goto
LECTURE 6
Font
Alignment
Number
LECTURE 7
Characteristics
Relative reference
Absolute reference
LECTURE 8
From where I can get speak cell option
Speak cell ( by row and by column )
Speak cell when I press enter
LECTURE 9
How to replace spaces to column
LECTURE 10
Insert Comments
Edit Comment
Delete comment
Show all comments
LECTURE 11
Copy & Paste
Paste special
Copying cells to one or many cells
Copying to different work sheets
using filling for quick copying
LECTURE 12
Filling & Drag Numbers / Days / Months / years
Custom options
Fill down & Right
LECTURE 13
Filling Series With Formatting
Filling Without formatting
Filling Aweek days
Filling Days
LECTURE 14
Flash Fill CTRL + E
LECTURE 15
Hide Column / Row
Freezing Panes
Splitting windows
LECTURE 16
Create Custom Views
Displaying Custom Views
Deleting Custom Views
LECTURE 17
Inserting / Deleting Cells
Inserting / Deleting Columns
Inserting / Deleting Rows
Resize Cells / Columns / Rows
LECTURE 18
Insert & Delete & Rename Worksheet
Copying & Moving Worksheets
Hide & Unhide Worksheet
Changing Worksheet tab Colour
Format after Grouping
Comparing 2 Worksheets
LECTURE 19
Revision
Case Study (Progress Report)
Case Study (Time Sheet)
Case Study (Products Coding)
LECTURE 20
Logical Test
If Function
Advanced IF Function ( Nested IF )
LECTURE 21
IF AND Function
IF error Function
Auditing Tools ( Displaying and removing dependent , precedent arrows )
Formula text ( To show the Cell formula as a text )
LECTURE 22
Trim Function ( to remove extra spaces )
Proper Function ( first letter capital )
Upper function ( text change to upper cases )
lower function ( text change to lower cases )
len function ( to calculate length of the text including spaces )
Left & right & mid & concatenate functions ( To right appreviation )
find & search & replace & substitute functions ( to change some letters )
Date functions ( today & now & day & month & year & date )
Add days & months & years
networking days
net working days internationaly
changing date format to show only day ( custom format for the date )
LECTURE 23
to calculate different in years between 2 dates ( =datedif(D1,D2,”Y”)
to calculate different in months between 2 dates ( =datedif(D1,D2,”M”)
to calculate different in days between 2 dates ( =datedif(D1,D2,”d”)
define & Edit & Delete name range ( formula – name manager – new )
Examples for name range Usage
LECTURE 24
add – subtract – divide – multiply
brackets usage
count – countA – count blank -count if
sumif – count if – avergaeif
sumifs – countifs – avergaeifs
LECTURE 25
( Sumproduct – Floor – Ceiling ) Formulas
vlookup Formula
vlookup Formula with data validation
vlookup with Name range
vlookup with approximate match
vlookup with trim ( 2 cases )
LECTURE 26
Hlookup Formula
Vlookup Formula rules ( unique & left to right & belong to 1st column )
Lookup Formula ( horizontal & vertical & approximate & result belong to last col & result vector )
LECTURE 27
Index & Match formulas
lock & unlock cell
protect the sheet
LECTURE 28
Protect Workbook
hyperlink to a sheet
hyperlink to a file
hyperlink to a web page
page setup for printing ( margins – orientation – print area – print size – page break – background – print titles )
LECTURE 29
Header & footer ( page number & date & file name )
Data Sorting ( Numbers – Text – dates – Multicolumn sorting – color sorting )
advanced sort
advanced filter
advanced filter error
LECTURE 30
check box – Data filtering – Number filtering – Text filter – Search – copy & paste filtered data
Create table – Sort – Sort by color – design tab – table style options
LECTURE 31
Advanced table ( rename table – resize table – slicer for table – remove duplicates – convert table to range )
Subtotals ( subtotal option – hide all data except subtotal –
LECTURE 32
Charts type ( column chart – Bar chart – Pie chart – X Y scatter chart – sparklines formatting )
Charts elements ( chart area – plat area – series – data labels – vertical and horizontal axes – gridlines – chart titles )
Chart format & Options
format chart ( Double click )
LECTURE 33
Bar Chart
Pie Chart
Column Vs Line Chart
LECTURE 34
Spark line chart
Pivot chart
LECTURE 35
Revision 1
Create & Close Files
Create Template
Tabs & Groups
Editing Data
Formatting in Excel
characteristics of cells
Speak Cells
LECTURE 36
Revision 2
Conditional formatting
format cell options
spelling check
Text to column
Comments
Copying and Filling
Advanced Filling
sheet protection
Work sheets Views
Comparing Files
LECTURE 37
Revision 3
Logical test
IF function
Advanced if function
IF AND
IF ERROR
Auditing Tools
Formula text
Evaluate formula
LECTURE 38
Revision 3
Trim Function ( to remove extra spaces )
Proper Function ( first letter capital )
Upper function ( text change to upper cases )
lower function ( text change to lower cases )
len function ( to calculate length of the text including spaces )
Left & right & mid & concatenate functions ( To right appreviation )
find & search & replace & substitute functions ( to change some letters )
Date functions ( today & now & day & month & year & date )
Add days & months & years
networking days
net working days internationaly
changing date format to show only day ( custom format for the date )
Header and Footer & Data sorting and advanced filtering
Data Filtering & Creating table
Table advanced option & Subtotal
Charts & Chart Format
Pivot Chart
Lecture 39
Excel Tricks 1 CTRL + E
Lecture 40
Excel Tricks 2 CTRL + E
Lecture 41
Excel Tricks 3 CTRL + ; & Ctrl + E
Lecture 42
Excel Tricks 4 Custom List for dragging down
Lecture 43
Excel Tricks 5 Select Multi Sheets and make your work
Lecture 44
Quick access analysis tool
Lecture 45
Filter – table
Lecture 46
Data Validation ( Drop down list data validation )
Lecture 47
Auto fit columns & Rows
Lecture 48
Transpose ( تقلب الصف عمود والعمود صف )
Lecture 49
Remove duplicates
Lecture 50
Goal Seek ( Data – what if – goal seek ) ( محتاج احقق قيمه معينه )
Lecture 51
Vlockup
Lecture 52
Paste Sepacial
Lecture 53
changing chart columns to pattern or picture
Lecture 54
IF Function
Lecture 55
Insert ScreenShot ( insert – illustration – screenshot )
Lecture 56
Absolute Cell Reference
Lecture 57
Show Formula ( CTRL + ˜ )
Lecture 58
Convert Text to columns
Lecture 59
Conditional formatting
Lecture 60
Power Pivot
Lecture 61
Freeze Panes
Lecture 62
CTRL Arrow Ley to select last cell
Lecture 63
3D reference (جمع نفس الخليه في كل الشيتات )
Lecture 64
forecast sheet
Lecture 65
SUMIFS ـجميع القيم التي امام قيم معينه ( اكثر من criteria )
Lecture 66
IF ERROR – Advanced Transpose ( Live Edit ) تحديث القيم بعد عمل transpose عند تغيير القيم الاصليه
Lecture 67
Slicers
Lecture 68
Scenario Manager
Lecture 69
Convert Function
Lecture 70
Convert Currency Live