Home
Videos uploaded by user “Excel Campus - Jon”
Introduction to Pivot Tables, Charts, and Dashboards (Part 2)
 
14:48
http://www.excelcampus.com/charts/pivot-tables-dashboards-part-2 This is part 2 in the series on an introduction to pivot tables and dashboards. In this video I explain some of the different pivot table calculation types. Learn how to calculate the average, count, sum, and percentage of total. I explain how to update your pivot table with more data, and also how to refresh your pivot tables. The video contains a lot of Excel tips, like how to quickly make a duplicate copy of a sheet, how to select the visible cells only, and a lot of useful keyboard shortcuts. I also demonstrate how to filter your source data and build a pivot table from your source data sheet using the PivotPal add-in. Lots of fun stuff to learn!!! Download the files to follow along at the following link. http://www.excelcampus.com/charts/pivot-tables-dashboards-part-2 WATCH PART 1: https://www.youtube.com/watch?v=9NUjHBNWe9M WATCH PART 3: https://www.youtube.com/watch?v=FyggutiBKvU Get PivotPal: http://www.excelcampus.com/pivotpal Subscribe to my free newsletter: http://www.excelcampus.com/newsletter
Views: 1724221 Excel Campus - Jon
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
 
14:48
WATCH PART 2: https://www.youtube.com/watch?v=g530cnFfk8Y Download file used in the video: http://www.excelcampus.com/pivot-table-checklist-yt In this video series you will learn how to create an interactive dashboard using Pivot Tables and Pivot Charts. Works with Excel 2003, 2007, 2010, 2013 for Windows & Excel 2011 for Mac Don't worry if you have never created a Pivot Table before, I cover the basics of formatting your source data and creating your first Pivot Table as well. You will also get to see an add-in I developed named PivotPal that makes it easier to work with some aspects of Pivot Tables. Download the files to follow along at the following link. http://www.excelcampus.com/pivot-table-checklist-yt I have another video that shows how to reformat the pivot chart in Excel 2010. In the video above I'm using Excel 2013 and the menus are different from Excel 2007/2010. Here is the link to that video. http://www.youtube.com/watch?v=Jt_QqG-vRRw Get PivotPal: http://www.excelcampus.com/pivotpal Free webinar on The 5 Secrets to Understanding Pivot Tables: https://www.excelcampus.com/pivot-webinar-yt Subscribe to my free newsletter: http://www.excelcampus.com/newsletter
Views: 6634667 Excel Campus - Jon
How to Create a Dashboard Using Pivot Tables and Charts in Excel (Part 3)
 
15:19
http://www.excelcampus.com/charts/pivot-tables-dashboards-part-3 This is part 3 in the series on Pivot Tables and Dashboards. In this video we bring it all together to create the interactive dashboard. You will learn how to: - Group dates into months and years to create a time series trend chart. - Group amounts to create a distribution (histogram) chart. - Add slicers to your pivot tables and charts to make them interactive. - Lots of tips and shortcuts for formatting your charts. You can download the file to follow along at the following link. http://www.excelcampus.com/charts/pivot-tables-dashboards-part-3 Please subscribe to my free email newsletter to get your tutorials like this and get updated about PivotPal. Get PivotPal: http://www.excelcampus.com/pivotpal WATCH PART 1: https://www.youtube.com/watch?v=9NUjHBNWe9M WATCH PART 2: https://www.youtube.com/watch?v=g530cnFfk8Y Subscribe to my free newsletter: http://www.excelcampus.com/newsletter
Views: 1449202 Excel Campus - Jon
Progress Circle Chart in Excel - Part 1 of 2
 
07:11
The progress circle (doughnut) chart displays the percentage of completion on a single metric. This is a simple chart that can be used in dashboards to show the progress towards a goal or target. It's basically a progress bar chart in the shape of a circle or doughnut. DOWNLOAD the file: https://www.excelcampus.com/charts/progress-doughnut-chart/ In this first video, we look at how to create the progress doughnut chart in Excel. It is a simple chart that just requires 1 doughnut chart and a formula. In part 2 of the video series, I explain how to apply conditional formatting to the chart so the color of the bar changes as the progress level changes. Watch Part 2 - https://youtu.be/dTkV4KmIb1Y FREE video series on Pivot Table & Dashboards: https://youtu.be/9NUjHBNWe9M
Views: 106858 Excel Campus - Jon
How to Install Power Query in Excel 2010 or 2013 for Windows
 
04:04
Learn how to install Power Query with this step-by-step tutorial for Excel 2010 or 2013. If you have Excel 2016 then Power Query is already built-in and located on the Data tab of the ribbon. The first step is to determine what bit version of Excel you have, 32-bit or 64-bit. This can be found on the File menu under Help (2010) or Account (2013). Once you know the bit version you can install Power Query from the following link. http://www.microsoft.com/en-us/download/details.aspx?id=39379 Next, completely close Excel. You might need to close other Office apps like Outlook or Power Point. Run the Power Query installer. Once it finishes, open up Excel. You should see the Power Query tab in the Ribbon. Here is a link to an article with step-by-step instructions. http://www.excelcampus.com/install-power-query/ Checkout my article on an overview of Power Query to learn more. https://www.excelcampus.com/powerquery/power-query-overview/
Views: 91368 Excel Campus - Jon
How to Write VBA Macros to Copy and Paste Cells in Excel - Part 1 of 3
 
04:56
Free Video Training Series on Macros & VBA: http://www.excelcampus.com/free-vba-training Download the example workbook at: http://www.excelcampus.com/vba/copy-p... Learn how to copy and paste cells and ranges in Excel with VBA. This video tutorial is the first in a 3 part video series that explains the Range.Copy method. This is any easy way write a macro for a simple copy and paste in Excel. Watch Part 2: https://youtu.be/AzxTCCuo8OM Watch Part 3: https://youtu.be/fQX4N4ePhaU
Views: 206520 Excel Campus - Jon
How to Create a Personal Macro Workbook in Excel and Why You Need It (Part 1 of 4)
 
