Muutke küpsiste eelistusi

Advanced Excel Success: A Practical Guide to Mastering Excel 1st ed. [Pehme köide]

3.67/5 (6 hinnangut Goodreads-ist)
  • Formaat: Paperback / softback, 349 pages, kõrgus x laius: 254x178 mm, kaal: 699 g, 358 Illustrations, black and white, 1 Paperback / softback
  • Ilmumisaeg: 08-Dec-2020
  • Kirjastus: APress
  • ISBN-10: 1484264665
  • ISBN-13: 9781484264669
Teised raamatud teemal:
  • Pehme köide
  • Hind: 58,13 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Tavahind: 68,39 €
  • Säästad 15%
  • Raamatu kohalejõudmiseks kirjastusest kulub orienteeruvalt 3-4 nädalat
  • Kogus:
  • Lisa ostukorvi
  • Tasuta tarne
  • Tellimisaeg 2-4 nädalat
  • Lisa soovinimekirja
Advanced Excel Success: A Practical Guide to Mastering Excel 1st ed.Suurem pilt
  • Formaat: Paperback / softback, 349 pages, kõrgus x laius: 254x178 mm, kaal: 699 g, 358 Illustrations, black and white, 1 Paperback / softback
  • Ilmumisaeg: 08-Dec-2020
  • Kirjastus: APress
  • ISBN-10: 1484264665
  • ISBN-13: 9781484264669
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781484264669.html
Explore advanced skills in Excel and gain an amazing array of tricks and tools to increase your productivity. This book discusses new techniques such as power functions, chart tricks, and many more to master Excel.





Advanced Excel Success starts with a few useful data tools in Excel followed by advanced formulas that will help you increase productivity. Here, you will learn power functions that aggregate, return ranges, and much more. Further, you will look at custom formatting tricks along with advanced charting tricks. These include automatically changing the color of key metrics, dynamically sorting chart data, and building creative labels.





Next, you will understand the role of Power Query which is one of the most important upgrades in Excel. Power Query is the Microsoft Data Connectivity and Data Preparation technology that enables business users to seamlessly access data stored in hundreds of data sources and reshape it to fit their needs, with an easyto-use, engaging, and no-code user experience. Finally, you will learn Power Pivot which is a distinct feature in Excel that goes beyond spreadsheets. 





After reading this book, you will be well equipped to work on Excel with its advanced features. 





What You Will Learn









Work with the most useful data tools  Understand formulas and the ten power functions Use advanced chart and formatting tricks and techniques for dynamic and effective visuals Work with power tools

Who This Book Is For 





