Call 1300 885 420
iTandCoffee | Patient help with your personal and business technology
  • Home
  • About Us
  • Classes
  • Appointments
  • Guides | Videos | Tips
    • Guides
    • Videos
    • Blog
    • Newsletter
    • Tips Library
  • Gift Vouchers
  • iTandCoffee Club
    • About the Club
    • Membership Options
    • Members Area
  • Store
  • Contact
  • Home
  • About Us
  • Classes
  • Appointments
  • Guides | Videos | Tips
    • Guides
    • Videos
    • Blog
    • Newsletter
    • Tips Library
  • Gift Vouchers
  • iTandCoffee Club
    • About the Club
    • Membership Options
    • Members Area
  • Store
  • Contact
Search by typing & pressing enter

YOUR CART

Tips, Tricks and News 
about all sorts of technology in your life!

16/11/2020 0 Comments

How to quickly convert a whole column of numerics/non-numerics to text in Excel

Picture
Image by Pexels from Pixabay
What I love about my job is that, nearly every day, I have one of those "I didn't know that!" moments - for example, where I find out there is a quick way to do something that was otherwise quite tedious and potentially error prone.

I have regularly come across the problem in Excel where a column includes values that are generally numeric, but can also sometimes include an alphabetic character. Excel will often assume that the numeric value is number, when I really need the whole column to be treated as text. 

When this happens, it can cause problems for any formulae that try matching against the column values - if the formulae expects text, but the value stored is numeric. Changing the overall column format to text is not enough - the numeric values already in the column remain as numbers even when this is done.

In preparing one of the recent Boot Camp sessions for Microsoft Excel, I discovered that that there is such an easy way to convert a column like that so that all entries are text - with no need for formulae or copying and pasting values (which is what I used to do!)
Members of the iTandCoffee Club can access this week's handy hint, which looks at how to use an option in the Data tab of Excel to quickly and easily format an entire column as text.
Format an Excel column as text

Many more great tips like this for Excel users

This is just one of the many great tips covered in the Boot Camp for Excel class series, video series and book.

​We will run the class series again soon (see below for any scheduled dates) - but those of you interested in learning more in your own time can access all these great tips any time by purchasing access to the videos an/or purchasing the book that accompanies the video series.

​Here are the details.

On Sale

On Sale

Microsoft Excel Bootcamp - Access to 8-Part Video Series (90 days)

A$200.00 A$150.00

Purchase 120 days' access to watch all 9 parts the Microsoft Excel Boot Camp series of videos. These 9 parts are being added each week over the period of 8/8/20 to 10/10/20 (excluding 28/8/20). (Note that the access period will become 90 days for purchases made after 10/10/20.)


Here's what's covered in each part:


Week 1 – Getting Started


In this first session of our Microsoft Excel Boot Camp, we start at the very beginning – creating a new Excel workbook and understanding Excel terminology – Workbook, Worksheet, Cell, Rows, Columns, Formulae. We take a guided tour of the main areas of the Excel app (the Ribbons); we look how to easily ‘zoom ‘your document to a size that suits your eyes; saving your document; re-opening your Excel file; and understanding where OneDrive fits in to the picture (and whether to save your Excel files there). We also look at the Templates that you can use to get started on your spreadsheet.


Week 2 – Creating a customer list with some simple formatting and formulae


In this second session, we use the example of a customer list, looking at: entering some simple data into your list; re-sizing columns and rows (manually and using ‘autofit’); text wrapping, entering different types of data into cells (eg text, integers, numbers, dates, dollars); how to select one or more cells (or whole rows / columns); formatting cells (size, bold, colour, background colour, number format, date format); selecting rows, columns and cells; inserting and deleting columns and rows. We then look at some basic formulae for adding the content of rows and columns, and how to copy formulae and formatting across the multiple cells.


Week 3 – Creating a standard invoice with calculations for GST and totals 


