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.
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.
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:
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.
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.
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)
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!
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.
Last updated: February 8, 2019 12:44 PM