Relax with Excel

Excel Training 101

Welcome to "Relax with Excel", the unique podcast where learning meets tranquility. Are you intrigued by the power of Excel but find the thought of spreadsheets a bit daunting? Or perhaps you're looking to soothe your mind while subtly enhancing your skills? Dive into the world of Excel with a twist – where education sails on a wave of relaxation.  Join me, your host with over a decade of experience teaching Excel, as I guide you through the gentle undulations of Excel's landscape. Each week, in my soothing monologue, I'll unravel the mysteries of Excel in a way that's almost humorously easy to digest, perfect for beginners and those seeking a calm learning experience.  Imagine learning about formulas, pivot tables, and data visualization while you're winding down or drifting off to sleep. "Relax with Excel" offers a unique blend of soft, informal discussions mixed with practical Excel tips. It's a place where the hum of numbers and the rhythm of functions become a lullaby for your brain.  No complex jargon, no high-pressure learning environment. Just you, me, and the peaceful exploration of Excel's capabilities, all at a pace that lets you breathe easy. Whether you're looking to learn in the background or seeking a serene companion to your night, "Relax with Excel" is your go-to podcast.  Tune in weekly and let's unravel the world of Excel together, one calming episode at a time. It's time to relax with Excel – where learning not only becomes easy but also incredibly peaceful. 

