Excel Tips Weekly

Get a new productivity-boosting tip every Tuesday from Excel expert Dennis Taylor.

Excel Tips

• Create forms using check boxes and option buttons to simplify data entry

Previous Episodes

• Hide and unhide worksheets securely
• Resizing, moving, copying, and manipulating charts and other objects
• Using list boxes and combo boxes to facilitate forms creation
• Using the FORMULATEXT and N functions for worksheet and formula documentation
• Using the ISBLANK and COUNTBLANK functions to check for and tabulate blank cells
• Summarizing data from diverse ranges with Data > Consolidate
• Using check boxes and conditional formatting together for dynamic displays
• Becoming more productive with these 10 tiny tips you'll use often
• Transposing data and charts for a different perspective on your data
• Applying conditional formatting across rows of data
• Exploring various layouts for your PivotTable
• Splitting and consolidating workbooks
• Using date and time as metrics in a PivotTable
• Adding illustrations to your workbook
• Using a pick list, VLOOKUP, table, and chart together
• Using formulas to calculate values across worksheets and workbooks
• Dynamically presenting data via chart slicers
• Parsing your data to create unique lists
• Using the REPT function to represent data visually
• Using source data from a table to make your PivotTable more dynamic
• Accelerating Cut, Copy, Paste, and Insert tasks
• Increasing your productivity with custom lists
• Highlighting PivotTable results with conditional formatting
• Customizing templates to fit your unique needs
• Creating dynamic charts with in-cell conditional formatting
• Enhancing table filtering with slicers
• Converting charts into pictures
• Custom grouping in PivotTables
• Quick formatting tips
• The top five unknown but useful keyboard shortcuts
• Using the TREND and GROWTH functions for projecting future results
• Using scroll bars and spin buttons to facilitate forms creation
• Creating colorful 3D buttons for worksheet annotation and macro buttons
• Calculating with hours, minutes, and times of day
• Tracking down cell dependencies across multiple worksheets and workbooks
• Discovering time-saving tools in the Go To Special feature
• Using Go To Special
• Reducing data entry time by expanding AutoCorrect options
• Setting up custom views for quick access to different worksheet displays
• Using hyperlinks for rapidly switching worksheet locations and jumping to websites
• Controlling worksheet security by allowing selected users to edit specific cell ranges
• Using option buttons, group boxes, and checkboxes to facilitate forms creation
• Exploring what-if scenarios using Goal Seek
• Creating text, numerical, date, and time lists in a flash
• Creating variable conditional formatting rules
• Handling dates with unusual formats
• Using wildcards (asterisk, tilde, and question mark)
• Custom formats using asterisk, semicolon, and brackets
• Meeting unusual filtering needs via Advanced Filter
• Create an organization chart
• Two-way lookup using the MATCH and INDEX functions
• Using Excel error-checking functions
• Custom formats, relative addressing, and conditional formatting
• Auditing
• Adding comments and shapes
• Drawing borders and border grids
• Building a flexible monthly table using functions
• Adding pictures as worksheet backgrounds
• ADDRESS, ROW, and COLUMN functions
• Join data with new CONCAT and TEXTJOIN functions
• Working with formulas in tables
• Locating data validation rules and violations
• Creating an Excel template
• Restoring missing column titles
• Creating a pick list dependent on another pick list
• Using command shortcuts with the Alt key
• Set up a macro for sorting dynamic data
• Use random number functions for sorting and creating sample data
• Use calcuated fields and calculated items in a PivotTable
• Replace characters by position with the REPLACE function
• Work with formulas in tables
• Keystroke shortcuts from A to Z: Using the Ctrl key
• Use special tricks for working with dates in charts
• Use a data form for data entry and exploring data
• Creating an Excel template to simplify updating a monthly file
DATEDIF
• Calculate dates efficiently using Excel's hidden function
• Create dynamic sheet name references with the INDIRECT function
• Avoid errors and hidden data with the powerful AGGREGATE function
• Hide worksheets, row, columns, cells and other Excel elements
• Use slicers and charts together for dynamic presentations
• Save a chart as a template: Use the template for current or new charts
• MAXIFS and MINIFS added to the COUNTIF/SUMIF family of functions
• Adjust a chart's source data and adjust its series order
• Use the OFFSET function for tabulating moving data
• Alter numeric data without formulas
• Use Flash Fill to rapidly combine or separate columnar data
• Adjust banded-row formatting
• Change the shape of comment boxes and other objects
• How and when to use the 10 rounding functions in Excel
• Calculate faster with the AutoSum button and AutoSum keystroke shortcut
• How to use mixed cell references
• Activate the Speak On Enter and Speak Cells features
• Accelerate data entry with five special techniques
• Insert a text box or shape and link a cell's content to it
• Use Watch Window and other techniques to track changing cells
• How to create time interval entries
• How to create frequency tabulations and distributions
• Create an expanded list from a summary using PivotTable techniques
• Solve matching issues with matching phone numbers and SS numbers
• Use CHOOSE to calculate fiscal quarters and as a VLOOKUP alternative
• Use outlining tools to quickly expand and collapse data for dynamic presentations
• Use the SUBTOTAL (and AGGREGATE) functions to avoid double counting
• Create lists of all 2nd Tuesdays, last Tuesdays. and other date series
• Create linkage formulas that refer to other worksheets and workbooks
• Formula nesting: Using multiple functions in the same formula
• Keyboard shortcuts for numeric formats
• Use the Subtotal command to analyze data with single- or multiple-level subtotals
• Work with MAX, MAXIFS, LARGE, and related functions
• Use the dynamic TODAY and NOW functions for real-time up-to-date calculations
• Use the FIND, MID, LEFT, and RIGHT functions
• Flip the left-to-right order of columns with the INDEX function or sort by columns
• Working with hidden data when copying cells and creating charts
• Use conditional formatting data bars to accentuate negative data
• Creative use of sparklines in merged cells with axes
• Copy formats quickly using dragging techniques and the Format Painter
• Displaying multiple worksheets and workbooks together
• Tips for creating and updating sample data
• Use conditional formatting to overcome formatting limitations of the IF function
• Use VLOOKUP, MATCH, and INDEX functions with array formulas
• Summarize data from different worksheets with a PivotTable
• AutoFill shortcuts for date series, one/two week intervals, EOM, and formulas
• Create summary statistics using COUNTIFS, SUMIFS, and AVERAGEIFS functions
• Use custom formulas in data validation to keep out bad data
• Dealing with circular errors
• Tips for creating charts quickly
• Creating heat maps using conditional formatting
• Using the INDEX function to extract data by row and column
• Displaying tips when using Excel in presentations
• The Wrap Text, Merge and Center, and Indent options
• Using fill effects (gradients, patterns, styles) for colorful cell background variations
• Working with multiple worksheets simultaneously
• Exploring font choices not found on the Home tab
• Using WordArt for special titles and headings
• Creating a two-way lookup with VLOOKUP, HLOOKUP, INDEX, and MATCH functions
• Borders and gridlines: Exploring variations, options, and differences
• New Excel 2016 chart types: Tree map and sunburst
• Freezing column and row titles
• Use data validation to force entries to be uppercase or lowercase
• Avoiding common chart distortions
• Use the new funnel chart available in Excel 365
• Combine data using CONCATENATE, CONCAT, and TEXTJOIN functions and the ampersand (&) character
• Recognizing Excel Formula errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM, and #NAME!
• Creating cross-totals with the SUMIFS function and mixed references
• New Excel 2016 chart types: Histogram, Pareto, and box, and whisker
• Using the AND and OR functions independently or within IF functions
• Vertical and horizontal alignment settings and orientation options
• Sorting tips and shortcuts
• A look at new chart types in Excel 2016: Waterfall
• Work with formulas and formats: Decimals and fractions
• Creating mixed reference formulas; converting to a table; applying conditional formatting
• Control gridlines in charts and on worksheets
• Use the INDIRECT function with intersection formulas and range names to extract data
• Using Expand/Collapse on PivotTables and PivotCharts for rapid display changes
• Control phone number and postal code formats using built-in options or customized formats
• Change the default Excel chart type; create and use chart templates
• Create your own customized date and time formats
• Create on-screen warnings and reminders with comments or data validation messages
• Use REPLACE for formats only, for data only, or for both
• Create numeric formats: Display in thousands or millions
• Converting dates like 20102006 into usable date entries
• Create custom lists for letter series like A to Z
• Freeze Panes and Split
• NETWORKDAYS.INTL and WORKDAY.INTL
• Calculate % of change
• Fill in all blank cells within a range with either formatting or data
• Use the COUNT and COUNTA functions
• Use workbook protection to prevent use of sheet commands
• Rank data with the RANK and RANK.AVG functions
• Use nonstandard fiscal years and quarters in PivotTables
• Identify or extract unique entries in a list
• Keyboard, mouse, and command techniques for viewing worksheets
• Create artistic charts with random numbers
• Extract day, month, and year from date fields
• Compare data with EXACT, FIND, and SEARCH functions
• How to covert ROMAN numberals
• Use range names for more readable formulas
• Customize your Quick Access Toolbar
• Conditional formatting based on date proximity
• How to adjust names
• Differences and limitations of converting data to a table
• Avoid the #DIV/0 error message
• Explore formatting options not available on the Home tab
• Control table formatting with custom formats
• Use date functions for age and tenure calculations
• Avoid unintentional entries when typing code numbers
• Sorting by moving columns
• Calculating cumulative totals
• Using the CONVERT function for different numbering systems
• Creating an automatically expanding chart by basing it on a table
• Keystroke shortcuts using the Alt key
• Using the Solver Add-in
• Column widths, row heights, merging cells, and related formatting issues
• Adjusting default layouts and date grouping in PivotTables
• Auditing cell content with Watch Window and dependent cell formulas
• Use the LEN and REPT functions for specialized cell testing and display options
• Use the TRIM and CLEAN functions to remove excess spaces and non-printing characters
• Create double-spaced and triple-spaced printouts while repeating column headings
• Helpful keystroke shortcuts
• Identify weekdays and weekend days in data
• Use chart and filter for presentations
• Create picture links
• How to use the error-checking rules in Excel
• Protect worksheets and lock cells
• Use mixed addresses in Excel formulas
• Rapid filtering with Filter by Selection
• Display large values
• Tracking down conditional formatting and data validation rules
• Transposing data and using the TRANSPOSE function
• Displaying gridlines, borders, and column/row headings when printing
• Exploring some of the 200-plus SmartArt graphic options
• Using the CEILING and FLOOR functions for specialized rounding needs
• Inserting, reshaping, and formatting shapes: Rectangles, arrows, stars, and banners
• Tabulating totals with the VLOOKUP function and array constants
• Working with array formulas more easily
• Using the new UNIQUE function to count and copy unique list entries
• Creating and updating sample data
• Format macros to make you a more efficient Excel user
• Use the new SORT and SORTBY functions to extract sorted lists
• Use themes to adjust worksheet colors, fonts, and effects
• Calculate loan payments and investments with PMT and FV functions
• Chart display options with blank cells in source data
• Adjust Conditional Formatting rules by altering percentage breakpoints
• Discover new formula capabilities with new functions and dynamic arrays
• Sort or filter data based on color font or cell color background
• Simplify the use of special characters and symbols
• Select from over 800 icons to enliven worksheets
• Use the new RANDARRAY function that replaces RAND and RANDBETWEEN
• How and when to use an area chart
• Insert colorful images with the People Graph Add-in
• Use formulas to create interactive charts
• Track variables using the Scenario Manager
• Get totals quickly without creating formulas
• Use column or row references to create dynamic formulas
• Overcoming obstacles when working with dates in charts
• Create map-type charts based on geographical locations
• Techniques for creating date series
• Extract filtered data with data validation and the FILTER function
• Use Find and Replace to change cell contents and formats
• Add formula tools and symbols to the Quick Access Toolbar
• Use the XLOOKUP function to replace VLOOKUP
• Change PivotTable settings for titles and summaries
• Use the Excel filtering capability for dates
• Use worksheet names in formulas
• Use data validation rules with special phone and social security formats
• Work with time calculations in formulas
• Prevent and locate duplicate worksheet entries
• Look forward and backward using EDATE and other date functions
• Display and highlight worksheet formula cells
• Split or join columnar data with Text to Columns or Flash Fill
• Highlight milestone data with conditional formatting and cumulative formulas
• Use the XMATCH function to replace MATCH
• Create a powerful macro
• Work with hidden and visible data in filtered and subtotaled lists
• Use TODAY, NOW, YEARFRAC, and DATEDIF
• Use data validation rules to prevent duplicate entries in a range
• Use various techniques to hide cells, rows, columns, and worksheets
• Align, arrange, and rotate shapes in worksheets
• File documentation with the Workbook Statistics button
• Avoid misleading visuals when rescaling
• Use the Fill Justify feature to wrap long text
• Create range names from Column and Row headings
• Use the SUBSTITUTE and REPLACE functions
• Accelerate Conditional Formatting with the Quick Access Toolbar
• Use wildcard symbols in functions
• Use wildcard symbols in filtering and commands
• Condense lengthy nested IF functions with the newer IFS function
• Add flair to charts with these formatting options: Gap width, shadow, glow, 3D, and more
• Control worksheet security by allowing selected users to edit specific cell ranges
• Retrieve vital data based on location using the Geography tool
• Use the new SEQUENCE function to quickly build numeric and date arrays
• Calculating text length and word count with LEN, TRIM, and SUBSTITUTE functions
• Create a list box to facilitate selection of data from a list
• Use various Excel count functions: COUNT, COUNTA, COUNTBLANK, and more
• Enhance readability with banded rows via conditional formatting
• Seven different ways to drag data using the Ctrl, Shift, and Alt keys
• Use the SWITCH function: Compare with IFS, CHOOSE, and other lookup functions
• Adjust charts quickly with quick layout, change colors, and chart styles options
• Analyze PivotTable data with 13 different Show Values As options
• Expand/collapse or sort displayed results when using the SUBTOTAL command
• Use the AGGREGATE function to circumvent errors and ignore hidden data
• Use the F9 key to evaluate parts of a formula, recalculate random entries, and control iteration
• Using last-to-first (bottom-up) searches with XLOOKUP and XMATCH
• Explore Paste Special options: Skip Blanks, Column Widths, Add, Transpose, and more
• Reduce data entry drudgery with Ctrl+Enter, AutoComplete, and AutoCorrect
• Using the LET function to simplify formulas via programming concepts
• Create 51 new US State worksheets, or any multiple cluster of worksheets, in a flash
• Create moving averages with formulas and chart trendlines
• Use error-checking functions in Excel: IFERROR, ISERR, and ISERROR
• Use Alt+Enter and Wrap Text for line-wrapping titles and improving formula readability
• Work with cell colors, patterns, and effects to emphasize data and provide visual flair
• Dynamic array formulas, new ways to calculate: One formula displays results in multiple cells
• Fonts such as Arial and Calibri and a rundown of various available fonts in Excel
• Use 3D formulas to tabulate data from multiple worksheets with the same layout
• Multiple built-in date formats as well as tons of self-defined variations; m/d/y and mmm-d-yyyy
• Where does that cell get its data from?: Tracking down cell precedents
• Controlling date entry restrictions using data validation
• Remove duplicates from a list vs. creating a new list without duplicates
• New data types: Extract valuable online data into your worksheets
• Use the FILTER and UNIQUE functions together for dynamic extractions
• Adjusting text entries with the UPPER, LOWER, and PROPER functions
• Refine filtering needs via custom filter options
• Comparing column charts and bar charts: Pros and cons
• How to use exponentiation (powers and roots) in Excel formulas
• Tracking down errors with the ISTEXT, ISNUMBER, and ISNONTEXT functions
• Tips for saving time when typing function names
• SUMIFS, SUMPRODUCT, and SUM functions compared and contrasted
• Conversion of values into binary, octal, decimal, and hexadecimal equivalents
• Quick number formatting with keystroke shortcuts and icon buttons
• Date calculation and formatting issues related to the 2029/2030 switch
• Using wildcards in the new XLOOKUP and XMATCH functions
• Using slicers as analytical tools
• Conditional formatting using contrasting heat maps on multiple vs.single ranges
• Create dynamic pick lists using the UNIQUE and SORT functions, and data validation
• Contrasting uses of clustered column and stacked column charts
• Use the XLOOKUP function with multiple column criteria and multiple column results
• Tips for updating entire columns of date entries when using real or sample data
• Using TRIM, CLEAN and other functions to clean up text data
• Create multiple range names from the top row and/or left column
• Use dragging techniques to extend dates by weekdays only, month, or year
• Use the LAMBDA function to create worksheet functions
• Get the formula results you want using Goal Seek
• Sort your data based on a Custom List that you define
• Create a dynamic sheet name reference with the INDIRECT function
• Calculate dates and/or days of the week for selected future holidays
• Use dragging techniques for faster Paste Special options
• How to use and not use SS numbers; fake SS numbers; display as XXX-XX-9999
• Five indispensable keystroke shortcuts for Windows and Mac users
• Using new formula-writing techniques to simplify wide-ranging formulas
• Calculate time differences within days and across multiple days
• Use keyboard, mouse, and commands to zoom in/out quickly in Excel Windows and Mac versions
• Make dynamic presentations with rapid expand/collapse detail features in outlining
• Get data analysis suggestions with the Analyze Data (formerly Ideas) tool
• Using the Distinct vs. Unique options when using the UNIQUE function
• Use the Screen Snip tool or keystroke shortcut to take a picture of the screen or a portion of it
• Exploring Excel's Sunburst chart to display hierarchical data
• Creating floating text boxes with shadow, reflection, glow, and other shape effects
• Page Setup tip: Print repeating titles, shrink-to-fit, landscape/portrait, double-spaced layout
• Use Data Validation based on multiple criteria
• Customize the Quick Access Toolbar using the Alt key or a mouse
• Exploring treemap charts to display hierarchical data
• Use the Quick Analysis tool for easy access to power tools
• Create a number series with the Ctrl key and left/right mouse button
• Combine IF, MAX, VALUE, and COUNTIF functions to analyze sales
• Unhide multiple worksheets at once and use the very hidden option
• Use the Fill Justify command to quickly rewrap text into different cells
• Create a 15th day or end-of-month series and adjust to weekdays only
• Use the Go To Special option to select and populate blank cells
• Accentuate values with conditional formatting and sparklines
• Transpose data with formula substitution and the TRANSPOSE function
• Use emojis and other symbols in formulas and charts
• Use the Advanced Filter for specialized filtering needs
• Combo charts: Combine different chart types to present data more clearly
• Combining the SEQUENCE, LEN, and MID functions to extract data into multiple cells
• Exploring Edit Default Layout options when using PivotTables
• Navigation tips: Shortcuts to jump within and between worksheets and workbooks
• Use DATEDIF to calculate months or days after yearly or monthly anniversaries
• WordArt Transform: Special text character layouts
• Sort data based on months and weekdays, even when entered as text
• Use formulas to calculate values across worksheets and workbooks
• Use XLOOKUP instead of complex combinations of VLOOKUP, INDEX, and MATCH
• Use dragging techniques to create a forecast based on TREND and GROWTH functions
• Stock charts: Adding Open, High, Low, Close, and Volume values
• Control gridlines: Change colors, turn them on/off, and assign to a macro button
• Examples of TRANSPOSE and other functions working differently with the new calculation engine
• Locate formula dependencies via keystroke shortcuts and the Trace Dependents command
• Replace text based on formats, like cell attributes (bold, text color, etc.)