In Part 3, we look at the example of a standard invoice: how to format this invoice as an A4 sized page to include a business logo and business details; how to set up your invoice for entry of the items you wish to charge for; setting rows and columns to automatically calculate GST and totals; hiding the grid lines in printed version; including borders around required areas; turning the invoice into a Template that you can use over and over and how to access that template when you need it; setting up your worksheet so that it fits to a single page width; converting that invoice into a PDF that you can send via email and/or save somewhere on your computer/device.


Week 4 – Sorting, filtering, totals/sub-totals and finding/removing duplicates


Part 4 looks at working with data that needs sorting, sub-totalling, grouping or that has duplicates that need to be removed. We also look at the wonderful ‘filter’ options available, to allow you to more easily find the data you are looking for in what may be a very long list of rows – based on whatever criteria you specify.


Week 5 – Basic & Useful formulae PLUS data validation 


In part 5, we focus in this part on using Formulae in your workbook – looking at some of the more common formulae that relate to data in a single worksheet, and across multiple worksheets. We also look at how to set up data validations, which can help with data entry and consistency – setting up a ‘pick list’ of values and how this can ensure entered data is limited to this list.


Week 6 – Importing/exporting CSV files, working with sheets, conditional formatting


In Part 6, we look at importing a ‘comma-delimited’ file (a file in text format, with commas – or some other ‘delimiter’ separating the fields) into Excel format, and at how to create a CSV file from a sheet in your Excel workbook. We look at working with multiple sheets – adding, copying, renaming, moving, creating another workbook from a sheet, hiding, and protecting sheets. We also look at setting up conditional formatting that will automatically bold, colour, underline (or any other format) cells, columns and rows in your sheet.


Week 7 – Summarising and Reporting your data - Pivot Tables and Charts


Part 7 looks at how utilise the Pivot Table feature to see your data represented in a summarised format, counts, totals, averages and other functions reflected in this summarised format. We also look at presenting your data in charts that provide a wonderful visual representation of what is otherwise a flood of text and numbers!


Week 8 – All sorts of handy tips, shortcuts and formulae


In Part 8, we look at really handy shortcuts, features, formulae and tips – to help you use Excel efficiently and effectively. This session will capitalise and expand on what we have covered in earlier sessions, quick ways of achieving things that require several keystrokes and use of the mouse abd discovering great formulae that you may not realised are available.


Shop

On Sale

On Sale

Excel Boot Camp (Microsoft 365) - Compilation of Parts 1-8 (Paperback)

A$75.00 A$39.90

This 159-page paperback guide has been published as a companion to the Microsoft 365 Boot Camp for Excel video series from iTandCoffee, so is designed to be used in conjunction with that series. 


It has been produced based on the content of the slideshow from that video series, so the style is ‘bullet points’ rather than detailed descriptions. Refer to the corresponding video for the more detailed descriptions of each of the areas covered in this document.


It reflects the descriptions and screenshots as shown in parts 1-8 of the video series - see details of contents of each part here. To purchase access to watch the videos, visit this page.


This paperback is available for pickup from iTandCoffee in Camberwell (Melbourne, Australia), or can be delivered to any location in Australia (delivery fees apply).


We can also ship to other countries.


Those who are located in the US and other locations can choose to order directly via our publisher Blurb, for delivery to any location in the world. Shipping costs direct purchases from the publisher are generally less than those applicable to purchases from iTandCoffee.


Here's the link to order for delivery to overseas addresses.

Shop

On Sale

On Sale

Excel Boot Camp (Microsoft 365) - Compilation of Parts 1-8 (PDF)

A$55.00 A$25.00

This 159-page PDF guide is a companion to the Microsoft 365 Boot Camp for Excel video series from iTandCoffee, so is designed to be used in conjunction with that series. 


It has been produced based on the content of the slideshow from that video series, so the style is ‘bullet points’ rather than detailed descriptions. Refer to the corresponding video for the more detailed descriptions of each of the areas covered in this document.


It reflects the descriptions and screenshots as shown in parts 1-8 of the video series - see details of contents of each part here. To purchase access to watch the videos, visit this page.


Important information before you purchase (please read): 


After purchase of this product, you will be provided a link to download the purchased PDF file to your device - this link will be valid for 3 days, to allow you to complete the download to your device. We recommend opening the downloaded book in the Books app if you have an iPad.


