## Quora Questions

### 05 11 2019

I write answers on various Excel topics at http://www.quora.com and this page provides links to some of my answers. They are not normally massively complex questions but I answer the ones that I think might be interesting to my visitors and readers. The pages are probably in reverse order: lastest pages first; but that should not matter!

Update 5th December 2019

How do I convert F to C in Excel?

How can you take a profit and loss statement in CSV format and easily load it into PowerBi?

Why does Microsoft Office make it so difficult to guarantee your work is saved?

How do I extract consecutive capitalized words from a body of text in Microsoft Excel?

Why doesn’t Excel recognise comma separated values as separate and filterable?

How do you graph 10,000 data in an Excel chart?

What is paste special in Excel?

What is the x-intercept and y-intercept of two lines x-3y=7 and 2x-5y=4?

https://www.quora.com/How-do-I-make-a-line-graph-in-PowerPoint/answer/Duncan-Williamson-1

**How can one have two things on the same axis in Excel?**

This page is on my facebook page and not Quora! The page shows a Power BI image of the flight path of LY1747 of El Al … it is unusual, to say the least. https://www.facebook.com/william.williamson.144/videos/1600460606762146/

**What are some real life examples and applications of multiple regression?**

**Which duplicate does Excel remove?**

**How do I paste a chart from one Excel to a blank Excel?**

**What are the most unused excel formulas?**

**Journalizing records data chronologically in accounting. What is the explanation of it?**

## Welcome to this Blog: Excel with ExcelMaster

### 21 04 2017

**This Blog contains an ever growing set of detailed pages on How to … do things and pages of simple but very useful Tips.**

**22nd March 2020**: Along with many other Excel bloggers, I am spending a lot of time on making Excel the go to software to gather, store and communicate the **covid-19** data. I have done something that no one else is doing, again. I am demonstrating how to turn text into tables, pivot tables and pivot charts. You will hardly believe it’s possible but you can see what I did and then download my Excel file and do it yourself. This will take you time to learn and set up but it takes just a minute a day to update. Here is my page on Data From Text

**17th March 2020**: If you are Irish, have as happy a St Paddy’s Day as you can! Otherwise, two pages on the Append function in Power Query. Brilliant pages for everyone with page two being relatively advanced.

**16th March 2020: Three new pages for you: How MIRR really works, Z Charts and COVID-19 and Long mathematical equations in Excel**

**7th March:** This is a treat for anyone who is using Power Query and needs some relatively advanced insights into using them to create Pivot Tables and Pivot Charts, when using Connection Only Queries. There is neither video nor Excel file but the page is so good and has such good graphics that the keen reader will not mind that they are not here! Go to this page … Pivot Table for a Connection Only Query?

**6th March**: [Data.Format.Error]We couldn’t convert to number … If you ever receive this error message when creating a Query in Excel or Power BI, you might find there is not enough help on the internet to help you … until now! Read this page for the solution.

**5th March** 2020: this is a moderately interesting problem you might come across. See the page entitled Percentages Disappeared from Power Query to see what can happen when Excel chooses the wrong Data Type for you. It also shows you how to correct that mistake.

**3rd March 2020: **this is epic! Sorting a Pivot Table by TWO columns. It cannot be done … until you find Helgi and http://www.stackoverflow.com and she helps you to achieve the impossible. Read on!

**7th February 2020**: Here is a fascinating article: fascinating for two reasons. Reason one is that I have used some of the latest new Excel functions, RANDARRAY() AND XLOOKUP(). Reason two is that I caught someone out in a porky pie by means of programming a spradsheet. Go and have a look at Infinite Monkey or Porky Pie. You will also learn about the infinite monkey theorem while you are there!

**23rd January 2020**: I have previously written three pages on Sparklines and here is a fourth: it’s fascinating and so easy to do. However, if Sparklines are new to you, do note the links to the other three pages at the bottom of the page of this latest post. Have fun with Sparklines! Sparklines with the Camera Tool.

**16th January 2020**: I started this blog in 2011 and since then, this is where you all come from … a map showing where my blog is viewed from and I have highlighted the top ten countries. Looks like I cover the world!

**15th January 2020**: You know that I am a regular contributor to www.quora.com. A couple of weeks ago, I was invited to open and use what they call a **Space**: it’s essentially a blog inside Quora. You will see that I put things there that could easily go here. So, do yourself a favour by following this link and signing up with me there as well as here … meaning you will miss nothing!

**5th January 2020**: Happy new year to everyone, first of all! Here is something you might not have seen yet. If you take a look at the Review Ribbon in your version of Excel you might see something new: in the Proofing section on the left of the ribbon there is something called Workbook Statistics … click on it and see what it tells you about the file you are currently in. Is it useful? Will you use it? Who will use it? Also today, I have added a new page, on the very important topic of **Accessibility in Excel**: that page is here

**12th December 2019**: A video … a video and a file to download. How to use a checklist to solve the problem of dirty data. It’s a smaller problem than I have dealt with before, so we did not need to use Power Query. Still, solving these problems is a skills that we all need. The file is here where the video and Excel file are waiting for you!

**6th December 2019**: Today’s contribution to this blog is a page I have written on the XOR function. This is something of an odd function since I don’t think I have seen it in action. Never mind that, it is not difficult to use and understand and there are clearly many uses for it. As a bonus, I have included the ISODD() and ISEVEN() functions in this article and, hot on the heels of yeseterday’s post on IF, IFS and CHOOSE, this is a totally topical page! You can find the page here and there is an Excel file to download, too.

**5th December 2019: **If you have ever wondered about the use of Nested IF, IFS and CHOOSE, look no further because I just created a page for you. Two of the examples are easy and the third example is much more of a challenge. Go here to see the page https://excelmaster.co/nested-if-ifs-and-choose/

