Excel Tips Weekly

Excel Tips Weekly

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 21h 30m | 3.92 GB

This tips-based course will show Excel users productivity-boosting tricks, cool hidden features, need-to-know functions, and advanced content on subjects such as using PivotTables for data analysis. Tune in every Tuesday for a new tip from expert Dennis Taylor. Each tutorial is a short, self-contained lesson guaranteed to give you new insights into Excel.

Table of Contents

Excel Tips New This Week
1 Control gridlines in charts and on worksheets

Previous Episodes
2 Hide and unhide worksheets securely
3 Resizing, moving, copying, and manipulating charts and other objects
4 Using list boxes and combo boxes to facilitate forms creation
5 Using the FORMULATEXT and N functions for worksheet and formula documentation
6 Using the ISBLANK and COUNTBLANK functions to check for and tabulate blank cells
7 Summarizing data from diverse ranges with Data Consolidate
8 Using check boxes and Conditional Formatting together for dynamic displays
9 Becoming more productive with these 10 tiny tips youll use often
10 Transposing data and charts for a different perspective on your data
11 Applying conditional formatting across rows of data
12 How to create frequency tabulations and distributions
13 Create an expanded list from a summary using PivotTable techniques
14 Solve matching issues with matching phone numbers and SS numbers
15 Use CHOOSE to calculate fiscal quarters and as a VLOOKUP alternative
16 Use Outlining tools to quickly expand and collapse data for dynamic presentations
17 Use the SUBTOTAL and AGGREGATE functions to avoid double counting
18 Create lists of all 2nd Tuesdays, last Tuesdays. and other date series
19 Create linkage formulas that refer to other worksheets and workbooks
20 Formula nesting_ Using multiple functions in the same formula
21 Keyboard shortcuts for numeric formats
22 Exploring various layouts for your PivotTable
23 Use the Subtotal command to analyze data with single or multiplelevel subtotals
24 Work with MAX, MAXIFS, LARGE, and related functions
25 Use the dynamic TODAY and NOW functions for realtime uptodate calculations
26 Use the FIND, MID, LEFT, and RIGHT functions
27 Flip the lefttoright order of columns with the INDEX function or sort by columns
28 Working with hidden data when copying cells and creating charts
29 Use conditional formatting data bars to accentuate negative data
30 Creative use of sparklines in merged cells with axes
31 Copy formats quickly using dragging techniques and the Format Painter
32 Displaying multiple worksheets and workbooks together
33 Splitting and consolidating workbooks
34 Tips for creating and updating sample data
35 Use conditional formatting to overcome formatting limitations of the IF function
36 Use VLOOKUP, MATCH and Index functions with array formulas
37 Summarize data from different worksheets with a PivotTable
38 AutoFill shortcuts for date series, onetwo week intervals, EOM, and formulas
39 Create summary statistics usingCOUNTIFS, SUMIFS, and AVERAGEIFS functions
40 Use custom formulas in data validation to keep out bad data
41 Dealing with circular errors
42 Tips for creating charts quickly
43 Creating heat maps using conditional formatting
44 Using date and time as metrics in a PivotTable
45 Using the INDEX function to extract data by row and column
46 Displaying tips when using Excel in presentations
47 The Wrap Text, Merge and Center, and Indent options
48 Using fill effects gradients, patterns, styles for colorful cell background variations
49 Working with multiple worksheets simultaneously
50 Exploring font choices not found on the Home tab
51 Using WordArt for special titles and headings
52 Differentiating weekdays and weekends in Excel data
53 Creating a 2way lookup with VLOOKUP, HLOOKUP, INDEX, and MATCH functions
54 Borders and gridlines_ Exploring variations, options, and differences
55 Adding illustrations to your workbook
56 New Excel 2016 chart types_ Tree map and sunburst
57 Freezing column and row titles
58 Use data validation to force entries to be uppercase or lowercase
59 Avoiding common chart distortions
60 Use the new Funnel chart available in Excel 365
61 Combine data using CONCATENATE, CONCAT, and TEXTJOIN functions and the ampersand character
62 Recognizing Excel Formula errors_ NA, VALUE, REF, DIV0, NUM, and NAME
63 Creating crosstotals with the SUMIFS function and mixed references
64 New Excel 2016 chart types_ Histogram, Pareto, and box, and whisker
65 Using the AND and OR functions independently or within IF functions
66 Using Pick list, VLOOKUP, Table, and Chart together
67 Vertical and horizontal alignment settings and orientation options
68 Sorting tips and shortcuts
69 A look at new chart types in Excel 2016_ Waterfall
70 Work with formulas and formats_ Decimals and fractions
71 Creating mixed reference formulas_ converting to a table_ applying conditional formatting
72 Using formulas to calculate values across worksheets and workbooks
73 Dynamically presenting data via chart slicers
74 Parsing your data to create unique lists
75 Using the REPT function to represent data visually
76 Using source data from a table to make your PivotTable more dynamic
77 Accelerating Cut, Copy, Paste, and Insert tasks
78 Increasing your productivity with custom lists
79 Highlighting PivotTable results with conditional formatting
80 Customizing templates to fit your unique needs
81 Creating dynamic charts with incell conditional formatting
82 Enhancing table filtering with slicers
83 Converting charts into pictures
84 Custom grouping in PivotTables
85 Quick formatting tips
86 The top five unknown but useful keyboard shortcuts
87 Using the TREND and GROWTH functions for projecting future results
88 Using scroll bars and spin buttons to facilitate forms creation
89 Creating colorful 3D buttons for worksheet annotation and macro buttons
90 Calculating with hours, minutes, and times of day
91 Tracking down cell dependencies across multiple worksheets and workbooks
92 Discovering timesaving tools in the Go To Special feature
93 Adding, managing, and printing comments for worksheet documentation
94 Reducing data entry time by expanding AutoCorrect options
95 Setting up custom views for quick access to different worksheet displays
96 Using hyperlinks for rapidly switching worksheet locations and jumping to websites
97 Controlling worksheet security by allowing selected users to edit specific cell ranges
98 Using option buttons, group boxes, and checkboxes to facilitate forms creation
99 Exploring whatif scenarios using Goal Seek
100 Creating text, numerical, date, and time lists in a flash
101 Creating Variable Conditional Formatting Rules
102 Handling Dates with unusual formats
103 Using wildcards asterisk, tilde, and question mark
104 Custom formats using asterisk, semicolon, and brackets
105 Meeting unusual filtering needs via Advanced Filter
106 Create an Organization Chart
107 Twoway lookup using the MATCH and INDEX functions
108 Using Excels errorchecking functions
109 Custom formats, relative addressing, and conditional formatting
110 Auditing
111 Adding comments and shapes
112 Drawing borders and border grids
113 Building a flexible monthly table using functions
114 Adding pictures as worksheet backgrounds
115 ADDRESS, ROW, and COLUMN functions
116 Join data with new CONCAT and TEXTJOIN functions
117 Working with formulas in tables
118 Locating Data Validation rules and violations
119 Creating an Excel template
120 Restoring missing column titles
121 Creating a pick list dependent on another pick list
122 Pasting picture links
123 Using Command Shortcuts with the Alt key
124 Set up a macro for sorting dynamic data
125 Use random number functions for sorting and creating sample data
126 Use calcuated fields and calculated items in a PivotTable
127 Replace characters by position with the REPLACE function
128 Work with formulas in tables
129 Keystroke shortcuts from A to Z using the Ctrl key
130 Use special tricks for working with dates in Charts
131 Use a data form for data entry and exploring data
132 Creating an Excel template to simplify updating a monthly file
133 Calculate dates efficiently using Excels hidden function DATEDIF
134 Create dynamic sheet name references with the INDIRECT function
135 How to use locked cells and sheet protection
136 Avoid errors and hidden data with the powerful AGGREGATE function
137 Hide worksheets, row, columns, cells and other Excel elements