In some Excel worksheets, you might want each item entered only once. For example, assign employees for one weekly on-call assignment, without duplicates. Instead of a normal drop-down, use this trick, to make a drop down list that hides all the used names.
Category: Excel Videos
How to Send Excel Data to Another Sheet-No Macros
See how to filter Excel data and copy the results to a different worksheet, without using macros.
Continue reading “How to Send Excel Data to Another Sheet-No Macros”
TEXTJOIN Excel Function-Examples-Video
To quickly combine text and numbers from multiple cells in Excel, use the TEXTJOIN Excel function. Sarah shows the steps in this 3-minute video.
Sort Multiple Columns in Excel Step-by-Step
Sorting data in Excel is quick and easy, if you only need to sort by a single column, or even two columns. For a large table, sorting can be more complicated, if you need to sort by 3 or more columns. Today, I’ll show you how to sort multiple columns in Excel so your data is quickly organized the way you need it. Whether you’re sorting by two, three, or more columns, this guide will help you through it!
Continue reading “Sort Multiple Columns in Excel Step-by-Step”
Video – How to Make a Simple IF Formula in Excel
Use the Excel IF function when you want to test something. The IF formula will show one result if the test result is True, and a different result if the test result is False.
Continue reading “Video – How to Make a Simple IF Formula in Excel”
Video – Show Percent of Subtotal – Excel Pivot Table
In an Excel pivot table, you can use built-in custom calculations, for a different view of the data.
For example, in this video I set up the pivot table to show what % of monthly sales were Binders, and what % for each colour – red, blue, and black.
Continue reading “Video – Show Percent of Subtotal – Excel Pivot Table”
Why Excel Freeze Panes Settings Disappear Prevent Problem
Did you ever set up your Excel workbook exactly the way you want it, with headings in a freeze pane at the top, and hidden gridlines, and other settings? And then one day, all of those settings disappeared? Why did that happen, and how can you prevent that from happening again?
Continue reading “Why Excel Freeze Panes Settings Disappear Prevent Problem”
Troubleshoot Excel Pivot Table Refresh Errors
If you get pivot table refresh errors, this video shows how to troubleshoot those errors and try to fix them. For example, one common error is, “The PivotTable field name is not valid”. Or, you might see this error: “PivotTable report cannot overlap another PivotTable report.”
Continue reading “Troubleshoot Excel Pivot Table Refresh Errors”
Fix Excel VLOOKUP Error Problem Video Steps
Sometimes a VLOOKUP formula returns an #N/A error, even though you can see the value you’re looking for in the lookup table. The most common cause for this is a text “number” in one place, and a real number in the other place. See how you can fix that VLOOKUP problem.
Continue reading “Fix Excel VLOOKUP Error Problem Video Steps”
How to Make Panel Chart in Excel – Video Step-by-Step
In Excel, there’s no built-in Panel Chart type, but you can create line panel charts, like the one shown below. Watch a video, to see the steps, and there’s a transcript too, and a link where you can download the sample Excel file.
Continue reading “How to Make Panel Chart in Excel – Video Step-by-Step”
Are You Proficient in Excel Like Bryan?
On TikTok, I follow Bryan Nothling aka thebnoth, because he’s funny, and he’s Canadian! His “working from home” videos have brightened my days, and he has over 200K followers, so I’m not the only person who enjoys them.
Excel Humour 2022-01-10 Excel Chart Superhero
Sometimes it’s easy to make an Excel chart, and sometimes a struggle. There are days when you feel like a superhero, if you just manage to build a simple chart, and change the colours in it!
Here are a couple of short videos that show how to work with Excel pie charts, and win the battle.
Continue reading “Excel Humour 2022-01-10 Excel Chart Superhero”
Excel YouTube Channel: MrExcel by Bill Jelen
Bill Jelen is the the king of all Excel media! In addition to his MrExcel website, and his book publishing empire, and his Excel podcasts, Bill has been on YouTube since July 23, 2006 – almost 15 years ago! Let’s take a look at it.
Continue reading “Excel YouTube Channel: MrExcel by Bill Jelen”
Friday Fun: Excel Songs by Professor Clint Tuttle
Here’s a bit of Friday fun, with Excel song videos by professor Clint Tuttle. I used to look forward to his videos, in December and April, when the school terms ended.
Continue reading “Friday Fun: Excel Songs by Professor Clint Tuttle”
Make Excel Check Boxes Look Better with Formatting Tricks
Do you ever use check boxes on your Excel worksheets? They’re handy for choosing options, or marking completed items in a To Do list.
Continue reading “Make Excel Check Boxes Look Better with Formatting Tricks”
Excel Dashboard Video Project Gantt Chart
Chandoo started a new Excel series on his blog — How to create a fully interactive Project Dashboard with Excel.
In this video for Part 1, Chandoo shows how to set up a Project Gantt Chart, and 3 more lessons in the series are coming.
Continue reading “Excel Dashboard Video Project Gantt Chart”Color Entire Row With Excel Conditional Formatting
Highlight all the cells in a row, if one cell is above or below a specific value.
To do this, use Excel conditional formatting, as described in this short Excel video tutorial.
For detailed instructions, please visit the page Excel Conditional Formatting Based on another cell
____________________
Excel Conditional Formatting Basics
To highlight cells with a value that is too high or too low, you can use Excel Conditional Formatting.
This short video shows you the steps.
For detailed instructions see Excel Conditional Formatting Basics.
________________
Create an Excel User Form
You can create a user form in Excel, to control how users are able to enter data.
These 3 short Excel video tutorials show you the steps.
For detailed instructions please visit the web page Create an Excel UserForm.
_________________
Stop GetPivotData Formula When Linking to Pivot Table
When you link to data in a pivot table, you may see a GETPIVOTDATA formula, instead of a simple cell reference.
To turn this feature off, follow the instructions in this short video.
For written instructions, please see Turn Off GetPivotData Formulas for Excel Pivot Tables.
________________
Copy Code to an Excel Workbook
If you find Excel VBA code on a website or a sample file, you can copy it to your own Excel file.
Follow the steps in this short Excel video tutorial to copy the code.
For detailed instructions please read the article Excel VBA – Adding Code to a Workbook
______________
Remove Duplicates in Excel
If I need a list of unique items from a long list, I usually use an Advanced Filter, because it leaves the original list alone, and extracts a list of unique records. But, if you want to remove duplicate items quickly and easily, there’s a new tool in Excel 2007.
Excel 2007
In Excel 2007, if there are duplicate items in a list, you can delete them by using the Remove Duplicates tool, which is new in this version.
To remove the duplicates, follow the steps in this short Excel video tutorial.
One or More Columns
In Excel 2007 or later, if there are duplicate items in a list, you can delete them by using the Remove Duplicates tool. The duplicates can be removed based on entries in a single column, or multiple columns
To remove the duplicates, follow the steps in this short Excel video tutorial.
Excel 2003
In Excel 2003, if there are duplicate items in a list, you can delete them by using an Advanced Filter.
To remove the duplicates, follow the steps in this short Excel video tutorial.
______________
Increase Excel Values By Specific Amount
You can quickly change values in Excel, without using formulas.
Follow the steps in this short video to add, subtract, multiply or divide, by a specific amount or percentage.
For detailed instructions, please visit Excel data entry tips.
______________
Excel Numbers Do Not Sum Correctly
If you copy data from another program to Excel, Excel might treat the numbers as text, and totals that use these numbers will be incorrect.
This video shows the simple steps to change the text "numbers" to real numbers. For written instructions, visit Convert Text to Numbers
______________________
Fill Blank Cells in Excel Tables
An Excel worksheet might have headings in column A, and subheadings in column B, with blank cells under each heading. If you want to sort or filter that data, you’ll need to fill in the blank cells. This short video shows you a quick way to clean up the data.
For written instructions please read the article Fill Blank Cells in Excel to Complete a Table
________________
Enable or Disable Macros in Excel
When you open a file in Excel, you might see an "Enable or Disable a Macro” message, but you know that there are no macros in the file.
Follow the steps in this video, to prevent this message from appearing.
For written instructions see: Remove Excel macro warning
______________
Name a Group of Cells for Navigation in Excel
It’s easy to name a group of cells in Excel, then use that name for navigation.
This Excel video tutorial shows you the steps.
For a written Excel tutorial on naming ranges, see naming ranges in Excel.
______________
Move the Excel 2003 Color Palette
In Excel 2003, or earlier versions, you can move the color and border palettes close to the cells you’re formatting.
To see the steps, watch this short Excel video tutorial.
For written instructions, see the move the color and border palettes in Excel notes on the Contextures website.
______
Add a Picture to an Excel Comment
Excel’s popup cell comments aren’t just for text. You can also display pictures in an Excel comment. This short Excel video tutorial explains the simple steps.
For detailed written instructions see: Add a picture to a comment
______