Contextures

Contextures News 20190212

Excel Order Form Setup

February 12, 2019

Show pivot table numbers as text, order form setup, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Order Form Setup

Instead of using Excel files that someone else set up, there are times when you have to create a workbook of your own. If you want to set up an order form in Excel, my latest video shows how to do that, step by step.

To watch the video, go to my Order Form page. There's a startup workbook, with lists of products and customers, that you can use to follow along with the video. You'll see how to add drop down lists, find prices with VLOOKUP formulas, and add other calculations, to complete the order form.

My favourite formula is in cell B7 - it does three lookups, to get the city, province and postal code:

=IF(B5="","", VLOOKUP(B5,CustLookup,3,FALSE) & ", " & VLOOKUP(B5,CustLookup,4,FALSE) & " " & VLOOKUP(B5,CustLookup,5,FALSE))

The Order Form page has written instructions too, and there's a completed version of the order form, if you want to download that.

Excel order form

Show Numbers as Text

In an Excel pivot table, the Values area will only show numbers. If you add a text field there, Excel shows a count of those text items. However, with conditional formatting and custom number formats, you can show number fields as text.

For example, instead of showing region ID numbers (1, 2 and 3), show the region names (East, Central, West). Here is the custom number format to show "East" for any cell that contains a 1:

[=1]"East";;

  • The 2 semi-colons at the end tell Excel to show nothing for for negative numbers or zeros

custom number format shows text

After applying that format, with cell B6 selected, you can see a 1 in the Formula Bar, even though "East" is showing on the worksheet.

custom number format shows text

To see all the steps for setting this up, go to the Pivot Table Text Values page on my Contextures website. There is also a macro there, thanks to Prof. Lee Townsend, and you can use it to automate the setup.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Beer - Larry Carpenter is a full-time engineer, and part-time brewmaster. Even if you're not interested in beer, his brewing spreadsheet is worth a look. It's free, with no macros, and a video explains how it works. (Level - All)

Excel Versions - This Microsoft ad is strange - I've never seen a company try to make one of its own products look bad! (Level - All)

Dashboards - A reminder that registration will close on Thursday, Feb. 14th, for Mynda Treacy's dashboard courses. Click here for details on the Excel Dashboard Course or the Power BI Course

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Museum of Play

Next Monday is Family Day, here in Ontario, so we can relax and play a few indoor games with our grandkids. If the weather is nice, we could play outdoors too, in one of our beautiful provincial parks.

A few years ago, we took our granddaughter to The Strong National Museum of Play, in Rochester, New York. It's an amazing place, so go for a tour, if you're ever in Western NY. The trolls in the museum are friendlier than the trolls on the Internet!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190212ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsdalg@ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: February 8, 2019 12:44 PM