06:58
In this video series you will learn how to create a personal macro workbook. I also explain why you should use it. Read the article and download the Excel file here: http://www.excelcampus.com/vba/person... The personal macro workbook is an Excel file that stores all of your personal macros. The file opens in the background every time you open Excel, which means your macros are easily accessible to run any time. You will learn where the personal macro workbook file is stored on the computer and how to view it. This is great if you need to backup the file or copy it to a new computer. I also explain how to add macro Buttons to the Excel Ribbon and Quick Access Toolbar. This makes it easy to run your macros on any open workbook. Finally you will learn how to make a Yes/No message box (pop-up window) to appear before your macro code runs. This is critical if you have macro buttons in the ribbon, as an accidental button press could run a macro that could ruin your workbook. I also have a free video training series on macros and VBA that will explain everything you need to know to get started. This covers writing your first macro, an explanation of the Excel object model and VBA programming language, how to automate simple Excel tasks, and how to create professional looking userforms. Other videos in this series: Where Is The Personal Macro Workbook Stored and How to View It (Part 2 of 4): https://www.youtube.com/watch?v=6cynP3OkIT0 How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar (Part 3 of 4): https://www.youtube.com/watch?v=dmdolFcS-fI How to Add a Yes No Pop-up Message Box to a Macro Before it Runs (Part 4 of 4): https://www.youtube.com/watch?v=rCh7ki9yVsM&t Please leave a comment below with any questions, and don't forget to hit the LIKE button if you enjoyed this video. Thank you!!!
Views: 74699 Excel Campus - Jon
How to Add New Items to Excel Drop-down Lists Automatically
 
05:20
In this video I explain how to include new items in Excel drop-down lists automatically. These are referred to as Dynamic Data Validation Lists because they automatically extend to include new rows/cells added to the source data range. Download the Excel file: https://www.excelcampus.com/tips/dynamic-data-validation/ In this example we will use Excel Tables and named ranges to create the dynamic lists. The named range references a single Table column using structured references (Table Name[Column Name]). The named range includes all cells in the column and never needs to be updated or maintained. This means we don't have to remember to update the source data range every time we make changes to it. It saves us a lot of time and helps prevent errors. Checkout by video on an Introduction to Drop-down Lists to learn more about creating data validation lists and all the rules that go along with them. https://youtu.be/KGnvCKiOLM0 Compatibility: This solution uses Excel Tables that were introduced in Excel 2007 for Windows. It will work in all versions of Excel including Excel 2007, 2010, 2016, 2019, Office 365 for Windows and Excel 2011, 2016, and beyond for Mac. The Free List Search Add-in: Unfortunately we can't directly search the drop down lists within Excel. So I created a free add-in called List Search that allows you to search any drop down list and input the value in the cell. It's packed with additional time saving features. Click the link below to learn more and download List Search. Download The List Search Add-in: https://www.excelcampus.com/vba/search-data-validation-drop-down-lists/ Other Videos and Resources: - How to create drop-down lists: https://youtu.be/KGnvCKiOLM0 - Beginners Guide to Excel Tables: https://youtu.be/fJnJTff4Na8 - How to search drop-down lists: https://youtu.be/kntHn2J7pdk - Update to List Search Add-in: https://youtu.be/igPO79uYoGg - Dependent Cascading Drop Down Lists: https://www.excelcampus.com/tables/dependent-drop-lists/
Views: 21983 Excel Campus - Jon
Where Is The Personal Macro Workbook Stored and How to View It (Part 2 of 4)
 
05:11
Learn where the personal macro workbook file is stored on your computer, and how to view it. It is important to know the location of the personal macro workbook for backing up the file, and sharing or transferring it to a new computer. Read the article and download the Excel file here: http://www.excelcampus.com/vba/personal-macro-workbook-video-series/ The personal macro workbook file opens in the background every time you open Excel. You can see it in the Project Window of the Visual Basic (VB) Editor. The file is stored in the XLSTART folder on the computer. Any Excel files in this folder will open when you open Excel. You can locate the file path for the personal macro workbook by typing the following line of code in the Immediate Window in the VB Editor. ?ThisWorkbook.Path Hit enter and it will return the path of the XLSTART folder where the personal macro workbook file is stored. Other videos in this series: How to Create a Personal Macro Workbook in Excel and Why You Need It (Part 1 of 4): https://www.youtube.com/watch?v=rD2-mSbTuL0 How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar (Part 3 of 4): https://www.youtube.com/watch?v=dmdolFcS-fI How to Add a Yes No Pop-up Message Box to a Macro Before it Runs (Part 4 of 4): https://www.youtube.com/watch?v=rCh7ki9yVsM&t Please leave a comment below with any questions, and don't forget to hit the LIKE button if you enjoyed this video. Thank you!!!
Views: 51184 Excel Campus - Jon
Create Multiple Pivot Table Reports with Show Report Filter Pages
 
06:08
Learn how to quickly create multiple pivot table reports with the Show Report Filter Pages feature. Download the file to follow along: https://www.excelcampus.com/pivot-tables/show-report-filter-pages/ Get the Tab Hound Add-in: https://www.excelcampus.com/tab-hound Pivot tables are an amazing tool for quickly summarizing data in Excel. They save us a TON of time with our everyday work. There is one "hidden" feature of pivot tables that can save us even more time. Sometimes we need to replicate a pivot table for each unique item in a field. This could be a report for: Each department in organization. Each salesperson on the sales team. Each account in the general ledger. Each customer in the CRM system. Each stock in the database. Or, just about any other field (column) in your data set. We could create one pivot table, filter it for a specific item, then copy the sheet and re-apply a filter for the next item. But this would take a lot of time if we have dozens or hundreds of unique items in the data set. Fortunately, we don't have to do all this manual work. Pivot tables have a feature called Show Report Filter Pages that automates this entire process. The Show Report Filter Pages Feature The Show Report Filter Pages feature: Creates a copy of an existing pivot table for each unique item in a field. The new pivot tables are created on individual worksheets. Each sheet is renamed to match the item name. A filter is applied to the field in the Filters Area of each pivot table for the item. All this is done with a click of a button. Your field can have 5 or 500 unique items. Show Report Filter Pages will create a sheet for each item and replicate the pivot table report.
Views: 81036 Excel Campus - Jon
How to Unpivot Data with Power Query in Excel
 
06:10
Learn how to use Power Query to quickly unpivot or normalize your data to use as the source for a pivot table. Power Query makes the unpivot process really fast and easy. It also allows you to create an automated system for updating your pivot tables, reports, charts, and dashboards. Full Article on this video - http://www.excelcampus.com/tables/unpivot-power-query/ How to Structure Data for Pivot Tables - http://www.excelcampus.com/modeling/structure-pivot-table-source-data/ Free Email Newsletter + Free Gift - http://www.excelcampus.com/newsletter
Views: 25061 Excel Campus - Jon
How to use the COUNTIF Function Instead of VLOOKUP in Excel
 
