Data Validation Selection Popup Tool – Contextures
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
3w ago
With the free Data Validation Select Popup (DVSP) tool from Contextures, you can add popup lists to Excel workbooks. Set the rules you’d like for the pop-up lists — allow single item selection in some columns, and allow multi-selection in other columns. You can even let people add new items to some lists – if you trust them! Pop-Up List Settings The Contextures Data Validation Select Popup tool lets you show a popup list when you click on a cell that contains a data validation drop down list. Or, set the pop-up list to appear when you double-click on a data validation cell (that’s what I usu ..read more
Visit website
Troubleshoot Excel Formulas with ERROR.TYPE Function
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
1M ago
Did you know that you can use the Excel ERROR.TYPE function to identify specific types of errors on a worksheet? And after the error type is identified, you can use that information to provide help with error troubleshooting. There’s a short video below, that shows an example, and there’s a list of the Excel error values that this function can identify. Excel ERROR.TYPE Function It’s easy to use the ERROR.TYPE function in Excel. Just give it a cell reference, and it will check that cell for an error value. For example, in the screen shot below, the following formula is in cell C3. It checks fo ..read more
Visit website
Excel Text in Multiple Rows – No Merged Cells
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
1M ago
I add lots of screen shots to my Excel tutorials, so it’s easier to follow the steps. And if I’m explaining a formula, it’s helpful to show that on the worksheet too. However, some Excel formulas can get pretty long, and I found a way to fix that problem! You can see all the solutions that I tried, or skip to the end, to see the final solution. Show Excel Formulas To show an Excel formula on the worksheet, you can use the FORMULATEXT function. In the screen shot below, I put this formula in cell D4: =FORMULATEXT(D3) It shows the COUNTIFS formula that’s in cell D3, to count the number of orde ..read more
Visit website
Excel for the Holidays 2023-Planning-Games-Trees
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
3M ago
Do you use Excel to help with all of your holiday planning? It’s a great tool for shopping lists, holiday budgets, dinner preparation, and even holiday games! Holiday Dinner Planner Every time we have a holiday dinner, I use my Holiday Dinner Planner to create a timeline for the preparation steps. Just pick your dinner time from the drop down list, and the schedule readjusts automatically. Dinner Checklist The dinner planner also acts as a checklist, to ensure that everything is prepared, and on the table, at dinner time. Without that checklist, something might be left in the fridge – like th ..read more
Visit website
How to Remove Duplicate Items in Excel List
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
4M ago
There’s a built-in tool in Microsoft Excel that makes it easy to remove all the duplicate values from a list. You can remove duplicates based on the items in one column, or multiple columns. Video: How to Remove Duplicates in Excel List The video below shows the steps for removing duplicate items from an Excel worksheet list, using Excel’s built-in Remove Duplicates tool. The video timeline is below the video, and you can get the sample Excel file on my Contextures site. Video Timeline In the video, I start with the steps for working with a 1-column list. After that, duplicates are removed fr ..read more
Visit website
How to Sum Filtered Excel Data – AGGREGATE or SUBTOTAL
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
5M ago
When an Excel list is filtered, how can you show a total sum or count for numbers in the visible rows only? There are two Excel functions for that task — SUBTOTAL (all Excel versions) and AGGREGATE (Excel 2010 and later). See how these functions work, and decide which one to use. Excel SUBTOTAL Function Should you use SUBTOTAL or AGGREGATE to get a total for filtered data? There’s a video further down the page, where I show both functions, along with the Excel SUM function. That might help you decide which function will work best for you. Here are a couple of reasons to choose the SUBTOTAL fun ..read more
Visit website
Video: Dependent Combo Boxes – Excel Data Entry
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
5M ago
Make it easy to enter valid data in an Excel workbook. Choose “Fastener” in one combo box drop down. The next combo box shows a list of fastener parts, with part ID and part name. This video shows how to use this simple data entry tool, and you’ll get a peek behind the scenes, on how it works. Video Timeline Here’s the video timeline, and the full video transcript is at the end of this post: 0:00 Custom Tab on Excel Ribbon 0:17 Excel UserForm Opens 0:37 Add Part Order 0:55 See Lists Sheet 1:25 See How Macro Works 1:50 Named Range for Parts List 2:00 UserForm Macro Code Dependent Combo Box D ..read more
Visit website
Hide Excel Hyperlink-Show Monthly-Conditional Format
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
6M ago
In your job, are there Excel tasks that need to be done at month end? Here’s how I use a hidden hyperlink to help with my month end routine. Make Month End Entries In one of my workbooks, I have to add a couple of entries on a worksheet, before I start with the new month’s entries. Sometime (frequently!), I forget to do that. That made extra work for me, because I had to go back, find the numbers, and enter them later. So, to remind myself about that month end step, I made a hidden hyperlink in the workbook. Start a Hyperlink To create the Excel hyperlink, I used the Insert Hyperlink command ..read more
Visit website
How to Prevent Duplicate Entries in Excel Column
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
6M ago
In some workbooks, you might need to block duplicate entries in a column. For example, we don’t want 2 employees to have the same ID number. See how to set up a custom rule for that, with data validation. And keep reading, to see why COUNTIF can cause problems for you. Block Duplicates in Column When you’re working in Excel, you can use data validation to prevent duplicates from being entered in a column. For example, in the video below, an Employee ID is entered in column A, and each ID must be unique. If you try to enter a duplicate ID number, a data validation rule will block you, and a mes ..read more
Visit website
Quickly Move Sheet in Large Excel File
Contextures Blog - Excel Tips and Tutorials
by Debra Dalgleish
7M ago
Do you ever find yourself scrolling through an endless number of worksheet tabs, while you’re working in a large Excel file? It’s happened to me, more times than I can count, while working in my own files, or client workbooks. Here’s a quick tip that might help you save some time! Video: Quickly Move Sheet Scrolling through a large workbook can be painfully slow, and it seems to take twice as long if you’re dragging a sheet tab, to move it to a different spot in the workbook. This short video shows a quick tip for moving sheets, so you don’t have to waste time dragging them a long distance! Y ..read more
Visit website

Follow Contextures Blog - Excel Tips and Tutorials on FeedSpot

Continue with Google
Continue with Apple
OR