contexturesblog.com Open in urlscan Pro
172.67.145.209  Public Scan

Form analysis
4 forms found in the DOM

POST https://assets.mailerlite.com/jsonp/951561/forms/121604431454143503/subscribe

<form class="ml-block-form" action="https://assets.mailerlite.com/jsonp/951561/forms/121604431454143503/subscribe" data-code="" method="post" target="_blank">
  <div class="ml-form-formContent">
    <div class="ml-form-fieldRow ml-last-item">
      <div class="ml-field-group ml-field-email ml-validate-email ml-validate-required">
        <!-- input -->
        <input aria-label="email" aria-required="true" type="email" class="form-control" data-inputmask="" name="fields[email]" placeholder="Email" autocomplete="email" aria-invalid="false">
        <!-- /input -->
        <!-- textarea -->
        <!-- /textarea -->
        <!-- select -->
        <!-- /select -->
        <!-- checkboxes -->
        <!-- /checkboxes -->
        <!-- radio -->
        <!-- /radio -->
        <!-- countries -->
        <!-- /countries -->
      </div>
    </div>
  </div>
  <!-- Privacy policy -->
  <!-- /Privacy policy -->
  <input type="hidden" name="ml-submit" value="1" aria-invalid="false">
  <div class="ml-form-embedSubmit">
    <button type="submit" class="primary">Subscribe</button>
    <button disabled="disabled" style="display: none;" type="button" class="loading">
      <div class="ml-form-embedSubmitLoad"></div>
      <span class="sr-only">Loading...</span>
    </button>
  </div>
  <input type="hidden" name="anticsrf" value="true" aria-invalid="false">
</form>

POST https://assets.mailerlite.com/jsonp/951561/forms/121604431454143503/subscribe

<form class="ml-block-form" action="https://assets.mailerlite.com/jsonp/951561/forms/121604431454143503/subscribe" data-code="" method="post" target="_blank">
  <div class="ml-form-formContent">
    <div class="ml-form-fieldRow ml-last-item">
      <div class="ml-field-group ml-field-email ml-validate-email ml-validate-required">
        <!-- input -->
        <input aria-label="email" aria-required="true" type="email" class="form-control" data-inputmask="" name="fields[email]" placeholder="Email" autocomplete="email" aria-invalid="false">
        <!-- /input -->
        <!-- textarea -->
        <!-- /textarea -->
        <!-- select -->
        <!-- /select -->
        <!-- checkboxes -->
        <!-- /checkboxes -->
        <!-- radio -->
        <!-- /radio -->
        <!-- countries -->
        <!-- /countries -->
      </div>
    </div>
  </div>
  <!-- Privacy policy -->
  <!-- /Privacy policy -->
  <input type="hidden" name="ml-submit" value="1" aria-invalid="false">
  <div class="ml-form-embedSubmit">
    <button type="submit" class="primary">Subscribe</button>
    <button disabled="disabled" style="display: none;" type="button" class="loading">
      <div class="ml-form-embedSubmitLoad"></div>
      <span class="sr-only">Loading...</span>
    </button>
  </div>
  <input type="hidden" name="anticsrf" value="true" aria-invalid="false">
</form>

GET https://contexturesblog.com/

<form role="search" method="get" class="search-form" action="https://contexturesblog.com/">
  <label>
    <span class="screen-reader-text">Search for:</span>
    <input type="search" class="search-field" placeholder="Search …" value="" name="s">
  </label>
  <button type="submit" class="search-submit"><span class="screen-reader-text">Search</span></button>
</form>

POST https://assets.mailerlite.com/jsonp/951561/forms/121604431454143503/subscribe

<form class="ml-block-form" action="https://assets.mailerlite.com/jsonp/951561/forms/121604431454143503/subscribe" data-code="" method="post" target="_blank">
  <div class="ml-form-formContent">
    <div class="ml-form-fieldRow ml-last-item">
      <div class="ml-field-group ml-field-email ml-validate-email ml-validate-required">
        <!-- input -->
        <input aria-label="email" aria-required="true" type="email" class="form-control" data-inputmask="" name="fields[email]" placeholder="Email" autocomplete="email" aria-invalid="false">
        <!-- /input -->
        <!-- textarea -->
        <!-- /textarea -->
        <!-- select -->
        <!-- /select -->
        <!-- checkboxes -->
        <!-- /checkboxes -->
        <!-- radio -->
        <!-- /radio -->
        <!-- countries -->
        <!-- /countries -->
      </div>
    </div>
  </div>
  <!-- Privacy policy -->
  <!-- /Privacy policy -->
  <input type="hidden" name="ml-submit" value="1" aria-invalid="false">
  <div class="ml-form-embedSubmit">
    <button type="submit" class="primary">Subscribe</button>
    <button disabled="disabled" style="display: none;" type="button" class="loading">
      <div class="ml-form-embedSubmitLoad"></div>
      <span class="sr-only">Loading...</span>
    </button>
  </div>
  <input type="hidden" name="anticsrf" value="true" aria-invalid="false">