If you are not familiar with the process of downloading files in Safari on the iPad/iPhone (as this has changed since iOS 13), here is a web page that you should read before purchasing.


The email you receive confirming your purchase will include a link to these instructions at the bottom - in case you need to refer to them again.


On your computer or other device, please look for the downloaded PDF file in your standard Downloads location.

Shop

Scheduled Excel Boot Camp classes

If no classes are currently shown below, you can register your interest in future classes here.
0 Comments



Leave a Reply.

    Receive great articles like this 
    fortnightly to your Inbox

    Picture

    Follow us on Facebook

    iTandCoffee

    Picture

    Join the iTandCoffee Club

    ​for free content, tutorials, videos, classes, offers, discounts and more

    Join our growing community of local and online members who love learning about their technology - through FREE club sessions, remote/phone support, online tips, tricks and tutorials, special members-only offers and discounts, and more. Membership starts at only $49/year.
    Find out more about The iTandCoffee Club

    What's on at iTandCoffee ?

    Join us for a short, fun 'topic of the day' classes known as PTT sessions (Personal Training for your Technology fitness!) - these are run on a regular basis. Or join any of our other classes shown below to learn so much about your technology.
    If you have questions, why not join our The iTandCoffee Club to attend fun and informative 'user group' meetings.
    All classes are run as online classes (using Zoom) which means you can attend from anywhere.

    Archives

    May 2022
    April 2022
    March 2022
    February 2022
    January 2022
    December 2021
    November 2021
    October 2021
    September 2021
    August 2021
    July 2021
    June 2021
    May 2021
    April 2021
    March 2021
    February 2021
    January 2021
    December 2020
    November 2020
    October 2020
    September 2020
    August 2020
    July 2020
    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    January 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    December 2017
    November 2017
    October 2017
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015
    August 2015
    July 2015
    June 2015
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    July 2014
    June 2014
    May 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013
    October 2013
    September 2013
    August 2013
    July 2013
    June 2013
    May 2013
    April 2013
    March 2013
    February 2013
    January 2013
    November 2012
    October 2012
    September 2012
    August 2012
    May 2012

    Categories

    All 1800-number Accessibility Accessories Activity Monitor Adware Airdrop Airplay Airpods Alarms Alerts Android Apple Id Apple-id Appleid.apple.com Apple Music Apple News Apple-pencil Apple Tv Apple Watch Apps App Store App Windows Apss Audio Autolock Automatic-downloadsupdates Automator Backup Backups Battery Be-connected Be-connected Bigpond Big Sur Bitcoin Blockchain Bluetooth Books Boot Camp Business Buying Guide Calendar Camera Catalina Cellular Data Centrelink Channel 9 Charging Christmas Chrome Classes Client Stories Cloud Cloud-storage Cloud-storage Club Club News Colours Com Contacts Continuity Control-center Control Centre Convert Cookies COVID COVID 19 COVID-19 Cybersafety Datad777f385d3 Data Usage Delete Desktop Dictation Dock Do Not Disturb Downloads Dropbox Education El-capitan Email Emergency Emojii Entertainment Ethernet Evernote Excel Facebook Family Family History Files Finance Finder Find My Find My Car Find-my-iDevice Fingerprint Sensor Fonts Force Reboot Forgetting Curve Fraud Free Friday Gift Ideas Gmail Google Google Flights Google Photos Guides Handoff Handy Hints Hard-drive-crash Health High Sierra Home Button Homepod Home Screen Hotmail Housekeeping Humour ICloud ICloud Backup ICloud Drive ICloud Family ICloud Photo Library Identity Theft Images Image-to-text IMAP IMessage Imovie Instagram Internet Internet Issues Internet-options Ios Ios-11 Ios-11 Ios-12 IOS 13 IOS 14 IOS 15 Ios Photos Ipad IPad Tips Iphone IPhone 12 IPhone 13 Iphone8 Iphone-8 Iphone Basics Iphone-calendar Iphone-settings Iphonex Iphone-x Ipod Itandcoffee Itandcoffee Club Itandcoffee News Itandcoffee Services Itandcoffee-swap-shop Itandwine Itunes Junk Keyboard Keychain Kidsandtechnology Kids And Technology Lifestyle Mac Mac-basics Mackeeper Mac Mail Mac-mail Mac-monday Macos Mac Photos Mac-photos Mac Tutorials Mac-videos Magsafe Mail Malware Maps Measure Media Medical-id Messages Microphone Microsoft Microsoft 365 Mobile Banking Mobile Data Mobile-data Mobile Porting Mojave Monterey Movies Multitasking Music My-health-record NBN Netflix Notes Notifications Number Porting Office 365 Onedrive Onenote Online-safety Online-safety Optusnet Os-x Outlook Pages Parental Controls Passwords Paypal Pdf Personal-hotspot Phishing Phone Photo Book Photo-books Photos Photos-on-mac Podcasts Popups Powerpoint Preview Printing Privacy Productivity Product Launch Publicity Publicityads57a389ac69 Purchasing-an-idevice Qa QR Code Quicktime Raise-to-wake Ransomware Reachability Reboot Recommendations Recovery Relay Reminders Remotewipe Repairs Restore Restrictions Rings Rotation-lock Router Safari Safari-favourites Samsung Santa Scam Scam Email Scams Scan Scannable Scanning School-ipad Scoopit Screen Screen-brightness Screensaver Screenshots Screen Time Scrolling Secondhand-devices Security Sell Seniors Seo Services Sessions Settings Share Shared-calendar Shop Shopping Shortcuts Sierra Signature Silent-auction Sim Siri Skype Slideover Slides Slideshow Slideshows Slow-device Slow-mac Smart-tv Sms Snapfish Socialmedia Social Media Softwareupdate Software Update Software Updates Sounds Spam Spinning-beachball Split Screen Split View Spotlightsearch Startup Storage Storage Space Subscriptions Sunrise Switch Symbols System Preferences Task Manager Tasks Taskswitcher Task-switcher Technology Technologyarticles Technologydependency Technology-lifestyle Technology-solutions Technologytips Techsupport Telco Telcos Telstra Telstracomplaint Text Text Replacements Theft The-katering-show Thermomix Thesenior Timemachine Time-to-leave Tio Toys Trackpad Training Videos Transcribe Translate Travel Travel-sim Tripadvisor Troubleshooting Tutorial Tutorials Two Factor Authentication Twofactor-authentication Two-step-verification Typing Typing Shortcuts Undo Unlock Update Updates Upgrade Uppercase Usage User Group Utilities Video Video-blog Videos Virus Voice-and-data Voicemail Voice-memos Volume Vpn Wallet Wallpaper Warnings Weather Web Browser Website Weebly Whatsapp Widgets Wifi Wi Fi Wi-Fi Wifi084d04ddac Wifi-calling Windows Windows10 Windows 10 Windows 11 Word Wordpress World-clock Yosemite YouTube Zoom

27 Sycamore St, Camberwell, Victoria Australia

​​Call 1300 885 420

​to book an appointment or class, or to enquire about our services and products


Seniors Card Welcome
SENIORS CARD WELCOME HERE:
Seniors Card holders qualify for a 10% discount on all classes booked and paid for online (excludes PTT sessions and classes already discounted during COVID-19 crisis). 
To activate the discount at the time of booking, select Redeem Coupon or Gift Certificate and enter 10OFFSEN before selecting Pay Now.

Services
Classes
Event Calendar
Private Appointments
Gift Vouchers
Remote support
​Business Services
​Help for families

Online Content
​Newsletter
User Guides
Video tutorials/classes
Tips and Hints
​Blog
iTandCoffee Club
About the Club
Free Club Sessions
Sign In/View Account
Members Area
Prices/Policies
Prices
Privacy
Cancellations, Refunds, Returns
About iTandCoffee
Home
About

​Contact us
Testimonials
In the press

Picture
​© 2012-2021 iTandCoffee Pty Ltd. All rights reserved  ACN: 606 340 434