03:05
Learn how to use the COUNTIF function instead of VLOOKUP to determine if a value exists in a list or range. This video is from the Ultimate Lookup Formulas Course. The course contains over 100 short videos just like this one. Click the following link to learn more about the course. http://www.excelcampus.com/lookups COUNTIF is a really easy function that will allow you to quickly determine how many cells contain a value/criteria in a range. Here are 3 reasons to use COUNTIF instead of VLOOKUP (when you just want to see if a value exists in a range of cells): 1. The COUNTIF function only has two arguments making it really fast and easy to write the formula. VLOOKUP has four arguments. 2. COUNTIF returns the total number of matching values in the range, so you can see if there is more than one matching value. VLOOKUP cannot do this, it only returns the first match. 3. If the value does not exist, COUNTIF will return a zero (0). You do not need to worry about a formula error. With VLOOKUP, the formula would return an error and you would use the IFERROR function to handle the error. Click here to read the entire blog post. http://www.excelcampus.com/functions/how-to-use-countif-instead-of-vlookup
Views: 30956 Excel Campus - Jon
How to Add a Search Box to a Slicer in Excel
 
06:38
Learn how to add a search box to a slicer to quickly filter pivot tables, pivot charts, or Excel Tables. Click here to download the files: http://www.excelcampus.com/pivot-tables/add-search-box-to-slicer/ Here are basic instructions from the video. The blog post page contains more screenshots and keyboard shortcuts for the filter search boxes. http://www.excelcampus.com/pivot-tables/add-search-box-to-slicer/ 1. Insert a slicer on the worksheet. 2. Make a copy of the pivot table and paste it next to the existing pivot table. 3. The new pivot table will also be connected to the slicer. The slicer is connected to both pivot tables. 4. Remove all fields from the areas of the new pivot table. 5. Add the slicer field to the Filters area of the new pivot table. 6. Move the slicer on top of the cell that contains the filter drop-down button in the Filters area of the new pivot table. 7. Adjust the column width so the filter button is just to the right of the slicer. 8. Turn off the Autofit column widths option on the new pivot table. (Right-click pivot table, PivotTable Options…) 9. Hide the column that contains the field name in the Filters area of the new pivot table. Please subscribe to my free email newsletter to get more Excel tips like this. http://www.excelcampus.com/newsletter
Views: 137995 Excel Campus - Jon
Progress Circle Chart with Conditional Formatting - Part 2 of 2
 
09:08
The progress circle (doughnut) chart displays the percentage of completion on a single metric. In this second video in the series, we learn how to apply conditional formatting to the chart so the progress bar color changes as the percentage of completion value changes. DOWNLOAD the file: https://www.excelcampus.com/charts/progress-doughnut-chart/ This will make the chart stand out even more on our dashboards. The reader will be able to quickly see the level of progress and determine if the metric needs attention based on the color of the bar. The conditional formatting is created using a few IF statement formulas in the source data range for the chart. The IF formulas create the different progress levels. When the actual progress level is within the level range, the cell displays a value and its bar is displayed in the doughnut chart. We use 3 levels in this example, but you can add as many progress levels as you'd like. In part 1 of the series, we learned how to create the basic Progress Doughnut Chart. Both of these solutions just require 1 Excel doughnut chart and a few formulas. They are pretty easy to set up and implement in your reports and dashboards. Watch Part 1 - https://youtu.be/KbAmWC2AuSk Article on the IF Function in Excel: https://www.excelcampus.com/functions/how-to-write-if-function/ FREE video series on Pivot Table & Dashboards: https://youtu.be/9NUjHBNWe9M
Views: 61372 Excel Campus - Jon
How to List Unique Values with Advanced Filter in Excel
 
05:10
In this video I explain how to create a list of unique values with the Advanced Filter feature in Excel. This is a follow-up to a previous video where I shared 2 other ways to list unique values using the Remove Duplicates feature and a VBA macro. Here is the link to the previous video. https://youtu.be/soVOGldB-Mo The Advanced Filter feature of Excel has an option called Unique Records Only that creates the list. We can take a column of cells that contains duplicate values, and create a list of the unique values only in another column on the sheet. The disadvantages of the Advanced Filter technique are the output must be on the same sheet as the data range, and the formatting is not pasted to the output. Otherwise, it's another great option for creating a list of unique values and removing duplicates. Here is the link to the page where you can download the Excel file to follow along. https://www.excelcampus.com/vba/remove-duplicates-list-unique-values/ Click here to checkout the Filters 101 Course and learn more about the Advanced Filter feature. https://www.excelcampus.com/filters Click here to join the Excel Campus newsletter and receive a free gift. https://www.excelcampus.com/free Please leave a comment below with any questions or suggestions for other ways to create a list of unique values.
Views: 22673 Excel Campus - Jon
Why is the Pivot Table Field List Missing & How to Get It Back
 
04:18
Did your pivot table field list disappear? In this video I explain how to get the field list back if it is not showing. The quickest way to get it back is to Right-click a cell inside the pivot table and select "Show Field List" from the menu. The field list is contextual and will only appear when a cell is selected inside the pivot table. It can also be turned on or off in the Analyze/Options tab of the Ribbon by clicking the Field List button. Here are the instructions: 1. Select any cell inside the pivot table. 2. Go to the Analyze/Options tab on the right side of the ribbon. If you are using Excel 2010/2007 it will say options. The tab has been changed to Analyze in new versions. 3. Click the Field List button in the show section to enable the field list. This is a toggle button that can be turned on/off. The close button in the top-right corner of the field list window also turns the toggle off. The PivotPal Add-in allows you to work with the field list when you are looking at the source data of the pivot table. This is something that cannot be done with Excel's field list. Click here to learn more about PivotPal. https://www.excelcampus.com/pivotpal Subscribe to my free email newsletter: http://www.excelcampus.com/newsletter
Views: 68837 Excel Campus - Jon
Filter for Duplicates with Conditional Formatting
 
04:00
Learn how to filter for duplicates with conditional formatting in Excel. This video is a free sample lesson from my Filters 101 Course. https://www.excelcampus.com/filters101 In this video we learn how to quickly filter a column for duplicate values. We first use conditional formatting to highlight the duplicate values in the column. Then we can filter the column by font or fill color that was applied by the conditional format. Finally, we can sort the column in ascending order to group the duplicate values in adjacent rows. This makes it easy to see the duplicates all in one place, and figure out what actions we need to take to remove the duplicates or analyze them further. Read the article and download the sample file at: https://www.excelcampus.com/tables/filter-duplicates-conditional-formatting I use an Excel Table in this video, and I highly recommend learning how to use Tables. I have an entire video on a Beginner's Guide to Excel Tables to help you get started. Here is the link. https://youtu.be/fJnJTff4Na8 The video above on filtering duplicates is a FREE sample lesson from my Filters 101 Course. Filters 101 is an online course that will help you learn filtering techniques to prepare and analyze your data faster. Please click the link below to learn more about the Filters 101 Course. https://www.excelcampus.com/filters101
Views: 20762 Excel Campus - Jon
2 Ways to Reverse the Number Sign Positive Negative in Excel
 
