Tuesday, April 30, 2019
Create a Dynamic Named Range
Do you have a table that has data added/removed on a recurring basis and it's an input to other tables? When you add or remove records, you may have needed to change the references from the other tables. You can offset these work by creating a dynamic named range or an Excel table. Since I used the word "offset" in an earlier sentence it's a clue to the type of function that will be introduced. One way to create a dynamic named ranges is to use the OFFSET and COUNTA functions. It's a bit more involved in trying to understand what the combination of these two functions do, but if you're counting on learning some cool tips, check it out.
Monday, April 29, 2019
Plot Two Sets of Data on an X Y Scatter Chart
This video show how to plot two sets of data into an X Y Scatter chart. It also shows how to add additional axis to the chart for a total of four axes.
Wednesday, April 24, 2019
Combine Tables from Multiple Sheets in the Same Workbook with Power Query
If you work with data in many worksheets, sometimes it's better to combine this all into one worksheet. Especially if they all have the same header fields. You can usually do a copy and paste, but if there are a lot of worksheet or if this is a recurring task, you might want to semi automate it. Power Query makes this easier to do. This video shows how to combine tables from different tabs in the same file using the Append capability in the Excel with Power Query.
Subscribe to:
Posts (Atom)