</form>

Text Content

Skip to content


CONTEXTURES BLOG

Excel tips and tutorials

Menu
 * Home
 * Aboutexpand child menu
   * Contact
   * Products
   * Videos
 * Excel Topicsexpand child menu
   * Start Here
   * Charts
   * Data Validation
   * Excel Tips
   * Filters
   * Formatting
   * Formulas
   * Macros
   * Pivot Tables
 * Privacy




DEBRA’S EXCEL NEWS–JULY 2024

There are bad characters, musical spreadsheets, and more, in this month’s Excel
news.

 * AutoFit Specific Cells
 * Quick Grand Totals
 * Bad Characters
 * Number Keypads
 * Spreadbeats
 * Monthly Photo


NEW ON CONTEXTURES

Pivot Table Tips: Make your Excel pivot tables look better with these 3 quick
layout tips. One tip – AutoFit Cells – is described below

Overlap Error: How to find and fix those annoying Excel pivot table overlap
errors.


AUTOFIT CELLS

I increased the font size on a worksheet recently, and that messed things up, as
you can see in the pivot table shown below. In cells where numbers didn’t fit in
the larger font size, Excel showed number signs instead.



To quickly fix a few key columns, here’s what I did:

 1. First, I selected the Grand Total cells, C9:E9 (totals usually have the
    biggest numbers)
 2. Next, I went to the Excel Ribbon’s Home tab, and clicked Format > AutoFit
    Column Width, to fix the selected cells
 3. After that, I selected the grand total cells in another pivot table on that
    sheet
 4. Then pressed the F4 key, to quickly repeat the AutoFit command

To show the steps, I made a short video, and you can see that video on my Excel
Worksheet Tips page.


QUICK GRAND TOTALS

Use this trick to get quick totals for rows, columns and the grand total. Excel
automatically adds the formula in each cell!

 1. First, select the range of cells that you want to total, as well as the
    cells where you want the totals — cells B2:E6 in the example below
 2. Next, press the AutoSum keyboard shortcut: Alt + =



You can see more ways to sum in Excel, on my Contextures site.


BAD CHARACTERS

Do you know which character is “bad”, and is NOT allowed in an Excel sheet tab
name?

 1. ; semi-colon
 2. + plus sign
 3. ? question mark
 4. # number sign

I asked that question on my YouTube channel, and only 52% of the people picked
the right answer. You can see the correct answer, further down in this email,
below the monthly photo.

Fun Fact: You can use an apostrophe in a sheet name, but not at the beginning or
end of the name.


NUMBER KEYPADS

Do you use your keyboard’s number pad, while working in Excel? Or do you use the
numbers across the top of the keyboard?

I loved this article about the design decisions in touch-tone phones, that
carried over to our cell phone keypads. The number layout is different from our
computer’s number keypad, which carried over from old adding machines and
calculators.

And here’s a key takeaway, if you’re designing an Excel workbook, or anything
else: “What people say they prefer is not necessarily what works best.”


SPREADBEATS

This YouTube video shows Spreadbeats, an amazing music video, created entirely
in Excel, by Spotify’s media company. That sets the bar pretty high for the rest
of us who work in Excel!

And this video, on the Spotify Ads YouTube channel, gives more details about
Spreadbeats, but doesn’t include a sample file for us to download!


YOU’VE GOT THIS!

It’s been a long time since I worked on a jigsaw puzzle, so I started this one
with high hopes. Despite the 500-piece puzzle’s positive message, “You’ve Got
This!”, I only completed the edges, and the words in the centre. It was fun, but
a real strain on my eyes. So here’s my motivational message for you — “That’s
good enough!”




QUIZ – CORRECT ANSWER

In the Bad Characters quiz, if you picked the question mark, congratulations —
you got the right answer! In Excel sheet names, the following 7 characters are
not allowed:

        :     \    /    ?    *   [    ]


GET MONTHLY EXCEL TIPS!

Don’t miss my monthly Excel newsletter! You’ll get quick tips, article links,
and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to
protect you from spam!

Get Debra's monthly Excel news! 

Subscribe

Loading...

THANK YOU!

You have successfully joined our subscriber list.


STAY IN TOUCH!

That’s it for this month, and you’ll get my next monthly newsletter on Tuesday,
August 13th!

If you have any comments or questions, please let me know.

– – Debra

P.S. Visit my Contextures site for more Excel tips, tutorials and videos.

Author Debra DalgleishPosted on July 9, 2024July 8, 2024Categories Excel
NewsLeave a comment on Debra’s Excel News–July 2024


DEBRA’S EXCEL NEWS – JUNE 2024