**5th November 2019** For the benefit of all Brits … have a happy and safe Bonfire Night. Secondly, look out for my NEW page, Quora Questions. Just scroll down from here and you will see the heading, just click it to open and read what I am doing. Simple concept with some very useful ideas.

**26th September 2019**: I have created this page to show how I went from an image on a web page, via OCR software, to a relatively sophiticated Excel file creation that uses Data Types … Stocks, Excel Tables, Pivot Tables, Conditional Formatting and more.

**13th September 2019**: a fantastic page on corporate bonds and their analysis. How to program a table using dates, the IF(), AND(), MONTH(), YEAR() functions and arithmetical calculations. If you find difficulty using dates, consider this page.

**11th September 2019**: a friendly introduction to the setting up and use of a Training/Testing Model in Excel. It is an introduction but it is a good one. The page does not, however, carry out any detailed analysis of the validity of the model you will read about. The data are real and you can replicate what I have done very easily. The page is here.

**22nd August 2019**: As you all know, this site is free of charge and always will be. Every now and again, I announce something like the publication of one of my books. Sometimes my books are not free of charge because they are professionally published. Whilst that is true of my latest book, it IS free of charge, at least for a while. The title of the book is EXCEL POWER QUERY, AN INTRODUCTION: BUSINESS INTELLIGENCE PART I.Click on the title to go to the site where you can download it free of charge.

**1st August 2019**: I have already created two pages (Flash Fill, Flash Fill and Quick Analysis) on Flash Fill, both for Excel and for Power Query and here is another one. In this case, I am introducing even more ideas on how Flash Fill (FF) can be used very effectively. Firstly, by using FF to extract, combine, extract and combine; and secondly, I watched a video of a highly respectable Excel practitioner who tried to sell us the idea that there are times when FF is just not good enough and neither is Power Query: in that case, he said, use a formula … he demonstrated a relatively complex formula. The formula worked but I am showing here that that presenter is wrong and that FF will do exactly what we want without programming anything.

**14th July 2019**: A different approach but the same quality. I have just posted my latest two videos to YouTube. Video 1 is about Flash Fill and Quick Analysis in Excel. Video 2 is about Flash Fill in Power Query and Quick Analysis in Excel. The aim is to illustrate how it is possible, even with dirty data, to create a basic dashboard within 10 minutes! Video 1 … Video 2

**19th June 2019**: **BOOKS** … Since I was last here, I have completed writing two books on Excel and I will announce publication details here when I have them. The first book is the fourth in my Excel Solutions for Accountants series and the second book is the first in the series on Power Query, Power Pivot, Power BI. These books are the only things I talk about here that are not free of charge.

**CASES** … in addition to books, I create case studies and for courses I am running at the moment, I am sharing four such cases with you, in a PDF file. There are two cases on financial analysis, one case on budgeting for buying and running a private jet and a case on exchange rates. Look at the page to see the outline of each case and what you need to know about Excel to work through them.

**5th May 2019**: If you are serious about learning VLOOKUP in the Power Query and Power Pivot environments, look no further. I have created two videos, one of PQ and one for PP to help you. Just go to this page and take a look at my detailed explanations.

**20th March 2019**: One file, **two videos**. This page outlines what I did after downloading some **World Health Organisation** data on DTP3 vaccinations (Diphtheria, Tetanus and Pertussis), covering the period 1980 to 2017. From a health point of view, what you see here is fantastic. From an Excel point of view, what you see is intermediate level but there are enough features illustrated to make it well worth your while to take a look at it! The page is here

**6th March 2019**: This page ends with an Excel file but it is really a discussion of how I created some Power BI files that I then used to create the Excel files by copying **M Language code** from one to another. What I describe here is magical and I have used the departures and arrivals information for both of Bangkok’s two international airports as the basis of my work. The page you want is here and there are two bare bones Excel files to download … Word Press will not allow me to share Power BI files, I am sorry to say.

**15th February 2019**: Let’s watch a video! I have created a video showing you how to use Data Types Stocks to pull in share prices, Beta values, market capitalisations of potentially thousands of different companies from around the world. There is a video to watch and a spreadsheet to download. The page is here: Data Types … Stocks

**7th February 2019:** I know you have read my pages on Sparklines and therefore you know how clever they can be and how you can format the cells they are in, you can add text/formulas to the cell they are in. Well, now, you are about to learn to use Conditional Formatting Icons in them. As far as I know, you can find this technique nowhere else on the web! Here is the link to the page Sparklines with ICONS

**9th January 2019**: This is a fabulous page … not because I am so brilliant but because I have just learned how to combine **array constants with a VLOOKUP function**. It has been around for years but it just filtered down to me. You can thank me later! Here is the page you need: https://excelmaster.co/vlookup-with-array-constants/

**8th January 2019: Happy New Year Everyone. **Secondly, the purpose of this update is to share with you my solution to creating a histogram for any one of àbout 90 possible line items in a ratio analysis worksheet. The data relate to Netflix and comprise a ten year view of the ratios that come from the morningstar.com web site. The file also includes my Positive Skewness Test that I have recently started to program and use. There is a file to download too and if you find it useful, please tell me how you are using it. the file is here: https://excelmaster.co/one-histogram-90-choices/

If you’d like my help and guidance on something, just ask and I’ll do what I can.

Duncan Williamson

Introduction for Absolute Beginners: Introduction to Excel for Absolute Beginners

Excel Files for Practice:

The basics of spreadsheeting 1

The basics of spreadsheeting 2

The basics of spreadsheeting 3

The basics of spreadsheeting 4