BizTrix

?? Free Excel Course ?? Have you ever struggled to organize and brand your Excel files? If yes, then you’ll want to take this course. BizTrix just launched a free Excel course: Excel Course 4, Formatting. The course includes over 1 hour of instruction and covers a wide range of formatting features that include concepts such as ?? Format codes and colors ?? Presentation ready tables ?? Format tools ?? Custom color palettes ?? Worksheet settings, and ?? Customizing built-in features I’m a strong advocate for style guides, and this course is designed to help you create the foundation for your Excel style guide. You don’t have to take my word for it though, try it out for free! The teaser video and other free content can be found in the link that follows. Simply Enroll on the site: https://lnkd.in/eQ6ArJXA #Excel Comments of how formatting has improved trust and yielded recognition: "It’s such a pleasant experience working with your files." "Not only do I always know which file is yours, but I’m also relieved because I know it will be a smooth review." "I was never taught anything like this in school. This was so helpful in providing me with direction on how to get organized."

I want to show you how to test the join examples from previous posts, but I need to cover some basic SQL features first. You may not know it, but you don’t need a table to run a select statement in SQL ??Here are some basic examples: ?? select 1; ?? select 'BizTrix'; Note: Ensure you use a straight single quote (') in SQL, not an opening (‘) or closing (’) single quote. These two examples will return the value as a single record with a column name of “(No column name)”, “?column?”, or some other variation of that. If you want to give the column a name (or an “alias”), then simply provide the name after the value: ?? select 1 id; ?? select 'BizTrix' company; These two examples will return the value as a single record with a column name of “id” and “company,” respectively. These examples don’t use the ‘as’ keyword to define the alias, but you can put the ‘as’ keyword in if you like: ?? select 1 as id; ?? select 'BizTrix' as company; Let's say you want to select more than one record. How can you do this? You need to use the ‘union’ keyword. ‘union’ appends data on top of one another. select 1 id union select 2; This example will return two records with a column name of “id.” Notice that you didn’t need to place the alias name (i.e. “id”) after 2. (If you put “id” after 2, i.e. select 2 id, then it won’t make a difference in the results. In fact, try the following to see what happens: select 2 my_id). If you don’t use the ‘all’ keyword with union (i.e. union all), then it will remove duplicates. ?? union = remove duplicates ?? union all = do not remove duplicates union (returns 1 record): select 1 id union select 1; union all (returns 2 records): select 1 id union all select 1; Do you want to return multiple columns and records? Simply place a comma (,) between the columns. select 1 id, ‘BizTrix’ company union all select 2, ‘Microsoft’; You’ll use these two techniques (select and union) in the future to test the join examples. Let me know in the comments how you use this. BizTrix trains on business skills: https://biztrix.us/

It happened! Excel enabled high-contrast only mode. (It just showed up on my version of Excel). I previously wrote about the lack of consistency for the font color picker across Excel, PowerPoint, and Word (link below). A few months later, PowerPoint upgraded to match Word (link below). Yep, a few months later and Excel also upgraded to match Word ??. ?? PowerPoint still needs “Automatic” ?? Excel and Word still need “Eyedropper” ?? Excel and PowerPoint still need “Gradient” (Maybe this is a Word specific thing? I’ve never used Gradient) My fingers are crossed that these other features are implemented so I don’t have to modify my keystroke sequence (annoying) across platforms??. BizTrix trains on business skills: https://biztrix.us/ Inconsistency: https://lnkd.in/eq48NRY9 PowerPoint Update: https://lnkd.in/eHPZ2Rjc

Have you ever performed a dual lookup in Excel to find the missing values from two lists? (You likely used VLOOKUP, XLOOKUP, or MATCH/XMATCH to find the #N/A errors from each lookup). There’s a way to do the same thing in SQL using a full outer join. A full outer join will keep all records from both tables; matches return the values and non-matches return NULL. (If it helps, it’s like a left join but both tables are being treated as the left table. The link below reviews left join). How many results do you get when you join TblA (3 entries) to TblB (5 entries) on Part? 7 results. ?? Banjo is the only match (2 results) ?? TblA: no match on bass clef and NULL (2 results) ?? TblB: no match on drums, piano, and NULL (3 results) Remember the Excel lookup from above to get the missing values (i.e. #N/A errors)? What can you add to the query to return the NULL values only? ?? ?Add a where-clause to filter for TblA.Part is NULL or TblB.Part is NULL. (Take the time to double check your logic when working with NULL). ?? This returns 5 results Let me know in the comments how full outer join helped you solve your use case. BizTrix trains on business skills: https://biztrix.us/ Previous Link: https://lnkd.in/ej9zzesW

Now that you are an inner join expert, let’s consider a left join. A left join keeps all the entries from the left table and returns only those entries from the right table that have a match. If there is no match from the left table, then a NULL value appears in the result set for the right table. Recycling the example from last week (link below), how many results do you get when you replace the “join” keyword with the “left join” keywords? In this example, “from TblA join TblB” will become “from TblA left join TblB”. TblA is the table on the “left” and TblB is the table on the “right”. Did you get 7 results? ?? The left table, or TblA, has 5 entries. At a minimum, you will get 5 results because left join will keep all the entries from the left table. (Yes, you keep the NULL entry). ?? TblA.Part matches TblB.Part for banjo only. The existing 2 banjo entries from TblA are already part of the “left” table, and the 2 banjo matches in TblB add the additional entries. Now consider what happens if you swap TblA and TblB. “from TblA left join TblB” now becomes “from TblB left join TblA”. How many results do you get? ?? Did you get 7 results? Yes. ?? Are they the same results as “from TblA left join TblB”? No. Let me know in the comments what questions you still have after learning about left join. BizTrix trains on business skills: https://biztrix.us/ Previous Post: https://lnkd.in/ep9Dgpu7

There are two join types in SQL: inner and left. (Is that right? ??) Okay. There are more join types, but if your experience is like mine, then inner join and left join will dominate your queries. It may not be fully obvious, but the previous posts exposed you to inner join. Inner join returns only those results that match between the tables. Using the equal (=) comparison operator (and musical instruments to help add some visual variety), you saw how to join to one column only and multiple columns in the previous posts. The image below provides a recap: ?? TblA-to-TblB on Part (4 results) ?? TblA-to-TblB on Num (3 results) ?? TblA-to-TblB on Part & Num (1 result) Recall that “join” will return all the matches and drop any NULL values (because NULL is unknown). In the case of an inner join: ?? Using ‘inner join’ or ‘join’ will perform the same operation ?? The order of the tables does not matter (e.g. “from TblA…” vs. “from TblB…”) You have everything you need to utilize an inner join to your advantage ?? Let me know in the comments what your favorite part of using inner join is. ? BizTrix trains on business skills: https://biztrix.us/

It's common to see a NULL value in a database. A NULL value indicates that the data point is unknown. It’s important to understand how NULL values are handled in a join. (Again, this is also important for Excel’s Power Query tool). Here are the important facts about NULL: ?? NULL is not an empty string ?? NULL is not zero ?? NULL is unknown Because of these facts: ?? No two NULL values will never be equal ?? Comparing a NULL to a non-NULL will return unknown You may be thinking, “Why would I want a NULL value if it is unknown?” If you know someone who has a middle name and someone who does not, then you can understand why NULL is useful. ?? First name = John; Middle name = Devin; Last name = Sailor ?? First name = Kerry; Middle name = NULL; Last name = Richardson In the image below (which follows my previous posts), you can see how NULL impacts the results for the join on Part, Num, and Part & Num. ?? Part: NULL is dropped ?? Num: NULL is not compared to anything (because the join is on Num) ?? Part & Num: NULL is dropped Let me know in the comments if you've ever been burned by NULL when joining tables together. I have! It's how I came to better learn NULL's impact on data ??. BizTrix trains on business skills: https://biztrix.us/ There is an error in the image. The join should be "on a.Part = b.Part" not "on a.Num = b.Num".

Can you correctly solve this SQL join question? If you use (or plan to use) Excel’s Power Query tool, then you need to know this. The previous post (link below) illustrated the basic concept of a join in SQL. Because this is such a fundamental fact in SQL, it’s important to expand upon it. I continue with the equal (=) comparison operator and the use of musical instruments to keep consistency with previous posts (and to add visual variety). There are three images in the post: ?? Image 1: TblA has 2 banjos and TblB has 1 banjo (the setup) ?? Image 2: TblA has 2 banjos and TblB has 2 banjos (the question) ?? Image 3: The answer to Image 2 Image 1 shows that both of TblA’s banjos match TblB’s banjo. This returns 3 results. It’s important to remember that join returns any result that has a match. Image 2 asks you to solve the join, and image 3 has the answer. Did you get 5 entries as the result? You may be tempted to link each banjo in a one-to-one pairing (i.e. 3 entries in the result); however, the join returns any result that has a match. Thus, TblA’s banjo[123] matches each banjo in TblB (i.e. banjo[456] and banjo[713]), and TblA’s banjo[942] matches each banjo in TblB (i.e. banjo[456] and banjo[713]). You’ll quickly notice that this relationship between TblA and TblB duplicates TblA’s data, which is a critical concept to understand when working with join. You’ll continue to build on these fundamental facts in the coming weeks. There’s more to learn in order to complete the foundation for join ?? BizTrix trains on business skills: https://biztrix.us/ Previous Post: https://lnkd.in/eMdvcbv4

It’s been a while since I last posted about SQL. Previous SQL posts took you through the basic keywords for selecting, filtering, and grouping data. The next logical step is to “join” tables together. We’ll keep this high level and zoom in over the coming weeks. The dictionary defines join as “connect,” as in a place where two or more things are connected. In the case of SQL, a “join” will connect two or more tables “on” a specified column. In the image below, I can join TblA to TblB on Part, Num, or a combination of the two. Let’s look at three examples in the image below where the join is performed using the equal (=) comparison operator. (I’m using musical instruments to help add some visual variety): ?? TblA-to-TblB on Part (join the two tables on Part); returns 3 results ?? TblA-to-TblB on Num (join the two tables on Num, the SQL sample is included on the slide); returns 2 results ?? TblA-to-TblB on Part and Num (join the two tables on Part and Num); returns 0 results The important part is this: Join returns any result that has a match! You’ll see in the coming weeks why this is a very important fundamental fact in SQL. BizTrix trains on business skills: https://biztrix.us/

No one mentioned last week (link below) that the Navigation task pane is an option for jumping to the cell location of a named range. Give it a try by going to the View tab, Show group, Navigation command. In addition to the navigation tools for named ranges, here are some other tools you may find helpful for named ranges. (Personally, I use all these tools. They are listed in no particular order): ?? F3 (paste name) ?? F3 (paste list) ?? IntelliSense feature for table specific names (i.e. structured references) ?? IntelliSense for names ?? Ctrl+Shift+F3 to add “bulk” names (I think I’ve used this maybe once during my Excel tenure, though I do use Alt+F3 instead to quickly add names) One thing I don’t like is how table names aren’t included in the Paste Name dialog box. Did Microsoft miss this or is something else at play here? (I don’t know). Let me know in the comments if there are others tools/ways you interact with names. BizTrix trains on business skills. Previous Post: https://lnkd.in/et58ZVDs