Excel users looking to take the next step to expert level.
About the Author xi
About the Technical Reviewer xiii
Acknowledgments xv
Introduction xvii
Chapter 1 Excel Tricks and Data Tools
1(56)
Fill Techniques
1(9)
Generate a Number Series
1(2)
Additional Series Options
3(4)
The Incredible Flash Fill
7(3)
Take Advantage of Custom Lists
10(5)
Change Multiple Worksheets at the Same Time
15(3)
Advanced Find and Replace Tricks
18(15)
Find and Replace in the Entire Workbook
19(2)
Edit Your Formulas Fast
21(1)
Change Cell Formatting
22(2)
Remove Values
24(3)
Remove Asterisks from a Range
27(2)
Replace Line Breaks Easily
29(2)
Delete Every Nth Row Quickly
31(2)
Quickly Find All Cells That Meet Criteria
33(11)
Remove Blank Rows
34(2)
Fill Blank Cells withO
36(2)
Fill Blank Cells with the Cell Value Above
38(3)
Format All Cells Containing Formulas
41(1)
Compare Two Columns by Identifying Row Differences
42(2)
The Secrets of Text to Columns
44(7)
Convert Text to Number
44(1)
Converting Date Formats
45(3)
Converting International Number Formats
48(1)
Convert Values with a Trailing Minus Sign
49(2)
What Is So Special About Paste Special?
51(6)
Convert Positive Values to Negative
51(1)
Remove Formulas from a Cell
52(2)
Repeat Column Widths
54(1)
Pasting with Charts
54(3)
Chapter 2 The Ten Power Functions of Excel
57(80)
Functions Are Beautiful
58(1)
Using Tables
58(4)
Format a Range as a Table
58(3)
Advantages to Using Tables
61(1)
Dynamic Arrays - The Game Has Changed
62(4)
SUMPRODUCT
66(9)
Simple Example of SUMPRODUCT
67(1)
Sum and Count with Multiple Criteria
67(3)
Understanding the Logic
70(1)
More Complex Examples
71(2)
Alternative to Array Formulas
73(2)
Unique
75(8)
Create a Distinct List
76(1)
Dynamic List for Data Validation
77(3)
Count of Distinct and Unique Entries
80(3)
SORT and SORTBY
83(6)
SORT Function
83(1)
Sort a Distinct List
83(1)
Sort Multiple Columns
84(2)
Sortby Function
86(1)
Sort Multiple Columns with SORTBY
86(1)
Sort by Column Not in the Sorted Array
87(1)
Sort Products by Sales Totals
88(1)
Filter
89(12)
Filter Function Example
90(4)
Dependent Drop-Down List
94(3)
Shrinking Drop-Down List
97(3)
Aggregate FILTER Results
100(1)
Index
101(8)
Index and MATCH for Versatile Lookups
102(4)
Return the Last X Values from a Row/Column
106(3)
Choose
109(8)
Pick a Formula from a List
110(5)
Choose Specific Columns for FILTER
115(2)
Xlookup
117(7)
Quick XLOOKUP Example
118(1)
Robust Range Lookup
119(1)
Multiple Column Lookup
120(1)
Return the Last Match
120(2)
Two-Way Lookup
122(1)
Dynamic Sumifs Columns
123(1)
Indirect
124(6)
Return Table Based on Drop-Down Selection
125(4)
Reference Other Sheets with INDIRECT
129(1)
Switch
130(7)
Testing Text Values
131(2)
Using Logical Operators
133(1)
SWITCH and Other Functions
134(3)
Chapter 3 Advanced Formatting Techniques
137(44)
Conditional Formatting
137(30)
Apply a Rule to an Entire Row
137(4)
Conditional Formatting with Multiple Columns
141(2)
Format Dates That Are Due Soon
143(3)
Format Weekends and Other Important Dates
146(5)
Compare Two Lists
151(2)
Icon Sets to Show Change
153(3)
Data Bars to Compare Values
156(2)
Data Bars to Show Progress Toward a Goal
158(3)
Create a Heat Map with Color Scales
161(3)
Conditional Formatting with PivotTables
164(3)
In-Cell Charts with the REPT Function
167(3)
Custom Number Formatting
170(11)
Combine Text and Numbers in a Cell
172(1)
Show the Weekday of a Date
173(3)
Keep the Leading Zeroes of a Value
176(1)
Display Negative Values in Red
177(1)
Show Zero Values as Blank Cells
178(1)
Format Time to Show Duration over 24 Hours
179(2)
Chapter 4 Advanced Chart Tricks
181(1)
Dynamic Charts
181(1)
Dynamic Chart Range with Tables
182(2)
Charts and Dynamic Array Formulas
184(3)
Automatically Sort Chart Values - With Excel 365
187(1)
Automatically Sort Chart Values - Without Excel 365
188(4)
Rolling Excel Chart for Last X Values
192(3)
Interactive Charts
195(10)
Adding Interactivity with a Data Validation List
196(3)
Check Boxes to Select Data Series
199(6)
Creative Chart Labels
205(9)
Dynamic Chart Title
205(3)
Advanced Data Labels
208(4)
Display Symbols in the Chart Axis
212(2)
Advanced Formatting with Charts
214(13)
Conditional Formatting on Charts
214(3)
Conditionally Format Markers on a Line Chart
217(2)
Bar in Bar Chart to Compare Actual Against Target
219(3)
Add a Target Range to a Line Chart
222(5)
Chapter 5 Power Query - You Will Never Work the Same Way Again
227(56)
Introduction to Power Query
227(3)
Transform Data in Excel
230(7)
Combine Multiple Sheets into One
237(5)
Excel.CurrentWorkbookO Problem
240(2)
Connect to Another Excel Workbook
242(6)
Merge Queries - A Lookup Alternative
248(4)
Merge Queries - Compare Tables
252(5)
Import Files from a Folder
257(6)
Extract Data from the Web
263(9)
Import from PDF
272(3)
Group By and Pivot
275(8)
Chapter 6 Power Pivot - The Internal Data Model of Excel
283(62)
What Is Power Pivot?
283(2)
Enable the Power Pivot Add-In
285(3)
Importing Data into the Model
288(8)
Import from Folder
288(4)
Import from Excel Workbook
292(4)
Create the Table Relationships
296(9)
Create a Date Table
305(7)
Sort by Column
308(2)
Mark the Table as a Date Table
310(1)
Create the Relationship to the Date Table
311(1)
Working with DAX Measures
312(16)
What Are the Advantages of Using Measures?
312(2)
Create DAX Measures
314(1)
Calculate the Number of Sales
314(3)
Total Sales Revenue
317(2)
Cumulative Sales Total
319(2)
% of Year Total
321(2)
Difference and % Difference to Previous Month
323(3)
Hide Bad Measure Totals
326(2)
Organizing Your Measures and Fields
328(8)
View Fields and Areas Side by Side
329(2)
Hide Fields from Client Tools
331(1)
Create a Measures Table
332(4)
Using a Disconnected Slicer
336(6)
Convert a PivotTable to Formulas
342(3)
Index 345
Alan is a Microsoft MVP, Excel trainer, YouTuber, and freelance writer. He has been helping people in Excel for over 20 years. He loves training and the joy he gets from knowing he is making people's working lives easier.





Alan runs his own blog - Computergaga - and writes for multiple other websites. His YouTube channel has over 500 videos and over 24 million views. He organizes a monthly Excel meetup in London where the Excel community learns, shares, and enjoys each others company.