Episodes

  1. 29/02/2024

    S1E9-Dropdown List

    Hi, welcome to the ninth episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of how to create a dropdown list.  Having a dropdown list in Excel is highly beneficial for several reasons. Firstly, it streamlines data entry, making it faster and more efficient. Instead of typing out data each time, users can simply select an option from the list, which is especially useful for repetitive or standard entries. Dropdown lists are also user-friendly, guiding users to select from predefined options and thus avoiding invalid entries. For large datasets or databases, this feature is invaluable as it ensures uniformity and accuracy across entries.   Creating a dropdown list in Excel with dummy data involves a few detailed steps. Let's say we want to create a dropdown list of five fruit names: Apple, Banana, Cherry, Date, and Elderberry.  First, input your dummy data (the fruit names) in a separate column in your Excel sheet. For instance, you could enter 'Apple' in cell A1, 'Banana' in A2, 'Cherry' in A3, 'Date' in A4, and 'Elderberry' in A5. This column will serve as the source for your dropdown list.  Next, select the cell where you want the dropdown list to appear. This could be any cell outside of your list; let's say it's cell B1.  Click on the 'Data' tab on the top   cell. In the data tools section, find and click on 'Data Validation.'  In the Data Validation dialog box that appears, select 'List' from the 'Allow' dropdown menu in the 'Settings' tab.  Now, you need to specify the source for your dropdown list. Click in the 'Source' box, and then select the range of cells containing your dummy data (A1:A5 in our example). Alternatively, you can manually type the range as '$A$1:$A$5'.  After selecting the range, click 'OK.' The ce  chose (B1) will now have a dropdown arrow in it. Clicking this arrow will show a list of your dummy data (the fruit names), from which you can make a selection.  If you click on the dropdown arrow in cell B1, you should see 'Apple,' 'Banana,' 'Cherry,' 'Date,' and 'Elderberry' as selectable options.  This procedure creates a dropdown list in Excel using your specified dummy data, making it easy to select from predefined options and ensuring consistency and accuracy in data entry.  That was a basic explanation of how to create a dropdown list in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    3 min

  2. 22/02/2024

    S1E8-CONCAT

    Hi, welcome to the 8th episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the CONCAT function.    The CONCAT function is a way to join two or more text strings into one. Here is a step by step approach on how to use the CONCAT function:  Open a blank Excel file and enter some sample text in different cells. For example, you can enter the following text in cells A1:A3:       Hello     World     !      Select a cell where you want to enter the CONCAT formula. For example, you can select cell B1.  Type =CONCAT( in the cell. You will see a tooltip that shows the syntax and arguments of the CONCAT function. The syntax is CONCAT(text1, [text2], ...). The arguments are:  text1: The first text string to join. For example, you can enter A1 as the first text string to join the text in cell A1.  [text2]: (Optional) The second text string to join. You can enter up to 253 text strings to join. For example, you can enter A2 as the second text string to join the text in cell A2.  ...: (Optional) You can enter more text strings to join, separated by commas. For example, you can enter A3 as the third text string to join the text in cell A3.  Enter the arguments for the CONCAT function in the cell, separated by commas. For example, you can enter =CONCAT(A1,A2,A3) in cell B1. This formula will join the text in cells A1, A2, and A3 into one text string.  Press Enter to complete the formula. You will see the result in the cell. For example, you will see HelloWorld! in cell B1, which is the concatenated text string. You may notice all the words joined together without a space.  You can eithe ace after certain words like a space after Hello or add a space character in the CONCAT function.  Let’s modify the arguments to join different text strings. For example, you can add a space character between the text strings by using " " as an argument. For example, you can enter =CONCAT(A1," ",A2," ",A3) in cell B2. This formula will join the text in cells A1, A2, and A3 with a space between them. You will see Hello World ! in cell B2, which is the concatenated text string with spaces.    That was a basic explanation of  concatenation in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    4 min

  3. 15/02/2024

    S1E7-Text to Columns

    Hi, welcome to the 7th episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the Text To Columns  feature.  The “Text to Column” feature is a way to split the text in a cell into multiple columns based on a delimiter or a fixed width. Here is a step by step approach on how to use the “Text to Column” feature :  Open a blank Excel file and enter some sample data in a cell. For example, you can enter the following data starting with cell A1.  The other records will be entered in the row after.  |Alice,90|  |Bob,80|  |Charlie,70|  Select the column that contains the text you want to split. For example, you can select column A.  Go to the Data tab   ck on the Text to Column button in the Data Tools group. You will see the Convert Text to Columns Wizard dialog box. Alternatively you can use the keyboard shortcuts.  Please Alt, then A and then E.  In the first step of the wizard, choose the type of data you have: Delimited or Fixed width. Delimited means the text is separated by a character such as a comma, a semicolon, a space, or a tab. Fixed width means the text is separated by a fixed number of spaces. For example, you can choose Delimited as the type of data for the sample data in cell A1.  Click Next to go to the second step of the wizard. In this step, you can specify the delimiter that separates the text. You can choose from the predefined options such as Tab, Semicolon, Comma, Space, or Other. You can also enter a custom delimiter in the Other box. In this example, you can choose Comma  for the sample data in cell A1. You will see a preview of the data in the D  iew box.  Click Next to go to the third and final step of the wizard. In this step, you can specify the format and destination of the data. You can choose from the options such as General, Text, or Date for the format of each column. You can also click on the Advanced button to set the decimal separator and thousands separator for the numeric data. For example, you can choose Text as the format for the first column and General as the format for the second column for the sample data in cell A1. Yo can allso choose the destination cell wh   the data. By default, the data will overwrite the original cell. You can change the destination by clicking on the Destination box and selecting a different cell or range. In this example, we can leave the default  setting.  Click Finish to complete the wizard and split the text into columns. You will see the result in the worksheet. For example, you will see the following data in cells A1:B3:  | Alice   | 90    |  | Bob     | 80    |  | Charlie | 70    |  That was a basic explanation of the  Text to Columns in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    4 min

  4. 08/02/2024

    S1E6-Flash Fill

    Hi, welcome to the sixth episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the Flash Fill feature.  The flash fill feature is a way to automatically fill in data based on a pattern or example. Here is a step-by-step approach on how to use the flash fill feature in a paragraph format:  Open a blank Excel file and enter some sample data in a column. For example, you can enter the following data in column A:    - Name    - Alice Smith    - Bob Jones    - Charlie Brown    - David Lee    - Eve Green    Let’s have Column B show the names in last name comma first name format.  In cell B1 type last, first.  Select a cell b2  and type the data you want to fill in based on the pattern or example. For example, you can type Smith   n cell B2 to fill in the last name and first name separated by a comma.  Press Enter to complete the data entry. You will see the data in the cell. For example, you will see Smith, Alice in cell B2.  Select the cell again and click on the Fill button in the Editing group on the Home tab. You will see a drop-down menu with different options to fill the data.  Click on the Flash Fill option in the menu. You will see the data automatically filled in the adjacent column based on the pattern or example. For example, you will see the following data in column B:    | Last , First |  |------------|  | Smith ,  Alice      |  | Jones, Bob        |  | Brown, Charlie    |  | Lee, David      |  | Green, Eve        |    You can copy and paste the data to other cells or modify the pattern or example to fill in different data. For example, you can type A. Smith in cell C2 and use the flash fill feature to fill in the first initial and last name separated by a dot.  Lets try with a keyboard shortcut instead of using the Ribbon command.  After you type A. Smith and press return, use the keyboard shortcust Ctrl + E and it will perform the flash fill. You will see the following data in column C:  - Name    - A. Smith    - B. Jones    - C. Brown    - D. Lee    - E. Green    That was a basic explanation of Flash fill  and how to use it in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    4 min

  5. 01/02/2024

    S1E5-Scatter Chart

    Hi, welcome to the 5th episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of creating an X Y Scatter Chart in Excel  An X Y scatter chart, also known as a scatter plot, is a graphical representation used to display the relationship between two quantitative variables. Each point on the chart represents an observation in your dataset, with the position of the point determined by the values of these two variables. The horizontal axis (X-axis) typically represents the independent variable, while the vertical axis (Y-axis) represents the dependent variable. These axes are scaled to encompass the range of data values for each variable.  Each dot or point on the scatter chart represents a single observation from your dataset. The position of a point is determined by the values of the two variables. For instance, a point at coordinates (5,10) indicates that for this observation, the independent variable has a value of 5 and the dependent variable has a value of 10. Here is a step-by-step approach on how to create an X Y scatter chart in a paragraph format:  Open a blank Ex file and enter some sample data in two columns. For example, you can enter the following data in cells A1:B6:  | X | Y  |  |---|----|  | 1 | 2  |  | 2 | 4  |  | 3 | 6  |  | 4 | 8  |  | 5 | 10 |  Select the data you want to plot on the chart. For example, you can select cells A1:B6.  Go to the Insert tab and click on the Scatter button in the Charts group. You will see a drop-down menu with different types of scatter charts.  Click on the Scatter with only Markers option in the menu. You will see a scatter chart inserted on the worksheet with the data points plotted as dots.  You can customize the chart by using the Chart Elements, Chart Styles, and Chart Filters buttons on the upper right corner of the chart. You can also use the Chart Design and Format tabs on the ribbon to modify the chart elements, layout, style, and format. For example, you can add a chart title, axis titles, gridlines, trendline, legend, data labels, and more to the chart  That was a basic explanation of creating an X Y Scatter Chart in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    4 min

  6. 24/01/2024

    S1E4-XLOOKUP

    Hi, welcome to the fourth episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of XLOOKUP.  The XLOOKUP function is a powerful and versatile tool that allows you to look up values in a table or range by matching a lookup value. You can use the XLOOKUP function to perform exact, approximate, wildcard, or reverse lookups. Here is a step by step approach on how to use the XLOOKUP function in a paragraph format:  Open a blank Excel file and enter some sample data in a table or range. For example, you can enter the following data in cells A1:B5:  | Name    | Score |  |---------|-------|  | Alice   | 90    |  | Bob     | 80    |  | Charlie | 70    |  | David   | 60    |  | Eve     | 50    |  Select a cell where you want to enter the XLOOKUP formula. For example, you can select cell D2.  Type =XLOOKUP( in the cell. You will see a tooltip that shows the syntax and arguments of the XLOOKUP function. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The arguments are:  lookup_value: The value you want to look up. For example, you can enter "Bob" as the lookup value to find his score.  lookup_array: The range or array that contains the lookup values. For example, you can enter A2:A6 as the lookup array to search for names.  return_array: The range or array that contains the values to return. For example, you can enter B2:B6 as the return array to return the scores.  [if_not_found]: (Optional) The value to return if the lookup value is not found. For example, you can enter "Not found" as the if_not_found argument to display a message if the name is not in the table.  [match_mode]: (Optional) A number that determines the type of match to perform. The default value is 0, which means an exact match. You can also use -1 for an exact match or the next smaller item, 1 for an exact match or the next larger item, or 2 for a wildcard match.  [search_mode]: (Optional) A number that determines the direction of the search. The default value is 1, which means a search from first to last. You can also use -1 for a search from last to first, 2 for a binary search from first to last (requires sorted data), or -2 for a binary search from last to first (requires sorted data).  So that completes the descriptions of the arguments.  Enents for the XLOOKUP function in the cell, separated by commas. You can enter =XLOOKUP("Bob",A2:A6,B2:B6,"Not found") in cell D2. This formula will look up the name “Bob” in the range A2:A6 and return the corresponding score from the range B2:B6. If the name is not found, it will return the message “Not found”.  Press Enter to complete the formula. You will see the result in the cell. For example, you will see 80 in cell D2, which is the score of Bob.  You can copy and paste the formula to other cells or modify the arguments to perform different lookups. For example, you can change the lookup value to “Eve” or “Frank” to see their scores or the message “Not found”. You can also change the match mode to 2 and use a wildcard character like * or ? to perform a partial match. For example, you can enter =XLOOKUP("D*",A2:A6,B2:B6,"Not found",2) in cell D3 to look up any name that starts with D and return the score. You will see 60 in cell D3, which is the score of David.  That was a basic explanation of  XLOOKUP and how to use it in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    6 min

  7. 14/01/2024

    S1E3-VLOOKUP

    Hi, welcome to the 3rd episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of VLOOKUP.  VLOOKUP is one of the more well-known lookup functions in Excel.  Like most lookup functions, it’s used to find things in a table or range of data.  For VLOOKUP it is looking in a row and would return a result from that row depending on the column you choose.  For example, if you have a two-column table with column 1 as the Part Number and column 2 as the price you might want to use the VLOOKUP function to execute a lookup that returns the price when you type in the part number in another cell.  Let's pretend we do have this situation and column A has over 100 unique part numbers going down the rows and column B has the prices for each  d might want to use column D as the area to do your lookup.  Cell D1 would have the Text “Part to Look up” and Cell D2 would have the title “Price”.  In cell D1 this is where you’d input any part number to be looked up.   In cell D2, is where you’d write out the VLOOKUP function.  At its most basic, it would take three arguments or inputs: the value to lookup, the range where it would find the data to be looked up and the column number of that range where it would rind the value to return. There is a fourth argument, but if that’s omitted, it would default to TRUE. If you had part number 123 in row 500 and wanted to just type 123 in cell D1 of the lookup cell to get back the price, in cell D2 you would write the VLOOKUP function beginning with an equal sign then VLOOKUP, open parentheses, D1 for the cell that you had inputted 123 (or any other part number in the future).  That was the first argument.  The 2nd argument is the range of data where the lookup table is.  You can select from A1 to B101 because that would have the range of data where part number is the first column and price is the second column.  OR if you could just type A1:B101.  The third argument would be what column to bring back the value from.  Since price is the second column, you’d type the number 2 here.  You can just type a closing parenthesis here to complete the function. VLOOKUP would effectively look in the first column for the value of the aupart number 123 and go row by row to find it.  Once it finds it in row 500, it would move to the 2nd column bring back the price.  And that is how a basic VLOOKUP function works.  When you have a large table with many columns and want to use a lookup function to quickly find column 5, 8 or column whatever value based on a lookup value, VLOOKUP is often one of the better tools to do this.  That was a basic explanation of a VLOOKUP and how to use it in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    4 min

  8. 13/01/2024

    S1E2-Table List

    Hi, welcome to the second episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of an Excel Table.  This was previously known as an Excel list.  When you’re putting text and numbers into some structured format where the first row is header row that has field names to help describe what the columns are; and the rows afterwards are where actual information is typed in; you might think this is a table.  For example, your first row may have three columns that have column 1 as first name, column 2 as last name and column three as sex. The second row would then have Jane in the first column, Lee in the second column and female in the third column. Rows 3 to say 20 after that would have first names, last names and male or female in columns 1, 2 and 3.  You might call this a table of information now. Though in a sense it’s correct, in the Excel world this would be a range of data and not necessarily a Table. Or at least not an Excel table. A range of data is just numbers and text that occupy cells in the worksheet, but once this range of data is turned into an Excel table, that is where the magic happens.  To turn that range of data of our three-col range.  Excel is usually pretty good at recognizing the boundaries and select all the cells that would be included in the table.  Next you could use the keyboard shortcut Ctrl + T or if you like to use the Ribbon, go to Insert and then click the Table icon.  Notice that if you hover over the Table icon it would give you a brief description of the Table icon command as well is show you can use the keyboard shortcut to create a table.  A small window labeled Create Table will appear and confirm if your range of data is what you want and have a checkbox for you to choose whether the first row is a header row or not.  Once you make your selections and click OK, the range of data will most likely change colors.  Usually, the default color is bluish.  The header row takes on a darker blue color with the other rows become an alternating or banded color or light blue and then white.  Also you should now notice that the top row header fields have a drop down icon on the right of the cell.  This means that filtering is enable for each column so you can filter or sort the data.  This makes it so much easier to analyze your data if it’s a large data set, either by sort item in ascending or descending order.  Another neat thing about the Excel table is that you can summarize values in each of the columns.  This means you can have a bottom row that will add up all the values in the rows or it can even give you an average of that column.  Just right click anywhere in the table and in the mini menu, select Table and then Totals Row.  The last row with have drop down icons on the right that will give you the option to sum, average, count or even put in some other function like countif.  The Excel table will expand or contract in size automatically depending on your addition or removal of data for your rows and that’s a benefit when you use the table as a source for other things like a Pivot Table or a chart.   These are a few of the key benefits of using an Excel Table, and once you create tables out of your range of data, you’ll find Excel Tables one of the best features of Excel. That was a basic explanation of an Excel Table and how to create one in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    4 min

  9. 12/01/2024

    S1E1-Pivot Table

    Hi, welcome to the first episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of a Pivot Table.  First off, what is a Pivot Table.  Basically, it’s a way to quickly summarize and analyze data.  It can turn an ordinary table of data into a dynamic table that you can quickly change the column and rows fields or pivot them around to analyze your data.  For example, if you have a table that’s a mailing list, you may have 5 columns: Name, Street Address, City, State and Zip code.  The rows would be the individual address records for someone like Oprah Winfrey or Tom Hanks. An example of the type of question a Pivot Table can help you answer would be: How many people live in New York or how many addresses have the word Terrace in them.  Let's pretend you have a table that has over a thousand names and addresses and put them into Excel.  It’s in an Excel worksheet.  Again, it has five columns.  The first row would be the column fields: Name, Street Address, City, State and then Zip.  Any rows after would be the mailing list records.  For example, in row 2, column 1 would be Oprah Winfrey.  Column 2 would be 123 Main Street.  Column 3 would be New York City.  Column 4 would be New York and Column 5 would be 11354.  Of course, Oprah doesn’t live there, but you get the idea.  Now pretend you have rows 3 –1000 with names and addresses of other celebrities.  This would mean you are quite the person, maybe a talent agent.  Click a cell with a value in it and Excel is usually good at figuring out how large of a range to include when creating a Pivot Table. Next in the Ribbon, click on the Insert Tab, then click the PivotTable icon.  A smaller window will show up call PivotTable from table and range. In the field that has the label Table/Range it should show the range of cells that include all the columns and rows you need to use. If it doesn’t, you may need to select the range at this point.  You can then choose where to place the Pivot Table, either in a New Worksheet or an Existing worksheet.    This is probably the most basic items you’d want to choose from in this window to set up your Pivot Table.  If all is good, click the OK button.  At this point you‘ll be back in a worksheet with a pane usually on the left where you can select the PivotTable Fields.  There’s an area where you can choose your fields.  In this case, it’ll be like Name or City.   Then there’s an area where you can put those fields in.  It will either be in a Filters, Column, Row or Values area.  Let say for this example we want to know how many celebrities live in each state. You would click on the Name field and drag it into the Value areas.  Excel will count the number of names, but that would be a total count, so if you already had 1000 records of names in the range, it’s not that useful.  But if you wanted to know the breakdown of names in each state, then select the State field and drag it in the Rows area. This will give you a top to bottom row view of the states in one column and the next column will give you the count per state.    Now you know how many celebrities live in Alaska or Florida.  If you wanted to see this from left to right, then you can move the State field to the Columns area.  This view means you’d scroll from left to right, whereas before you’d scroll up and down.  This action is the pivot part of a Pivot Table, because you just pivoted or moved around the way you see the data.  And that’s the magic of Pivot Tables.  That was a basic explanation of a Pivot Table and how to create one in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.

    5 min
  • 9 Episodes

About

Welcome to "Relax with Excel", the unique podcast where learning meets tranquility. Are you intrigued by the power of Excel but find the thought of spreadsheets a bit daunting? Or perhaps you're looking to soothe your mind while subtly enhancing your skills? Dive into the world of Excel with a twist – where education sails on a wave of relaxation.  Join me, your host with over a decade of experience teaching Excel, as I guide you through the gentle undulations of Excel's landscape. Each week, in my soothing monologue, I'll unravel the mysteries of Excel in a way that's almost humorously easy to digest, perfect for beginners and those seeking a calm learning experience.  Imagine learning about formulas, pivot tables, and data visualization while you're winding down or drifting off to sleep. "Relax with Excel" offers a unique blend of soft, informal discussions mixed with practical Excel tips. It's a place where the hum of numbers and the rhythm of functions become a lullaby for your brain.  No complex jargon, no high-pressure learning environment. Just you, me, and the peaceful exploration of Excel's capabilities, all at a pace that lets you breathe easy. Whether you're looking to learn in the background or seeking a serene companion to your night, "Relax with Excel" is your go-to podcast.  Tune in weekly and let's unravel the world of Excel together, one calming episode at a time. It's time to relax with Excel – where learning not only becomes easy but also incredibly peaceful. 

Information

  • Creator
    Excel Training 101
  • Years Active
    2K
  • Episodes
    9
  • Rating
    Clean
  • Copyright
    © Excel Training 101
  • Show Website
    Relax with Excel