There’s an Excel calendar, Excel lookup functions, and more, in this month’s
Excel news.

 * Excel Event Calendar
 * Quick Ribbon Tip
 * Excel Lookup Functions
 * Excel and AI
 * Monthly Photo


EXCEL EVENT CALENDAR

Long ago, I made an Excel holiday planning calendar that showed a list of events
under each date. The calendar worked, but it needed lots of helper columns.

Now I’ve made a new version, and it shows up to 5 events per day, sorted by
time. It also warns you if a date’s list runs out of room (red italic font).

You can see more details, and get a copy of this event calendar, on my
Contextures site. The calendar is built with formulas only – no macros.

Excel Event Calendar


QUICK RIBBON TIP

Do you ever waste time, looking for a rarely-used command on the Excel Ribbon?

If your mouse has a scroll wheel, here’s a quick way to check each tab for a
“lost” command:

 * If the Ribbon is collapsed, double-click any tab, to expand the Ribbon
 * Next, point anywhere on the Ribbon
 * Then, on your mouse, slowly roll the scroll wheel
   * toward you, to move right
   * away from you, to move left

You can see more Excel Ribbon tips, on my Contextures site.

quickly scroll through Ribbon tabs


EXCEL LOOKUP FUNCTIONS

Which of these lookup functions do you use most often?

 * VLOOKUP, XLOOKUP, INDEX/MATCH, or LOOKUP?

When I asked that question online, VLOOKUP was the overall winner:

 * 45% VLOOKUP
 * 35% XLOOKUP
 * 17% INDEX/MATCH
 *   3% LOOKUP

The results varied by voting location – Twitter, YouTube, and my Contextures
Blog. For now, YouTube people are sticking with VLOOKUP, but I hope they move to
XLOOKUP soon!

Excel Lookup Functions Poll results


EXCEL AND AI

Is AI here to help us, or could it make our jobs obsolete? In this Wall Street
Journal article, the consensus is that AI won’t make spreadsheets disappear,
anytime soon!

Apparently, corporate AI tools don’t work well if:

 * “critical data is stored across multiple systems…and employees end up
   downloading it all into spreadsheets”

Meanwhile, some people are using Microsoft Copilot, to get help with Excel
formulas and VBA code. If you’ve tried Copilot, did you get good results?


AXE ME ANYTHING!

It’s Father’s Day on Sunday, so we’ll celebrate with afternoon snacks on the
back deck. It’s nice to stare at trees and flowers for a while, instead of
computer screens.

And, for a change of pace, we’ll throw a few axes at our backyard target! We got
ours at Costco, and the same set is sold on Amazon.




GET MONTHLY EXCEL TIPS!

Don’t miss my monthly Excel newsletter! You’ll get quick tips, article links,
and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to
protect you from spam!

Get Debra's monthly Excel news! 

Subscribe

Loading...

THANK YOU!

You have successfully joined our subscriber list.

That’s it for this month! Thanks for reading my Excel news, and you’ll get next
month’s email on Tuesday, July 9th.

If you have any comments or questions, let me know!

— Debra

P.S. Visit my Contextures site for more Excel tips, tutorials and videos.

Author Debra DalgleishPosted on June 11, 2024June 11, 2024Categories Excel
NewsLeave a comment on Debra’s Excel News – June 2024


WHICH EXCEL LOOKUP FUNCTIONS DO YOU USE MOST OFTEN?

I’ve got an Excel  lookup functions question for you, in the quick poll below.
And I’ve got some breaking news for you too!

Continue reading “Which Excel Lookup Functions Do You Use Most Often?”

Author Debra DalgleishPosted on June 6, 2024June 5, 2024Categories Excel
Formulas5 Comments on Which Excel Lookup Functions Do You Use Most Often?



EXCEL FILE AUTO BACKUP WHEN WORKBOOK SAVED

Do you hit the Save button frequently, while you work in Excel? And do you make
backup copies of your Excel files, especially before making major changes? Or,
do you only think about saving, right after Excel crashes?

Continue reading “Excel File Auto Backup When Workbook Saved”

Author Debra DalgleishPosted on May 16, 2024May 15, 2024Categories Excel VBA1
Comment on Excel File Auto Backup When Workbook Saved


EXCEL DEPENDENT DROP DOWN LISTS – BLOCK CHANGES

One of my favourite Excel features is a data validation drop down list. In just
a couple of minutes, you can make a list of items, then make that list appear in
a worksheet cell. It’s like magic!

The drop down lists make it much easier to enter data, and they help prevent
typos, or invalid entries.

The steps for making a drop down list, using data validation, are on my
Contextures site.

Excel drop down list with data validation

Continue reading “Excel Dependent Drop Down Lists – Block Changes”

