[Lynda.com / Dennis Taylor] Excel Tips Weekly [2015-2017, ENG] + Sub (Eng) Обновлено 09.05.2017

Содержание

Excel Tips Weekly
01-Hide and unhide worksheets securely
02-Resizing, moving, copying, and manipulating charts and other objects
03-Using list boxes and combo boxes to facilitate forms creation
04-Using the FORMULATEXT and N functions for worksheet and formula documentation
05-Using the ISBLANK and COUNTBLANK functions to check for and tabulate blank cells
06-Summarizing data from diverse ranges with Data > Consolidate
07-Using check boxes and Conditional Formatting together for dynamic displays
08-Becoming more productive with these 10 tiny tips you'll use often
09-Transposing data and charts for a different perspective on your data
10-Applying conditional formatting across rows of data
11-Exploring various layouts for your PivotTable
12-Splitting and consolidating workbooks
13-Using date and time as metrics in a PivotTable
14-Adding illustrations to your workbook
15-Using Pick list, VLOOKUP, Table, and Chart together
16-Using formulas to calculate values across worksheets and workbooks
17-Dynamically presenting data via chart slicers
18-Parsing your data to create unique lists
19-Using the REPT function to represent data visually
20-Using source data from a table to make your PivotTable more dynamic
21-Accelerating Cut, Copy, Paste, and Insert tasks
22-Increasing your productivity with custom lists
23-Highlighting PivotTable results with conditional formatting
24-Customizing templates to fit your unique needs
25-Creating dynamic charts with in-cell conditional formatting
26-Enhancing table filtering with slicers
27-Converting charts into pictures
28-Custom grouping in PivotTables
29-Quick formatting tips
30-The top five unknown but useful keyboard shortcuts
31-Using the TREND and GROWTH functions for projecting future results
32-Using scroll bars and spin buttons to facilitate forms creation
33-Creating colorful 3D buttons for worksheet annotation and macro buttons
34-Calculating with hours minutes and times of day
35-Tracking down cell dependencies across multiple worksheets and workbooks
36-Discovering time saving-tools in the Go To Special feature
37-Adding, managing, and printing comments for worksheet documentation
38-Reducing data entry time by expanding AutoCorrect options
39-Setting up custom views for quick access to different worksheet displays
40-Using hyperlinks for rapidly switching worksheet locations and jumping to websites
41-Controlling worksheet security by allowing selected users to edit specific cell ranges
42-Using option buttons, group boxes, and checkboxes to facilitate forms creation
43-Exploring what-if scenarios using Goal Seek
44-Creating text, numerical, date, and time lists in a flash
45-Creating Variable Conditional Formatting Rules
46-Handling Dates with unusual formats
47-Using wildcards (asterisk, tilde, and question mark)
48-Custom formats using asterisk, semicolon, and brackets
49-Meeting unusual filtering needs via Advanced Filter
50-Create an Organization Chart
51-Two-way lookup using the MATCH and INDEX functions
52-Using Excel's error-checking functions
53-Custom formats, relative addressing, and conditional formatting
54-Auditing
55-Adding comments and shapes
56-Drawing borders and border grids
57-Building a flexible monthly table using functions
58-Adding pictures as worksheet backgrounds
59-ADDRESS, ROW, and COLUMN functions
60-Join data with new CONCAT and TEXTJOIN functions
61-Working with formulas in tables
62-Locating Data Validation rules and violations
63-Creating an Excel template
64-Restoring missing column titles
65-Creating a pick list dependent on another pick list
66-Pasting picture links
67-Using Command Shortcuts with the Alt key
68-Set up a macro for sorting dynamic data
69-Use random number functions for sorting and creating sample data
70-Use calcuated fields and calculated items in a PivotTable
71-Replace characters by position with the REPLACE function
72-Work with formulas in tables
73-Keystroke shortcuts from A to Z - using the Ctrl key
74-Use special tricks for working with dates in Charts
75-Use a data form for data entry and exploring data
76-Creating an Excel template to simplify updating a monthly file
77-Calculate dates efficiently using Excel's hidden function - DATEDIF
78-Create dynamic sheet name references with the INDIRECT function
79-How to use locked cells and sheet protection
80-Avoid errors and hidden data with the powerful AGGREGATE function
81-Hide worksheets, row, columns, cells and other Excel elements
82-Use slicers and charts together for dynamic presentations
83-Save a chart as a template - use the template for current or new charts
84-MAXIFS and MINIFS added to the COUNTIF_SUMIF family of functions
85-Adjust a chart's source data and adjust its series order
86-Use the OFFSET function for tabulating moving data
87-Alter numeric data without formulas
88-Use Flash Fill to rapidly combine or separate columnar data
89-Adjust banded-row formatting
90-Change the shape of comment boxes and other objects
91-How and when to use Excel's 10 rounding functions
92-Calculate faster with the AutoSum button and AutoSum keystroke shortcut
93-How to use mixed cell references
94-Adjust formatting to display large values in thousands, millions, etc.
95-Activate the Speak On Enter and Speak Cells features
96-Accelerate data entry with five special techniques
97-Insert a text box or shape and link a cell's content to it
98-Use Watch Window and other techniques to track changing cells
99-How to create time interval entries
100-How to create frequency tabulations and distributions
101-Create an expanded list from a summary using PivotTable techniques
102-Solve matching issues with matching phone numbers and SS numbers
103-Use CHOOSE to calculate fiscal quarters and as a VLOOKUP alternative
104-Use Outlining tools to quickly expand and collapse data for dynamic presentation
105-Use the SUBTOTAL (and AGGREGATE) functions to avoid double counting
106-Create lists of all 2nd Tuesdays, last Tuesdays. and other date series
107-Create linkage formulas that refer to other worksheets and workbooks
108-Formula nesting: Using multiple functions in the same formula
109-Keyboard shortcuts for numeric formats
110-Use the Subtotal command to analyze data with single- or multiple-level subtotals
111-Work with MAX, MAXIFS, LARGE, and related functions
112-Use the dynamic TODAY and NOW functions for real-time up-to-date calculations
113-Use the FIND, MID, LEFT, and RIGHT functions
114-Flip the left-to-right order of columns with the INDEX function or sort by columns
115-Working with hidden data when copying cells and creating charts
116-Use conditional formatting data bars to accentuate negative data
117-Creative use of sparklines in merged cells with axes
118-Copy formats quickly using dragging techniques and the Format Painter
119-Displaying multiple worksheets and workbooks together

Внимание! Раздача ведётся путём добавления новых видеоуроков

Раздача ведётся путём добавления новых видеоуроков, при каждом добавлении создаётся новый торрент. Чтобы начать докачку нового урока, пользователям необходимо сделать следующее:
• остановить скачивание
• удалить старый торрент у себя из клиента (старые эпизоды удалять не надо)
• скачать новый торрент и запустить его у себя в клиенте вместо старого, при этом указать клиенту путь в старую папку, куда и должно происходить скачивание новых видеоуроков
Ваш клиент при этом должен произвести хеширование (проверку) старой папки (если не производит сам - помогите ему сделать это), и будет докачивать только те эпизоды, которых у Вас ещё нет. Старые уроки при этом не удаляются, а продолжают раздаваться.
Если Вы уже удалили старые эпизоды, то предотвратить повторную закачку старых видеоуроков можно, отжав галочку в соответствующих местах при запуске нового торрента.

Скриншоты