Одну секунду
Что-то сучилось. Попробуйте еще раз.
Solveandexcel смотреть последние обновления за сегодня на .
Suppose you need to create a system to search data in an Excel Table using different criteria for the various fields. In that case, Advanced Filter can be one good solution for you, especially if you do not have access to the Microsoft365 version of Excel with the FILTER function. This video will teach you how to automate the Advanced Filter tool to work immediately after changing a search criteria value. This short tutorial is part of a comprehensive class where you can learn more about using Advanced Filter and FILTER: 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
Power Query does not offer the possibility to refresh queries that load to tables in protected sheets to be refreshed while the sheet is protected. A simple macro will do the trick, but there is a very simple detail that we must not forget, or the macro will not work. - Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Facebook: 🤍 Instagram: 🤍solveandexcel LinkedIn: 🤍 LinkedIn: 🤍 Twitter: 🤍celia_excel Solve & Excel Consulting - 🤍 - #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
Power Query is one of the most spectacular tools within Excel, but like all the other tools and features, it is meant to be used in certain circumstances. This video lists a few situations where Power Query must be your first option to consider, especially if it is a task you will need to repeat. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer New videos on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the video EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA #Excel #shorts #solveandexcel #microsoft365 #microsoft #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #powerquerytraining #powerqueryeditor #worksmarternotharder #canada #msexceltraining #microsoftexceltips
Using the macro recorder can help you learn VBA while you automate small tasks in Excel. Always review the code produced by the macro recorder and do your best to learn how to clean it, make it more efficient, and easier to read and to maintain. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Facebook: 🤍 Instagram: 🤍solveandexcel LinkedIn: 🤍 LinkedIn: 🤍 Twitter: 🤍celia_excel Solve & Excel Consulting - 🤍 - #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
What if we combine multiple tables that only have some column headers in common into just one table? This video shows one way of doing that in Power Query in Excel. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA #microsoft365 #microsoft #toronto #torontobusiness #wit #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #powerquerytraining #powerqueryeditor #worksmarternotharder #canada #msexceltraining #microsoftexceltips
Printing and defining printing settings can be a tedious and time-consuming task. If you need to repeatedly print the same document, it might be worth setting up a macro that ensures all the printing details are as required. Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
In Today´s class, we are going to talk about the difference between storing data and presenting data results and how we can use Excel’s feature to build the report in the exact layout that we want. PART 1 of this class: 🤍 Get access to the exercise files by joining my Telegram Group at 🤍 Subscribe to my Newsletter for exciting news about the Excel world 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel
Sometimes you just want your Pivot Table to have a fixed number of rows or columns and stop “dancing” every time you change your filter selection. Watch how to achieve that. Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
In this video, I will demonstrate how we can group data in Power Query by one of the fields and get all the corresponding values from another field placed in just one cell as a list separated by a comma. This technique will involve editing a row of the M code in Power Query. Website: 🤍 Facebook page: 🤍 Linkedin profile: 🤍 Linkedin page: 🤍 Instagram: 🤍solveandexcel Playlist: Excel and Power Query: 🤍 Data Analysis: 🤍 Video intro by Daniela Alfarrobinha (🤍 Video editing by Celia Alves and Miriam Lozada (🤍
Reports printed from Excel require informative and professional-looking headers and footers. Learn how to take advantage of the Page Layout View to create clean and dynamic headers and footers without taking space out of your spreadsheet work area. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
When cleaning data in Power Query, it is quite common to merge text from cells in the same row. What about if we need to merge text from cells in the same column while maintaining the values in the other cells belonging to the same row? _ Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 _ Chapters: 00:00:00 - Scenario Merging cells in same column with PQ explained 00:02:50 - Add an Index column 00:04:25 – Transform the Index column using Integer-Divide 00:05:55 – Create a Reference Query 00:07:00 - Group By - All Rows 00:08:00 – Create List of Description column, Extract Values 00:09:43 – Merge Queries as New 00:13:10 – Summary of techniques used - Get access to the EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Enroll in my Excel Automation Training Program: 🤍 Boost your Excel skills with the best Excel books - Mr Excel Book store - 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel YouTube: https:www//youtube.com/celiaalvessolveexcel TikTok: 🤍solveandexcel #excel #msexcel #exceltraining #msexceltraining #powerquery #powerquerytraining #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
We can connect Excel to a Google Sheets file and import data from there with Power Query. Watch to learn the details you need to account for to make that connection happen. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
The client payment terms usually come as something like “Net 30” or “Due Upon Receipt.” See how we can teach Excel to determine the payment date based on the payment terms and the invoice issuing date. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Facebook: 🤍 Instagram: 🤍solveandexcel LinkedIn: 🤍 LinkedIn: 🤍 Twitter: 🤍celia_excel Solve & Excel Consulting - 🤍 - #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
Power Query does not offer a connector to import data from an Excel sheet, but we can work around that with this technique. Note: on a more recent version of Excel, in the Data tab we see a button “From Sheet” under the “Get & Transform Data” group. This button does not allow us to connect to a sheet to inspect and import all the data in it. Instead, this button has the same function as the former button “From Table/Range” and will allow us to import data from a range in a sheet (pre-selected range, table range, named range or the spill range resulting from a dynamic array function. Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA #Excel #shorts #solveandexcel #microsoft365 #microsoft #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #powerquerytraining #powerqueryeditor #worksmarternotharder #canada #msexceltraining #microsoftexceltips
When we type something in a cell adjacent to a table on the right side, the table will auto-expand to absorb that new column. The same does not happen when we type in the cells adjacent to the table’s left side. But there is an easy and quick fix for that. Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer YouTube - 🤍 Facebook: 🤍 Instagram: 🤍solveandexcel LinkedIn: 🤍 LinkedIn: 🤍 Twitter: 🤍celia_excel Solve & Excel Consulting - 🤍 - #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
When you need to find the entries in your data that match certain criteria, how do you do it? In this class we will CREATE AN EXCEL TOOL that allows the user to easily search the data and find the records that meet certain criteria. We will build this searching tool in two ways, using each one of the options Advanced Filter and FILTER. Last class we covered the basics about the Advanced Filter tool and the FILTER function in Excel. For PART 1 class check this video: 🤍 - Get access to the exercise files by joining our Telegram Group at 🤍 Subscribe to my Newsletter for exciting news about the Excel world: 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel
If you are expanding your formulas down a column manually all the time and not taking advantage of Excel tables to organize your data, you are missing all the fun in Excel and preventing many exciting doors from opening to you. Get access to the exercise files by joining our Telegram Group at: 🤍 Subscribe our Newsletter for exciting news about the Excel world: 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Instagram: 🤍solveandexcel Twitter: 🤍celia_excel
Data can arrive at us in many different layouts, but we know that the most convenient one is the tabular layout because that's the one that makes our lives easier in Excel when we need to do data analysis. Learn how to recognize that your data is pivoted and how to unpivot it using Power Query. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
Excel Tables are wonderful by themselves already, but you can make them even more awesome! Use slicers and have fun with your data! Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
UNPIVOT is one of the most potent tools in Power Query. Pivoted data comes in all types of shapes, and some are challenging. Watch one not-too-complex example in this video to understand some of the workaround techniques you can use to solve each case. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
When you need to find the entries in your data that match certain criteria, how do you do it? Table filters are one option, but they can be cumbersome to manage. There are other tricks that we can use. I will share two options in this class. NOTE: Between min 47:00 and 59:00 you will see me struggling with Advanced Filter not producing the expected results. It was due to, by mistake, having entered criteria "Mary" under the field client, instead of the field "Sales Rep." More details about Advanced Filter, including automation, will come in the next class. Get access to the exercise files by joining our Telegram Group at 🤍 Subscribe to my Newsletter for exciting news about the Excel world: 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel
In Today´s class we are going to talk about: how we can build a query that brings always the first and the last three columns from a table that keeps having new columns every new month. We will see two examples: one where the last columns to consider are dates, and another example where the last columns have random names. In Today´s class we are going to talk about: how we can build a query that brings always the first and the last three columns from a table that keeps having new columns every new month. We will see two examples: one where the last columns to consider are dates, and another example where the last columns have random names. Get access to the exercise files by joining my Telegram Group at 🤍 Subscribe to my Newsletter for exciting news about the Excel world 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel
Learn how to get data from the most recent file in a Folder with Power Query in Excel. The next time you need to update your report, you just need to place your new data file in that folder and refresh your queries. Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - My Contact information: Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn:🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel Learn Excel and connect with a community of Excel professionals and enthusiasts: MS Excel Toronto Meetup Group - 🤍 Learn how to automate your Excel Reports: 🤍 #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #mvpbuz #communityrocks #wit
Different reasons might make you want to change the language in Excel. You may need Excel to be in a familiar language for you to allow you to do your work. For a training or demonstration session, you may need to change the Excel language to fit another person's preferences. Also, sometimes changing the Excel Language to English may enable some features that can be available for the English version only at that moment. In this video, I demonstrate how to change Excel's language from English to Portuguese in Excel for Microsoft 365. The steps are similar if you are looking to switch between any other two languages. Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - Content: 0:13 - Introduction 0:49 - Getting More Display and Help Languages from Office.com 1:16 - Finding, selecting, downloading, and installing another language 2:07- Changing the display language in Excel 3:23 - Changing decimal and thousand separators Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel Learn Excel and connect with a community of Excel professionals and enthusiasts: MS Excel Toronto Meetup Group - 🤍 #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #mvpbuz #communityrocks #wit
When the information to print does not fit in one sheet only, it may be helpful to repeat rows at the top of each sheet printed with the column headers and repeat columns on the left. Watch how to set those as print titles. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
When all you need is a pivot table report from data from external files your sources, you can do it without the need of bringing the data physically into the Excel file that contains the pivot table. Learn how. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer FREE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
Numbers are numbers! And if you need to make calculations with them in Excel, that’s how they should remain: as numbers. However, you can change the way you display your numbers. For example, the number 5 could be displayed as “5.00 hrs” to indicate a total of 5 hours worked. Learn how to do this in Excel in a way that does not impact your ability to use that number in calculations. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
Having a set of data with dates, how can we filter it to get only the dates from the first day of the current day? We will answer this question using Power Query in Excel for two different scenarios: when the data does not go past today or the current month and when the data includes dates in the future. - Get access to the EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel YouTube: https:www//youtube.com/celiaalvessolveexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
Data Validation’s behavior is not consistent with case sensitivity, and this video shows why. Data Validation is an Excel tool that we can use to alert the user when the value entered on a cell does not match the requirements. When that cell requires a text string, it may be relevant if the user uses uppercase or lower case when inputting the data, so being aware of how this tool operates is essential. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Facebook: 🤍 Instagram: 🤍solveandexcel LinkedIn: 🤍 LinkedIn: 🤍 Twitter: 🤍celia_excel Solve & Excel Consulting - 🤍 - #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
When reading data from text files, sometimes the conversion to columns is not consistent. Source files produced the same way, expected to present the same number of columns, may be interpreted by Power Query differently and show a variable number of columns from file to file. This video shows a tweak that can help deal with that situation and make Power Query deal with any number of columns it finds in the data source. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer New videos on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the video EXERCISE FILES by joining our Telegram Group at 🤍 (For this video, search for "T0017" on my Telegram channel. - same files as for this video: 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA #Excel #shorts #solveandexcel #microsoft365 #microsoft #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #powerquerytraining #powerqueryeditor #worksmarternotharder #canada #msexceltraining #microsoftexceltips
When using Power Query to import data from an Excel sheet we may get extra rows and columns that may seem empty. The video explains why that happens and how to solve that. Previous video where I talked about cleaning the cell range not used in a sheet before importing its data with Power Query: 🤍 Bil Jelen’s video: 🤍 Power Query feature related to determining the dimension of the used range in a sheet: 🤍 Two classes about importing data from Excel sheets with Power Query – sheets can be in the same workbook as the query or in other Excel files: 🤍 🤍 - Get access to the EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Boost your Excel skills with the best Excel books - Mr Excel Book store - 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel YouTube: https:www//youtube.com/celiaalvessolveexcel #excel #msexcel #exceltraining #msexceltraining #powerquery #powerquerytraining #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
Today I have an awesome tip for you about how we can very easily copy queries from one workbook to another one. You don't have to recreate the steps manually! Before start copying queries from one workbook to another, I will show you the different situations in terms of how many data sources each one of the queries has, and how the different queries relate to each other or depend on each other. You can also read the explanation here: 🤍 Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn:🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel Learn Excel and connect with a community of Excel professionals and enthusiasts: MS Excel Toronto Meetup Group - 🤍 Learn how to automate your Excel Reports: 🤍 #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #mvpbuz #communityrocks #wit
We will look at an example of connecting to a web page and go through several data cleansing challenges. Importing data from a web page may come with a lot of challenges. In this class, we will deal with non-printable characters and dates prior to January 1st, 1900 which are not recognized by Excel but can easily be dealt with within Power Query. Get access to the EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel YouTube: https:www//youtube.com/celiaalvessolveexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
Sometimes users apply random formatting here and there, in an Excel Table, until it becomes a complete mess! You can clear all that out with just a few clicks. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer YouTube - 🤍 Facebook: 🤍 Instagram: 🤍solveandexcel LinkedIn: 🤍 LinkedIn: 🤍 Twitter: 🤍celia_excel Solve & Excel Consulting - 🤍 - #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
The best layout to present your data for quick reading may not be the best option for data analysis. This video shows how we can use Power Query in Excel to transform a multi-row header layout into a nice tabular data layout. Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer LIVE CLASSES on YouTube every week: https:www//youtube.com/celiaalvessolveexcel Get access to the Live Classes EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel #excel #msexcel #powerquery #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA - #Excel #shorts #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
This video is about the "Enable Background Refresh" property in Power Query in Excel. I will go through a couple of examples to demonstrate what this property does to query refreshing behaviour and to explain why it is important to pay attention to how we choose to set this property in our Power Query projects. Links Mentioned: Uservoice idea - make "Enable Background Refresh" unchecked by default - 🤍 Date Picker add-in by Sam Rdakovitz - 🤍 Video Tutorial by MrExcel: 🤍 (Subscribe to MrExcel Youtube Channel!) Fake Daya add-in by Sam Rdakovitz - 🤍 Video Tutorial by MrExcel: 🤍 (Subscribe to MrExcel Youtube Channel!) 0:00 - Introduction 0:38 - What is and where to find "Enable Background Refresh Property"? 4:10 - Power Query Tables and Pivot Tables 10:14 - Power Query Tables and VBA 18:14 - Excel UserVoice idea to vote 18:45 - Excel Date Picker and Excel Fake Data add-ins My Contact information: Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel Learn Excel and connect with a community of Excel professionals and enthusiasts: MS Excel Toronto Meetup Group - 🤍 Learn how to automate your Excel Reports: 🤍
SESSION OUTLINE: Dynamic array functions are a game-changer in Excel. Many problems can be solved in a much easier, simpler, and faster way if we can use dynamic array functions than without them. The Microsoft Excel team released several dynamic array functions in the past couple of years. Working with them requires a change in our thought process in Excel. However, once you start getting the hang of it, it is tough to go back to working without dynamic array functions and dynamic array capabilities in Excel. In this session, we will look at some business cases where dynamic array functions shine in making our life so much easier. A zip folder with Celia's files to go along with the presentation can be downloaded here: 🤍 SPEAKER PROFILE: Celia Alves is an Excel Business Solutions Developer certified by Microsoft as Excel Expert. She loves to design and build solutions combining Excel, VBA, Power Query and Power Pivot to automate tasks, analyze data and model business processes. With an academic background in Mathematics and Education and 20 years of experience as a high school Mathematics teacher, Celia combined her passion for teaching with her experience as Excel Consultant to offer free tutorials on YouTube and a Training Program to help professionals automate their Excel recurring tasks. She firmly believes that automating routine Excel reporting procedures saves money and resources for companies, improves their workflows and promotes a happier work environment by offering their staff the opportunity to contribute more valuable and meaningful work to their organizations. Celia is also a speaker and the founder and leader of the MS Excel Toronto Meetup Group. For her contribution to the community, she was awarded MVP by Microsoft in 2020 and every year after that. Connect with and follow Celia online at: Her website: 🤍 LinkedIn: 🤍 YouTube: 🤍 Facebook: 🤍 Instagram: 🤍
Session recorded live on Jan 6, 2021 To attend the MS Excel Toronto meetup group sessions live, please register for free as a member of the group: 🤍 The MS Excel Toronto Meetup group is organized by Celia Alves - 🤍 Vivek Patel - 🤍 Dick Moffat - 🤍 CHAPTERS: (Thank you to Gráinne Duggan for her help with this list.) 00:00:00 - Celia Alves MS Excel Toronto news and upcoming events 00:08:40 - Celia Introduces herself 00:14:36 - What is Power Query, how it helps with Data Acquisition, and when to use it 00:34:05 - Example 1 - Import data from CSV file 01:06:30 - Example 2 - Import data from Excel files in a folder 01:38:35 - Final notes and learning opportunities 01:45:00 - Discussion 02:00:00 - Wrap up - About this session: Any professional dealing with Excel regularly can significantly reduce the time spent on data importation and reshaping repetitive tasks by only applying Power Query fundamental techniques. It is not difficult to learn! Power Query is a tool available in Excel dedicated to facilitating extracting data from different sources, cleaning and reshaping the data to the format needed and importing it into Excel to be ready for consumption. Although Power Query has been available for a few years, many Excel users are still unaware of its existence, or they are unsure how they can take advantage of this tool. In this session, Celia will present some business case scenarios and offer some tips to those who are willing to get Power Query in their tool belt once and for all! Nothing like the start of a new year to challenge ourselves to learn something new that can be the door for a drastic evolution of our Excel skills throughout the year. - About the Speaker: Celia Alves Celia is a certified MOS Expert in Excel and develops complex solutions combining Excel, VBA, Power Query and Power Pivot to automate tasks, analyze data and model business situations. She got her Licentiate and Master’s degree, both in Mathematics and Education from the University of Lisbon, in Portugal, and worked as a high school Mathematics teacher for two decades up to 2013 when she moved to Canada. After re-skilling herself, Celia built a new career as an Excel consultant and trainer, ultimately founding her business Solve & Excel Consulting, serving companies in a wide variety of industries. Celia is also a speaker and the founder and leader of the MS Excel Toronto Meetup Group. In 2020, she received the Microsoft MVP award in the Office Apps and Services category for her contribution to the community. Find more about Celia's work and connect with her: LinkedIn: 🤍 YouTube: 🤍 Blog: 🤍 Facebook: 🤍 Instagram: 🤍 Twitter: 🤍
What if you need to combine the same type of information that you receive from multiple sources, but each source sends you the Excel report with different column names? Here is one possible solution with Power Query in Excel that renames the columns in all the files in a folder to get them ready to be combined in one data table only. Our previous video: 🤍 M Code Functions : Function Table.RenameColumns - 🤍 Function List.Zip - 🤍 - Learn how to automate your Excel Reports in minutes instead of hours without copy-paste or coding: 🤍 - Get access to the EXERCISE FILES by joining our Telegram Group at 🤍 Subscribe to my NEWSLETTER for exciting news about the Excel world: 🤍 Learn how to automate your Excel Reports: 🤍 Enroll on my Excel Automation Training Program: 🤍 Boost your Excel skills with the best Excel books - Mr Excel Book store - 🤍 Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Solve & Excel Consulting - 🤍 LinkedIn: 🤍 Facebook: 🤍 Twitter: 🤍celia_excel Instagram: 🤍solveandexcel YouTube: https:www//youtube.com/celiaalvessolveexcel #excel #msexcel #exceltraining #msexceltraining #powerquery #powerquerytraining #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA
When you have an Excel file to use as a template but then you keep messing up with the master copy or disputing it with your colleagues because you need to use the master copy at the same time, then it is time to consider saving your template master copy as .xltx instead of .xlsx. - Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer Facebook: 🤍 Instagram: 🤍solveandexcel LinkedIn: 🤍 LinkedIn: 🤍 Twitter: 🤍celia_excel Solve & Excel Consulting - 🤍 - #Excel #solveandexcel #microsoft365 #toronto #torontobusiness #wit #msexcel #microsoftexcel #excelreport #excelautomation #snapreports #powerquery #canada
.svg){kind=small}
![Excel Table – One of the most important concepts in Excel [L0004]](https://i.ytimg.com/vi/1uCPPqbmQe8/mqdefault.jpg "Excel Table – One of the most important concepts in Excel [L0004]")
