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?

Can I sort a column of strings in an Excel doc alphabetically by last rather than the first character?

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

Every CSV I’ve ever tried to save from Excel (2010) says “filename.csv may contain features that are not compatible with CSV (comma delimited). Do you want to keep the workbook in this format?” Why is this? How can I save it as CSV?

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?

In Excel Wizards, is there a function that enables me to choose specific texts in a cell based on other keywords (i.e., ‘age: “29”‘)?

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

What is an effective way to categorize and sort large lists of quotes or definitions for someone with limited Excel knowledge?

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?

What is NPV and its formula?

How can I make a Google Sheets function (in column K, row 5) that compares two rows (4 & 5) in the same column (column J) and then automatically skips 2 rows instead of one when dragged into row 6, so that now it’s comparing rows 6 & 7 in column J?

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?

How do you show a subtotal at the bottom of a pivot table without showing the title at the top in Excel?

Which duplicate does Excel remove?

How do you use an exponential equation you get from Excel? I know how to make Excel show me the equation, but how do I “copy” it or use it?

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

What are the most unused excel formulas?

What are some typical statistics and plotting made on datasets with 2 columns of numerical values and one column of categorical values?

In MS Excel, I have a large column of times (in seconds and minutes) when events occurred. Is there a way to automatically segment this data to make a frequency distribution table of say 0-5 minutes had 3 events, 5-10 minutes had 0 events, etc.?

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

What is exponential smoothing in Excel?

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

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, Conditinal 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 1Video 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

%d bloggers like this: