Excel VBA Introduction Part 58.7 - SQL for Excel Files - Basic Calculated Columns

This video explains the basics of adding calculations to an SQL query for Excel. You'll learn how to add columns to the Select list and assign aliases to them. You'll learn the basic arithmetic operators, what BODMAS and PEDMAS are and how to control the order of evaluation in a complex expression. You'll see how to add calculations to the Where and Order By clauses. You'll learn two different ways to divide numbers and how to use the Mod operator to return the remainder. You'll learn the basics of concatenating values and see what happens when your expressions generate an error. You'll also learn some basic ways to format the results of your expressions using functions such as Round and Format.

Chapters
00:00 Topic List
00:49 The Basic Setup
03:29 Adding Columns of Values to a Query
05:41 Assigning Names to New Columns
07:00 Table Aliases and Selecting Specific Columns
10:28 Tidying Up the Query Layout
14:18 Basic Arithmetic Operators
16:46 More Basic Calculations
17:32 Multiple Operations in One Calculation
18:57 Using Brackets to Control Calculation Order
20:12 Using Calculated Columns in Subsequent Calculations
22:09 Using Expressions in the Where Clause
23:43 Using Expressions in the Order By Clause
25:40 Criteria and Sorting without Displaying a Column
26:25 Different Ways to Divide Numbers
28:52 Concatenating Values
32:05 Dividing by Zero
35:49 Errors Produce Blank Cells
39:32 Rounding Numbers
42:10 Formatting Percentages
43:35 Formatting as Currency
45:42 Whats Next

[youssefsedkey]

There are many advantages to your lessons, Andy, let me list them:
1 - Each time you are at the beginning you give a full introduction to the content of the lesson.
2 - You walk step by step with the learner until he discovers mistakes himself.
3 - Rather, you sometimes deliberately make a mistake in order to teach us how to treat it.
4 - Each paragraph during the lesson has its own title.
5 - You teach us the equations and how to use them.
6 - You are presented in an orderly, systematic, and interesting way.
Finally, I built a decent software background with your lessons.
Thanks, Andy.

[excelexcellent]

Thank you Andy.... you have really got us all to Excel Black Belts with this series.

[frikduplessis]

Andrew this was a wonderful tutorial thank you, I can't wait for the follow-up I work with data and dates, you made so many questions clear, I can't wait to change a few things on my report tomorrow 👌😎 thanks to you I look like a excel ninja🥷 👏👏

[sebastian]

Another incredible and valuable video of the series. I can’t thank you enough for that. Having watched all the series lets you get ADO Master Black Belt 😆👏.

[KhalilYasser]

Thank you very much. I am following this series but in the latest videos, I forgot to comment because of the lengthy duration of the videos. Best Regards

[yasminalsham]

Thank you it's always very helpful.
I am just wondering if you could increase the number of records that you are running the SQL statement against, to more than 80k record?

[vanthinh]

Thanks for your sharing.
It's very useful for me.
Could you tell me how to format percent data and the result is not text?

[civilcourt]

You are doing great Job. Sir/M, Please, do series about Excel Animation with VBA Coding. I am ready to pay, if its payable.

[safeerahmed]

Hi Andy, can you teach how to add values using insert into as I am facing challenges to enter the Data

[tejamarneni]

Hi Andrew, thank you for the great tutorial. I have a quick question, is there a way to find the relation between two tables in SQL? There is no database diagram to view. How can I find the relation? Thank you.

[kashifkhanspecial]

Hi Andrew, Thank you so much again for very good tutorial about Excel/Sql.

I have two questions/requests regarding Excel/SQL.

1st question/request: can we use Windows Functions in Excel/Sql queries or similar like functionality in Excel/Sql? like we do in SQL Server Queries.

2nd question/request: can we add row number column in Excel/Sql queries, basically want to show another column in any Excel/Sql query that will show the row number? like we do in SQL Server Queries.

Many many thanks again for creating video tutorials on Excel/Sql topics.

God bless you, please stay safe and healthy.

Happy weekends :)

Thanks
Kashif

[karvannansa]

Sir, how to add new column with searial number sequence

[safeerahmed]

Please can you help m3 with this pls .... U am very. Stressed as I am not able to achieve

[vigneshe]

I have created a macro with help of your video which track items from outlook inbox and display it in excel with from, subject and time i want to add flag and markcomple also into
Can you help me with code to show flaged and marked complete items from outlook in msexcel using vba and if the item is flag it has to show in red and if it is marked complete it need to show in green