05:51
Learn 2 ways to reverse the sign of a number from positive to negative or negative to positive in Excel. https://www.excelcampus.com/tips/reverse-number-sign/ Flipping or reversing the number signs to positive or negative is a common task in Excel. In this video we look at two quick ways to accomplish this. First, we can use a formula to multiply an existing cell by negative one *-1. Second, we can use the Paste Special Multiply Operation to copy a cell that contains a -1 and multiply an existing range of cells to reverse the signs.
Views: 70930 Excel Campus - Jon
How to Write VBA Macros to Copy and Paste VALUES in Excel - Part 2 of 3
 
03:20
Free Video Training Series on Macros & VBA: http://www.excelcampus.com/free-vba-training Download the example workbook at: http://www.excelcampus.com/vba/copy-p... Learn how to copy and paste values in Excel with VBA macros. This video tutorial explains a very simple way to paste values by setting the value of a cell or range equal to the value of another cell or range. It's a very efficient method for pasting values. Watch Part 1: https://youtu.be/sfspd_QdbMg Watch Part 3: https://youtu.be/fQX4N4ePhaU
Views: 108510 Excel Campus - Jon
Find the Last Row, Column, or Cell in Excel VBA with the Range.End Method (Part 1 of 3)
 
06:56
Learn how to find the last used or non-blank row, cell, or column in a worksheet using VBA. In this video I explain the Range.End method which works well when your data does not contain blank cells. Watch PART 2 (Find Method): https://www.youtube.com/watch?v=6PLmcweo50I Watch PART 3 (SpecialCells): https://www.youtube.com/watch?v=BLxBm1yq4Js Download the file that contains the code + full article: http://www.excelcampus.com/vba/find-last-row-column-cell/ Free email newsletter: http://www.excelcampus.com/newsletter
Views: 88087 Excel Campus - Jon
Setup a Slicer to Sort or Filter Another Slicer for Quick Navigation
 
08:36
Learn how to group text or number fields to create a slicer that can sort and filter another slicer. This technique is great for slicers that contain a lot of items and are difficult to navigate. You can create a slicer with less items that will help sort and filter the longer slicer. This technique can be used for slicer fields with text (people names, product names, etc.) or numbers (phone numbers, id numbers, account codes, etc.) Download the file and read the article at: http://www.excelcampus.com/pivot-tables/how-to-sort-a-slicer-with-another-slicer/ Subscribe to my free email newsletter: http://www.excelcampus.com/newsletter
Views: 62531 Excel Campus - Jon
How to Analyze Survey Data Part 1 - Unpivot Data with Power Query
 
05:44
Checkout the full article and download the file at: http://www.excelcampus.com/pivot-tables/analyze-survey-data-in-excel/ Learn how to use Power Query to transform multiple choice survey data in Excel. This survey data has been exported to Excel in a format that is not easy to use for a pivot table. In this video you will learn how to use the Unpivot feature in Power Query to transform or normalize the data. This will make it easier to analyze with a pivot table and chart. Please subscribe to my free email newsletter to get more Excel tips and tutorials like this. http://www.excelcampus.com/newsletter PART 2: https://youtu.be/h-sKEPEvwZ8 PART 3: https://youtu.be/NBgL8ItVdKY
Views: 36657 Excel Campus - Jon
5 Ways to Use VBA Macros for Excel in Your Job
 
11:00
In this video I share 5 different ways to use VBA Macros for Excel in our jobs. DOWNLOAD the file: https://www.excelcampus.com/vba/5-uses-for-macros/ We can use macros to automate both simple and complex Excel tasks, and save a TON of time with our job. This includes everything from simple data related tasks like removing duplicates, creating formulas, and copy/pasting data. To more complex tasks like building pivot tables or updating 100's of Excel files with data. In the video we look at several examples of macros in the following 5 topics: - Data related tasks 0:30 - Workbook tasks 3:57 - Pivot Table tasks 5:23 - Userforms & Add-ins 6:51 - Process Automation 8:52 You can download the Excel file used in the video at the following link. https://www.excelcampus.com/vba/5-uses-for-macros/ Please register for my free webinar on "The 7 Steps to Getting Started with Macros & VBA" to learn more about how to use macros to automate tasks, save time with your job and advance in your career. Here is the link to register. It's FREE! https://www.excelcampus.com/free-macros-webinar
Views: 32300 Excel Campus - Jon
How to Re-enable an Add-in that is Disabled or Missing
 
02:29
Is your add-in missing? Learn how to re-enable it with this step-by-step tutorial Excel Add-ins can get disabled when Excel crashes. There is a Disabled Items window on the Excel Options, Add-ins menu that will allow you to re-enable or reactivate the add-ins that have been disabled. Checkout my video on how to install an Excel add-in: https://www.youtube.com/watch?v=reuU2zUsEPM I also have an article with a step-by-step guide on how to install add-ins. http://www.excelcampus.com/tools/how-to-install-an-excel-add-in-guide/ If this does NOT resolve the problem for you, then you might be impacted by an Office Security update that was released in July 2016 by Microsoft. Checkout this article for more details and instructions on how to fix it. https://www.excelcampus.com/vba/add-in-ribbon-disappears/ Please leave a comment below with any questions. Thanks!
Views: 64166 Excel Campus - Jon
Excel Keyboard Shortcuts to Select Column with Blank Cells
 
02:45
Free Gift - http://www.excelcampus.com/freegift Excel keyboard shortcuts to quickly select an entire column of data that contains blank cells. http://www.ExcelCampus.com
Views: 47770 Excel Campus - Jon
Pivot Chart Formatting Makeover in Excel 2010
 
03:08
http://www.excelcampus.com/charts/pivot-tables-dashboards-part-1/ Learn how to change the formatting settings on a standard Bar Chart. The default settings of the Pivot Chart add a lot of uneccessary chart element that distract the reader from the overall story that the chart is trying to tell. This video explains how to remove these elements and prepare the chart for a presentation or dashboard.
Views: 29083 Excel Campus - Jon
New Excel Features: Dynamic Array Functions & Formulas that Spill
 