Author Debra DalgleishPosted on April 25, 2024April 24, 2024Categories Excel
Data ValidationLeave a comment on Excel Dependent Drop Down Lists – Block
Changes



DATA VALIDATION SELECTION POPUP TOOL – CONTEXTURES

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 usually do).

In the pop-up list, you can quickly see all the items in the list, instead of
slowly scrolling through a long list.


SINGLE SELECTION OR MULTI-SELECT

In some columns, you can allow a single selection only. In the screen shot
below, you can only select one of the three countries from the list.



However, in some columns, the multi-select option might be allowed.

For example, in the screen shot below, you can pick one or more of the cities
from the pop-up list.




DEPENDENT DATA VALIDATION

Another helpful feature is support for dependent data validation.

For example, in the screen shot above, the city column has dependent data
validation.

 * Canada was selected in the Region column
 * Only the Canadian cities are the in pop-up list for the City column


VIDEO: WATCH THE DVSP DEMO

First, to see how the multiple selection popup lists works, you can watch this
short video.

To follow along with the DVSP demo, go to the Data Validation Select Popup page,
and download the DVSP Demo workbook.

 * Note: The DVSP tool is a free version of the popular DVMSP tool that I sold
   for several years. In the videos, screen shots and written steps, you might
   see “DVMSP”, instead of “DVSP”.




VIDEO: HOW TO USE DVSP SETUP FILE

Next, you can watch this video to see the DVSP setup file steps, and there are
written instructions in the Setup file.




EXCEL SKILLS NEEDED

To use the DVSP tool, you will need the following Excel skills, to get your file
ready:

 * create a named range (for the list of items) — there are instructions and a
   video here: Create a Named Range
 * create drop down list(s) on the worksheet, based on a named range — there are
   instructions and a video here: Create a Drop Down List


GET THE DVSP EXCEL FILES

To get the Contextures DVSP Setup file, and DVSP Demo file, go to the Data
Validation Select Popup page, and go to the Download section.

Be sure to unblock the zipped files after you download them, or they won’t work
correctly. There are steps to Unblock Files on my Contextures site.

 * NOTE: There is no technical support provided for the DVSP tool. Use the tool
   as is, and at your own risk.




RELATED DVSP PAGES

For more DVSP tips and instructions, visit the following pages:

— Data Validation Multi-Select Popup page

— DVSP User Guide page

— DVSP Frequently Ask Questions (FAQ) page

____________


DATA VALIDATION SELECTION POPUP TOOL – CONTEXTURES



____________

Author Debra DalgleishPosted on March 7, 2024April 23, 2024Categories Excel Data
ValidationLeave a comment on Data Validation Selection Popup Tool – Contextures



TROUBLESHOOT EXCEL FORMULAS WITH ERROR.TYPE FUNCTION

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.

Continue reading “Troubleshoot Excel Formulas with ERROR.TYPE Function”

Author Debra DalgleishPosted on February 22, 2024February 21, 2024Categories
Excel FormulasLeave a comment on Troubleshoot Excel Formulas with ERROR.TYPE
Function


EXCEL TEXT IN MULTIPLE ROWS – NO MERGED CELLS

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.

Continue reading “Excel Text in Multiple Rows – No Merged Cells”

Author Debra DalgleishPosted on February 8, 2024February 8, 2024Categories Excel
FormulasLeave a comment on Excel Text in Multiple Rows – No Merged Cells


EXCEL FOR THE HOLIDAYS 2023-PLANNING-GAMES-TREES

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!

Continue reading “Excel for the Holidays 2023-Planning-Games-Trees”

Author Debra DalgleishPosted on December 21, 2023December 20, 2023Categories
Excel TemplatesLeave a comment on Excel for the Holidays
2023-Planning-Games-Trees


HOW TO REMOVE DUPLICATE ITEMS IN EXCEL LIST

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.

Continue reading “How to Remove Duplicate Items in Excel List”

Author Debra DalgleishPosted on November 30, 2023November 28, 2023Categories
Excel FilterLeave a comment on How to Remove Duplicate Items in Excel List


POSTS NAVIGATION

Page 1 Page 2 … Page 137 Next page
Search for: Search

 * Contextures Excel Tips
 * Debra's Blog
 * Pivot Table Blog
 * Spreadsheet Day Blog

As an Amazon Associate I earn from qualifying purchases

Get Debra's monthly Excel news! 

Subscribe

Loading...

THANK YOU!

You have successfully joined our subscriber list.














 * Home
 * Aboutexpand child menu
   * Contact
   * Products
   * Videos
 * Excel Topicsexpand child menu
   * Start Here
   * Charts
   * Data Validation
   * Excel Tips
   * Filters
   * Formatting
   * Formulas
   * Macros
   * Pivot Tables
 * Privacy

Contextures Blog Proudly powered by WordPress


Update Privacy Preferences
A Raptive Partner Site