09:07
Microsoft just release a brand new feature for Excel called Dynamic Arrays. This is a set of functions, and new functionality, that allows us to create formulas that return multiple results to multiple cells. Read the article, download the file and FREE eBook: https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges 🚀UPDATE: These new features are now available to all users on the free Office Insiders Fast channel for Office 365. See the link above for more details. Dynamic arrays will eventually replace the Ctrl+Shift+Enter array formulas we use today. This will make is so much easier to write array formulas, and work with the output (spill range). The cells that the dynamic arrays output to are known as the Spill Range. This range is dynamic and automatically updates (spills) as changes are made to the source range or its precedents. The spill range can be referenced with the new Spill Ref notation (A4#). This is the first cell in the spill range, followed by the hashtag or pound symbol. In the video I share the new UNIQUE function that removes duplicates and returns a list of unique values to the spill range. I show how we can combine this with the new SORT function to sort any range. The FILTER function allows us to apply multiple filters to a range and output the results into a spill range with multiple rows and columns. This new functionality for Excel makes it much easier to write array formulas, and opens up a new world of possibilities. These new functions and features are currently available to all users on Microsoft’s Office Insiders Fast (Monthly Targeted channel) program. The Insiders program is free for all Office 365 subscribers and gives you access to early release builds and features. See this link for more info. https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges Related Content: Ctrl+Shift+Enter Array Formulas: https://youtu.be/I23Cy8FkMLY How to Create Data Validation Drop-down Lists: https://youtu.be/KGnvCKiOLM0
Views: 22486 Excel Campus - Jon
How to Analyze Survey Data Part 3 - Summarize with Pivot Tables and Charts
 
06:03
Checkout the full article and download the file at: http://www.excelcampus.com/pivot-tables/analyze-survey-data-in-excel/ In this third part of the series we learn how to use Pivot Tables and formulas to analyze the multiple choice survey data. We also create a chart that shows the percentage of total responses for each item (choice) in the survey question. Please subscribe to my free email newsletter to get more Excel tips and tutorials like this. http://www.excelcampus.com/newsletter PART 2: https://youtu.be/h-sKEPEvwZ8 PART 3: https://youtu.be/NBgL8ItVdKY
Views: 38140 Excel Campus - Jon
Copy & Paste Visible Cells Only in Excel for Windows or Mac
 
03:44
http://www.excelcampus.com/newsletter Use this simple keyboard shortcut to select the visible cells only when copying and pasting a range with hidden rows or columns. Windows: Alt+; Mac: Cmd+Shift+Z The following video explains a few tricks for pasting to the visible cells when the range contains hidden rows or columns. https://www.youtube.com/watch?v=mrDhkClPXEM Here is the article where I explain this technique in more detail. https://www.excelcampus.com/keyboard-shortcuts/copy-paste-select-visible-cells/
Views: 217625 Excel Campus - Jon
Pivot Table Calculation Type Default to Sum Instead of Count
 
06:00
Learn why the pivot table calculation type defaults to Count sometimes, and how to fix it to default to Sum. https://www.excelcampus.com/pivot-tables/calculation-default-to-sum/ One of the most common questions I see on my free 3-part video series on pivot tables & dashboards (https://www.youtube.com/watch?v=9NUjHBNWe9M) is, “Why does the pivot table default to Count instead of Sum when I add a field to the values area?” Well, Pivot Tables have some rules on which calculation type will be selected when we add a field to the Values area. The rules are pretty simple: If ANY cell in the column (field) contains text, blanks, or errors (#N/A, #REF!, #VALUE!, #NAME?), then the calculation type will default to COUNT. If ALL cells in the column (field) contain numbers, then the calculation type will default to SUM. Now that we know the rules, we can prevent this error by cleaning up our data. In the video above I show a data set that contains blanks in the Revenue column. Since there are blanks in this column, the pivot table calculation type defaults to Count. One quick way to fix this is to replace the blank cells with a zero (0) value. Here are instructions on how to find & replace all blanks in a column. 1. Select all cells in the column or Table (keyboard shortcut: Ctrl+Space Bar). 2. Open the Replace Window from the Home tab on the Ribbon in the Find & Select menu (keyboard shortcut: Ctrl+H). 3. Delete everything in the Find What text box so it finds blank cells. 4. Type a zero 0 in the Replace With box. 5. Press the Replace All button (keyboard shortcut: Alt+A). 6. Refresh the pivot table (keyboard shortcut: Alt+F5). 7. Add the field to the Values area of the pivot table. The calculation type should default to a Sum calculation if all cells in the data source column are numbers. Blank cells are the most common cause that I see for calculation type defaulting to Count. However, cells that contain text or errors can also cause this problem. If the source data column contains a formula that is returning errors, we can use an IFERROR function to return a zero instead of the error. Of course we will want to determine why the error is occurring in the first place. My free 3-part video series on the lookup formulas (https://www.excelcampus.com/lookup-formulas-free-training/) has an entire video dedicated to learning how to prevent and fix errors with formulas like VLOOKUP and INDEX/MATCH. Sometimes values in cells look like numbers, but the numbers are stored as text. This is a common issue when you are importing data into Excel from another system. In this case we will need to convert the text to numbers first. Checkout my article on keyboard shortcuts to convert text to numbers (https://www.excelcampus.com/keyboard-shortcuts/excel-convert-text-to-number-keyboard-shortcuts/) to learn more about these techniques. At the end of the video I also explain how PivotPal can save time with this issue. PivotPal has it’s own rules for adding a field to the values area, and does NOT default to count if the column contains blanks. If the column contains numbers and blanks, PivotPal will default the calculation type to Sum when we add the field to the values area using the PivotPal window. This is one way to get around this annoying issue without having to modify the source data. The PivotPal Add-in is packed with features that will save you time when working with pivot tables in Excel. Please click the link below to learn more and give PivotPal a try today. https://www.excelcampus.com/pivotpal/
Views: 30815 Excel Campus - Jon
How to Fix the Disappearing Add in Ribbon Menu Bar in Excel
 
04:11
Is your Add-in Ribbon Menu disappearing? A recent Microsoft Office security update has caused Add-in Ribbon Menu Bars to disappear when Excel is opened. Here is a link the article with step-by-step instructions. https://www.excelcampus.com/vba/add-in-ribbon-disappears/ Microsoft released an Office Update in July 2016 that is causing this issue with installed add-in Ribbons disappearing. The security update is blocking files that have been downloaded from the internet. Files downloaded from the internet now have a Security property that is automatically set to Block. When you open regular Excel files (.xlsx or .xlsm), you will get a security prompt asking you if the file is safe. However, there is no security prompt for add-in files (.xla, .xlam). That means there is nothing to tell you why the add-in ribbon disappeared. So if you have downloaded an Excel Campus add-in recently, or an add-in from another site, then you might not be seeing it load up when you open Excel. Argh!!! If this still does NOT solve your problem then your add-in might have become disabled. Checkout this video on how to restore a disabled add-in. https://www.youtube.com/watch?v=mJlemzmo8t0
Views: 58174 Excel Campus - Jon
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar (Part 3 of 4)
 
08:36
Learn how to add custom macro buttons to the Excel Ribbon or Quick Access Toolbar. This allows you to run macros from your personal macro workbook at any time, on any open workbook. Read the article and download the Excel file here: http://www.excelcampus.com/vba/personal-macro-workbook-video-series/ The Excel ribbon can be customized with new Tabs, Groups, and Buttons that will run your macros. You will also learn how to add macro buttons to the Quick Access Toolbar (QAT). The advantage here is that you can use the Alt+number key keyboard shortcuts to press a QAT button to run your macro. This makes it really fast to run a macro on any open workbook. Other videos in this series: How to Create a Personal Macro Workbook in Excel and Why You Need It (Part 1 of 4): https://www.youtube.com/watch?v=rD2-mSbTuL0 Where Is The Personal Macro Workbook Stored and How to View It (Part 2 of 4): https://www.youtube.com/watch?v=6cynP3OkIT0 How to Add a Yes No Pop-up Message Box to a Macro Before it Runs (Part 4 of 4): https://www.youtube.com/watch?v=rCh7ki9yVsM&t Please leave a comment below with any questions, and don't forget to hit the LIKE button if you enjoyed this video. Thank you!!!
Views: 95078 Excel Campus - Jon
Slicer Pivot Table Cache Relationship and the Filter Controls Error
 
07:22
Learn how the relationships between the slicers, pivot tables, and pivot caches work in Excel, and how all these objects are connected. Read the full article: https://www.excelcampus.com/pivot-tables/connected-filters-error/ I explain the filter controls error that occurs when trying to change the source data range of a pivot table that is connected to multiple slicers. There are two solutions for this error. The first method is to disconnect the slicers from the pivot tables, change the source data range for each pivot table, then reconnect the slicers. The second (better) method is to use Excel Tables for the source data ranges of your pivot tables. The Tables automatically extend to include new data, and you never have to update the source data range when new rows are added. This also prevents the filter control connections error because both pivot tables will always share the same cache. Here is an article and video on a Beginners Guide to Excel Tables. https://www.excelcampus.com/tables/excel-tables-tutorial-video/ Learn more about PivotPal here: https://www.excelcampus.com/pivotpal Please leave a comment below with any questions. Thank you!
Views: 10746 Excel Campus - Jon
How to Analyze Survey Data Part 2 - Unpivot Column of Comma Separated Values
 
04:04
Checkout the full article and download the file at: http://www.excelcampus.com/pivot-tables/analyze-survey-data-in-excel/ In this second part of the series we learn how to use Power Query to transform multiple choice survey data that is in a single column of comma separated values. This survey data has been exported to Excel in a format that is not easy to use for a pivot table. In this video you will learn how to use the Split Column and Unpivot features in Power Query to transform or normalize the data. This will make it easier to analyze with a pivot table and chart. Please subscribe to my free email newsletter to get more Excel tips and tutorials like this. http://www.excelcampus.com/newsletter PART 1: https://youtu.be/GJznCipDeg0 PART 3: https://youtu.be/NBgL8ItVdKY
Views: 14721 Excel Campus - Jon
Align & Size Excel Dashboard Elements with Snap to Shape
 
05:50
In this video I explain how to use the Snap to Shape feature in Excel to resize & align all the charts, slicers, images, and shapes on your dashboards. This technique helps save time when aligning all the elements on your dashboard, and is a good alternative to the other Align buttons. Snap to Shape is a mode we can toggle on/off in Excel. It's a pretty simple feature. When we move a shape by dragging it with the mouse, it will snap into alignment with other shapes on the worksheet. This also works when we are resizing shapes with the mouse. It makes it quick and easy to align all the elements on a dashboard, flowchart, org chart, or any other project in Excel where you have a lot of shapes on the worksheet. Here is a link to the article with step-by-step instructions: https://www.excelcampus.com/charts/dashboard-snap-to-shape-alignment/ Here are links to other videos mentioned in this video: Intro to Pivot Tables & Dashboards part 1 (learn how to build the dashboard used in the video): https://youtu.be/9NUjHBNWe9M Intro to Pivot Tables & Dashboards part 3 (learn how to align shapes with the Align buttons): https://youtu.be/FyggutiBKvU
Views: 9099 Excel Campus - Jon
How to Install an Excel Add in
 
04:21
Step-by-step instructions on how to install an Excel add-in in Excel 2007, 2010, 2013, 2016 for Windows. IMPORTANT NOTE: A July 2016 Office Security Update is causing the add-in ribbon menu to disappear. Here is a video that explains how to fix it. https://www.youtube.com/watch?v=AhnOU-ulqNg Installing the add-in is pretty easy, and should only take a few minutes. 1. Save the add-in file to your computer. – Pick any folder on your hard drive that is easy for you to find. – My suggestion is to create a folder named “Excel Campus” and place it in your Documents folder. 2. Open Excel and go to the Options Menu. – This varies depending on your version of excel. – 2007 – Office Button, Excel Options – 2010 & 2013 – File, Options 3. Click the “Add-Ins” option on the left-side menu, and then click the “Go…” button. – You may have to wait a few seconds for the window to appear. 4. Click the “Browse…” button on the Add-Ins Menu. 5. Locate the add-in file you saved in Step 1, and click OK. 6. The Add-in will appear in the list and should be enabled, click OK. 7. Installation Complete – The add-in is now installed and should automatically open every time to you open excel. Here is an article with written instructions and images. You can also download a pdf version and print it. http://www.excelcampus.com/tools/how-to-install-an-excel-add-in-guide/ Checkout my video on how to uninstall and completely remove an Excel Add-in: https://www.youtube.com/watch?v=5MVbR4pKAbU
Views: 142004 Excel Campus - Jon
Refresh Pivot Table Automatically when Source Data Changes
 
09:14
In this video I explain how to refresh pivot tables automatically when the source data changes. This solution uses a simple VBA macro and the Worksheet Change event. The Worksheet Change event macro runs every time a change is made to a worksheet. We can use this macro on the sheet that contains the source data to make sure our pivot tables are refreshed whenever the source data is modified or updated. Download the Excel workbook that contains the code: https://www.excelcampus.com/vba/refresh-pivot-tables-automatically/ I also share an alternative to the macro. We can enable the Pivot Table option: "Refresh data when opening the file" That option will only refresh the pivot tables when the file is opened by the user. It will NOT refresh the pivot tables when changes are made to the source data. For that to happen we need to use the macro solution. Related videos & articles: VBA Code Modules & How to Run Event Macros Based on User Actions: https://youtu.be/VJWBOuNXHB4 3-part Video Series on Getting Started with Macros & VBA: http://www.excelcampus.com/vba-training-my-first-macro/ 3-part Video Series on Pivot Tables & Dashboards: https://youtu.be/9NUjHBNWe9M Refresh Power Query Queries Before Pivot Tables (disable background refresh): https://www.excelcampus.com/library/enable-background-refresh-on-all-power-query-connections/
Views: 21393 Excel Campus - Jon
Compare Multiple Lists with a Pivot Table
 
08:14
http://www.excelcampus.com/pivot-tables/compare-lists-with-pivot-table/ Learn how to create a simple report to compare two or more lists of data with a pivot table. This pivot table technique is very fast and easy to implement and does NOT require any complex formulas. Download the workbook to follow along. http://www.excelcampus.com/newsletter/
Views: 54752 Excel Campus - Jon
How to Filter Pivot Tables for Month-to-Date MTD Comparisons with Slicers
 
09:28
Learn how to create month to date (MTD) calculations in your pivot tables for month-over-month ,quarter-over-quarter, or year-over-year comparisons. We will use slicers and the DAY function to filter the pivot table for any range of days in the month. Download the example file to follow along: http://www.excelcampus.com/pivot-tables/month-to-date-mtd-comparisons-pivot-table Additional Resources for this video: Free video series on Pivot Tables and Dashboards - www.excelcampus.com/pivot-table-checklist-yt Video Tutorial on Excel Tables - https://www.youtube.com/watch?v=fJnJTff4Na8 Video Tutorial on how to use Slicers - https://www.youtube.com/watch?v=m5wWZE9tk7M
Views: 88094 Excel Campus - Jon
3 Tips for Writing Formulas with VBA Macros in Excel
 
08:09
In this video I share 3 tips for writing and creating Excel formulas with VBA macros. Download the file: https://www.excelcampus.com/vba/writing-formulas-with-macros/ You will learn: 1. How to use the Formula property of a range object to insert a formula into a cell. The formula is text that begins with the equals = sign and is wrapped in quotation marks. 2. How to use the macro recorder to create the VBA code for more complex formulas that contain special characters. 3. What R1C1 style formula notation is and how it works. R1C1 allows us to create relative references to cells (rows & columns), instead of hard coding cell addresses like A1. R1C1 can be used for both absolute and relative references, and also mixed references (absolute row & relative column). Register for my free upcoming webinar on getting started with macros & VBA: https://www.excelcampus.com/yt-vba-webinar Related videos: 2 Critical VBA Assumptions: https://youtu.be/VohlY8WqLCg Free webinar on learning macros & VBA: https://youtu.be/EN8fSXjGFnI
Views: 24847 Excel Campus - Jon
Pivot Charts for Mac Excel 2016
 
04:33
Pivot Charts are now available for Excel 2106 for Mac. In this video I explain what a PivotChart is and how to insert one in the Mac version. This new feature is currently on a pre-release version of Excel on the Insiders Fast channel. Please see my other video on how to get this version (https://youtu.be/xHnxjjIeIrQ). It is available to everyone with an Office 365 subscription. PivotCharts (pivot charts) are linked to a pivot table and we can add slicers to quickly create interactive dashboards. This makes it fast and easy to take a large sheet of data and quickly create summary reports and dashboards. Checkout my 3-part video series on Pivot Tables and Dashboards in Excel to learn how to create interactive reports. https://youtu.be/9NUjHBNWe9M This new version of Excel for Mac also includes updates to the VB Editor (VBA). Checkout my video the New Visual Basic Editor for Mac for more details. https://youtu.be/eRhp1Og269E Please subscribe to my email newsletter to get updates and a free gift! https://www.excelcampus.com/newsletter
Views: 33186 Excel Campus - Jon
Excel Array Formulas Explained with MIN and IF Functions (Part 1 of 5)
 
12:48
In this video you will learn how array formulas work in Excel and how to calculate the Minimum of one column based on criteria from another column. Download the file: https://www.excelcampus.com/functions/formula-challenge-first-sale-each-month/ This is the first video in a series on solutions to the Formula Challenge to find the first sales transaction for each month of the year. There were over 100 solutions submitted on the blog page, so I created a series of videos to explain the most popular solutions. The most popular solution was to use a MIN IF array formula. This first video explains how array formulas work with a simple example. I also explain how to enter the array formula with Ctrl+Shift+Enter. We then use the Evaluate Formula window to step through each part of the formula and learn how Excel calculates arrays. In the next video I explain how to combine the MIN IF with VLOOKUP or INDEX MATCH functions, to return a matching result from another column. We will also look at NON-array solutions withe AGGREGATE and MINIFS functions in future videos. Additional links: #2 MIN IF Array Formulas with VLOOKUP or INDEX MATCH https://youtu.be/IdVUhCBhjps #3 How to use AGGREGATE for MIN IF Formulas in Excel https://youtu.be/4utkmMTIoXU #4 How to use MINIFS to Calculate Minimum Value based on Multiple Criteria https://youtu.be/pGvQnCJxEt4 #5 How to Use AGGREGATE for MIN IF Formula with Multiple Criteria https://youtu.be/NJFCd3HimXc Calendar System & Dates Explained in Excel: https://www.excelcampus.com/functions/how-dates-work-in-excel/ Beginner's Guide to Excel Tables: https://www.excelcampus.com/tables/excel-tables-tutorial-video/ How to Turn Table Formulas On/Off: https://www.excelcampus.com/library/turn-off-structured-references-table-formulas/
Views: 18306 Excel Campus - Jon
Apply Conditional Formatting to Shapes in Excel
 
05:43
Learn 2 ways to apply conditional formatting to shapes in Excel. This technique is great for dashboards and interactive reports where you don't want to be confined to the grid layout of the cells. There is no way to actually apply conditional formatting to shapes in Excel, so both these methods are workaround solutions. Here is the link to the article and page where you can download the sample Excel file: http://www.excelcampus.com/tips/conditional-formatting-shapes/ Method #1: A simple solution is to copy and past e the cell that contains conditional formatting as a Linked Picture. The paste special linked picture command will create a shape that displays the formatting and values of the cell. As the cell's value and formatting changes, the linked picture will also update to reflect the changes. You can use this technique for a single cell or a range of cells. The picture is resizable so you can make the shape bigger for your dashboard. You cannot change the shape type directly in the Excel menus or ribbon. However, you can use VBA to change the shape type. The following line of code will change the shape type of the selected shape with VBA. Selection.ShapeRange.AutoShapeType = msoShapeRightArrow Method #2: The CF Shapes Add-in allows you to create custom shapes with conditional formatting for the shapes fill color. Click the following link to learn more about the CF Shapes Add-in: http://www.excelcampus.com/cfshapes If you are new to conditional formatting or really want improve your skills then I highly recommend the Conditional Formatting Video Course by Dave Bruns at ExcelJet.net. Here is the link to learn more about the course: http://www.excelcampus.com/exceljet-conditional-formatting-course
Views: 46485 Excel Campus - Jon
How to Add a Table of Contents Gallery to any Excel File
 
03:51
Learn how to add a Table of Contents Gallery sheet to any Excel file. This sheet contains thumbnail images of each sheet in the workbook with clickable links to navigate to the sheet. You can download the file that contains the macro and learn more about the Tab Hound Add-in on the following page. https://www.excelcampus.com/vba/table-of-contents-gallery/ The Tab Hound Add-in allows you to create this Table of Contents sheet in any workbook with the click of a button. You do NOT need to know VBA or macros to use Tab Hound.
Views: 11606 Excel Campus - Jon
Excel Tables Tutorial: Beginners Guide for Windows & Mac
 
16:09
http://www.excelcampus.com/tables/excel-tables-tutorial-video/ Free Gift - http://www.excelcampus.com/freegift How to Create a Table | 1:16 1. Formatting | 1:55 2. Table Names | 2:44 3. Sorting & Filtering | 3:15 4. Auto Expansion & Navigating | 4:35 5. Total Row | 6:34 6. Remove Duplicates | 7:18 7. Create Unique List | 7:53 8. Pivot Table Integration | 8:33 9. Chart Integration | 10:24 10. Table Formulas | 11:26 In this video I show you how to create a table, then explain 10 awesome reasons to use tables. Excel Tables will save you time and make you more productive at organizing and analyzing lists of data. Tables are available for Excel 2007, 2010, 2013, and 2011 for Mac. They integrate with some of Excel's most powerful tools including charts and pivot tables. Please see the link above for additional resources and videos that will help you learn more about tables. There are many great benefits to using tables. Leave a comment below with questions. Thanks! :-)
Views: 77501 Excel Campus - Jon
How to Delete Blank Rows in Excel
 
07:41
Learn how to delete entire blank rows from a data set or table. In this video we look at a few different ways to delete entire blank rows from a worksheet. This is a common task for exported reports, and we need ways to delete the blank rows quickly. I explain how to filter for blanks, how to create a column of COUNTA formulas to count all the cells in a row that are not empty, and how to use a macro to quickly select the blank rows. Here are the links mentioned in the video: Download the file with the macro & read the full article: https://www.excelcampus.com/tips/delete-entire-blank-rows/ Beginner's Guide to Excel Tables Video: https://youtu.be/fJnJTff4Na8 How to turn off Table formulas (structured references): https://www.excelcampus.com/library/turn-off-structured-references-table-formulas/ Excel Filters Training 3 Part Series: https://www.youtube.com/playlist?list=PLKbOx3gUV_E-eJGxHn1mJQDxYlX1ErGyO Personal Macro Workbook 4 Part Series: https://www.youtube.com/playlist?list=PLKbOx3gUV_E_tY-0oSzMw4NE0oFy5sI0_
Views: 43048 Excel Campus - Jon
How to Turn Off GetPivotData for Excel Pivot Tables
 
04:03
In this video you will learn how to turn the Generate GetPivotData feature on/off for pivot tables. This feature creates the formulas with the GETPIVOTDATA function when we reference a cell inside a pivot table. Although GETPIVOTDATA formulas can be useful, they can also be annoying. Fortunately we can turn the feature on or off with a setting on the Options/Analyze tab of the Pivot Tables ribbon. Here is a link to the article with more details. https://www.excelcampus.com/pivot-tables/turn-off-getpivotdata/ I am also running my free training webinar on pivot tables. This is an hour long training on "The 5 Secrets to Understanding Pivot Tables". You will learn how pivot tables work to quickly summarize our data and create interactive reports and dashboards. I explain what the proper layout and formatting that is required for the source data, and we also take a look at Power Query. Click here to register for the free webinar. https://www.excelcampus.com/pivot-secrets-webinar
Views: 6063 Excel Campus - Jon
Convert Text to Numbers With Negative Sign at the End
 
05:30
In this video I explain how to convert text to numbers in Excel when the cell value contains a trailing negative sign at the end of the number. This happens with data that is exported from financial systems like SAP. We can use the Find & Replace menu to remove the negative sign, then use the Paste Special Multiply operation to convert the number back to a negative. Checkout my video on how to reverse the signs of a number for more details on the Paste Special Multiply operation. https://www.youtube.com/watch?v=qpR0_SJ_uIM
Views: 10456 Excel Campus - Jon
Excel Filters Training - Part 1 of 3
 
16:08
In this video series you will learn how to work with the Filters in Excel. Filters hide and unhide rows to help us prepare our data for analysis and tie out numbers to formulas, pivot tables, and summary reports. Click here to download the file used in the video: https://www.excelcampus.com/tables/filters-training-part-1/ In this first video I explain an overview of filters and how to apply them to your data. I share some advantages of using Excel Tables for filtering data. I also show navigation shortcuts to help find filtered columns. Click the following link to watch my video on a Beginners Guide to Excel Tables to learn more about this awesome feature of Excel. https://www.youtube.com/watch?v=fJnJTff4Na8 The second video is all about keyboard shortcuts for the filter drop-down menus. These shortcuts will save you tons of time when working with filters. In the third video you will learn how to apply filters to multiple columns with both AND and OR logic. I also explain the rules that Excel uses when applying filters to multiple columns. Throughout the video series I will also be sharing an Excel Add-in I developed called Filter Mate. Filter Mate is a productivity add-in that will help save you time when working with filters. Filter Mate & the Filters 101 Course are now available. Click here to learn more. https://www.excelcampus.com/filter-mate/ Please leave a comment on the following page with your favorite filtering tip. https://www.excelcampus.com/tables/filters-training-part-1/ Video #2: https://www.youtube.com/watch?v=Z1_oq-uPUBU Video #3: https://www.youtube.com/watch?v=rgATdkyLey8
Views: 25347 Excel Campus - Jon
2 Critical Excel VBA Assumptions You Must Know
 
07:28
In this video I explain two critical assumptions that VBA makes when running our Excel macros. Knowing these assumptions will help prevents errors and possible disaster when creating macros for Excel. I explain more about these assumptions and other tips for getting started with VBA in my FREE webinar on "The 7 Steps to Getting Started with Macros & VBA". https://www.excelcampus.com/yt-vba-webinar This is an hour long in-depth training on getting started with macros. Even if you have been using VBA for awhile, you will probably learn a lot from this training. The webinar is going on right now, so click the link to register for a day & time that works for you. https://www.excelcampus.com/yt-vba-webinar The assumptions I'm referring to are all about Context. We must tell VBA what workbook and worksheet we want to run our code on. Otherwise, VBA assumes we want to run it on the active workbook and active worksheet. This assumption can get us in trouble if the macro runs on the wrong workbook or worksheet. So it's critical that we know how to write macros with this assumption in mind.
Views: 10855 Excel Campus - Jon