In this free Excel PowerPivot webinar, Excel Instructor Seth Bonder explains how to add PowerPivot functionality into Excel, how to create a data model and much more. To learn more about PowerPivot, or to enroll in one of our PowerPivot classes, please visit our Excel PowerPivot Course page. https://youtu.be/KhOYyMWkDv8
SkillForge SkillSheet Microsoft Excel 2010/13/16 Keyboard Shortcuts Click here to download the PDF version. Frequently Used Shortcuts In order to… Press Close a spreadsheet Ctrl+W Open a spreadsheet Ctrl+O Save a spreadsheet Ctrl+S Copy Ctrl+C Paste Ctrl+V Undo Ctrl+Z Remove cell contents Delete key Choose a fill color Alt+H, H Cut Ctrl+X Bold Ctrl+B Center align cell contents Alt+H, A, then C Format a cell from context menu Shift+F10 or Context key Add borders Alt+H, B Delete column Alt+H, D, then C The Function Keys—What They Do Key Description F1 Displays the Excel Help task pane. Ctrl+F1 displays or hides [...]
When Excel 2007 was introduced, the PivotTable Field List included a “Filter” area, where one could drop a field to use as a filter (for example, year, brand name, size, etc. from a block of sales data). If the user filtered for one year, or one brand, the name of the item was visible in the space above the body of the table. But if more than one item is in there, all it says is “multiple items”…and not which ones. The feature worked, but couldn’t take this into account. So in Excel 2010, and all versions following, the Slicer feature [...]
One of my wife's colleagues recently asked about whether it's possible to track changes in an Excel spreadsheet. It certainly is, though it works a little differently than in Word, say. Turning the feature on in Excel is quite easy--simply go to the Review tab in the Ribbon, slide over to the Changes group, and click Track Changes. Almost immediately, we see one of the differences between this and the Word version--we are told this will put the document into Shared mode, allowing others to open and edit the file at the same time. We also want to decide which specific [...]
Judging Access vs Excel can take thought. Most people find Microsoft Excel fairly easy to learn—it has a fairly gentle learning curve, the fundamentals only take a short time to pick up, and the program is actually fairly versatile—it’s not just a ledger book on steroids. Microsoft Access, on the other hand, can be kind of intimidating. Some folks hear the word “database” and, to quote the Joker, “they start losing their minds!” The whole idea of a database program is scary because there’s more to learn, more to set up before you can use it, and more to manage even [...]
Some of the Excel functions, such as IF, come into play all the time, even outside business. They're versatile and can do a lot. But others seem a little abstruse, or out in left field. One question I occasionally get from business people in Excel is something like, "How do I make sure someone has filled in all the cells where I need data to, say, calculate an average correctly?" (Think "tax form" or W-4, for example. You want to make sure certain spaces are filled in regardless.) In some situations, you might use a function like ISERROR, and incorporate it [...]
In recent years Microsoft has included several add-ins to boost Excels usage in the world of Business Intelligence. They include Power Map, Power Query, Power Pivot, and our topic of discussion today, Power View for Excel 2013. Each of these ship with the Professional Edition of Excel 2013 but need to be added through the Options Panel. After going through this process I was more than surprised to find Power View was still unavailable. So this posting will help those of you experiencing the same dilemma I faced recently. First things first. This is what my Insert tab looked like after [...]
https://www.youtube.com/watch?v=qAt6R-6rfUs In this Microsoft Access tutorial, you'll learn how to import data from a Microsoft Excel worksheet and create a table from it in an Microsoft Access database. To learn more about Microsoft Access, check out our Microsoft Access training courses!
One of the most asked questions of beginning, and sometimes seasoned, pivot table users is “How do I hide the entries in a pivot table whose totals equal zero?” On first blush, this seems like an easy feat, but users quickly discover that it’s not as easy as predicted. There are ways to sort the source data and then exclude the entries with zero values, but that task of sorting and filtering the source data would have to be performed each time the source data is updated. This is not an appealing prospect. There is actually a very easy way to [...]
If you are an Excel user who likes to create charts, design dashboards, or just likes to play with neat toys in Excel, this tutorial is going to be right up your alley. Excel contains (in a super-secret place) a hidden camera. “But why would I need a hidden camera in a spreadsheet program?” I’m glad you asked. If you have ever created a chart on one sheet, but you need the chard displayed simultaneously on a different sheet, and you don’t want to make two of the same thing, the camera tool will solve this problem. First thing’s first; we [...]
Keyboard shortcuts are a great way to improve the speed at which documents are built, regardless of the application. It seems like there is a keyboard shortcut for just about every feature Excel contains; and there may be that one guru in the office that knows them all. But most of us fall somewhere between Guru and Labrador retriever (hopefully, closer to the former.) The good news is that it’s not an “all or nothing” proposition when it comes to keyboard shortcuts. Knowing just a few of the most productive keyboard shortcuts will serve you far better than knowing none at [...]
https://youtu.be/Qg6sDPqke0g In this tutorial, you'll see how to subtract two dates from one another in Microsoft Excel using the DATEDIF function. The DATEDIF function allows you to calculate the difference between two dates and return a variety of values, including days, weeks, months or years. Learn more about Excel in our Microsoft Excel Training classes.
https://youtu.be/5a3j9-nvDf0 In this tutorial, you'll see how to create a report in Crystal Reports using data from an excel worksheet. This video will demonstrate the different methods for connecting to Microsoft Excel data from Crystal Reports and covers using both Excel 2003 and prior as well as Excel 2007, Excel 2010 and Excel 2013. For more Crystal Reports training see our Crystal Reports Training classes.
If there has ever been a more “You’ve got your chocolate in my peanut butter!” moment, it’s the blending of Conditional Formatting with the Subtotals tool in Excel. If you have ever used the Subtotals tool to group information you have probable been impressed with its ability to group data by some changing event (like States) and have those groups aggregated and then structured into a collapsible outline. Before Subtotals After Subtotals But the one shortfall when it comes to the Subtotals tool is that there are no built-in artistic styles that can be applied to give the list a bit [...]
Excel’s MODE function is a great tool for returning the most frequently occurring number in a set of numbers. But what if you want to return the most frequently occurring word in a list of words? MODE with Numbers Using the MODE function in Excel is quite simple; you point to a list of numbers and MODE will tell you which number occurs the most often. In this list, the number “4” appears more often than any other number. MODE with Words As you can see, the MODE function does not work very well when pointing to a list of words. [...]
Excel – Mixed Pivot Table Layout Microsoft Excel Pivot Tables is one of the greatest inventions known to man; second only to those buttery mints they bring you in fancy restaurants. Even the greatest of ideas are not without their issues. Take for example the Report Layout feature in Pivot Tables. Excel gives the user three options with which to display hierarchically (wow; that’s a hard word to spell) related data. Compact Form Outline Form Tabular Form Let’s examine what makes each layout unique. Compact Form All row-based data is combined into a single column, one row per line item, and [...]
Converting Names into Email Addresses Suppose you have a list of names, perhaps a roster of employee names, and you wish to generate email addresses for these individuals. If you work at a company that has an established standard for email addresses (i.e. first initial of first name with last name) then you have a few options. The preferred strategy depends largely on the version of Excel you are using as well as the naming pattern used in the emails addresses. Flash Fill (Excel 2013 / Excel 2016) If you are not familiar with Flash Fill, this tool allows you to [...]
In our recent free webinar, SkillForge instructor Bryon Smedley shows all of the cool things you can do with Pivot Tables in Microsoft Excel. If you're not using them already, you can learn all about them in our Microsoft Excel Pivot Table Training Class! https://youtu.be/KcYSI1cccoc
If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering. The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data set. But what do you do if you wish to filter by the Value-based items? In other words, the numbers in the “connect the dots” area where row and column choices intersect. These, on first glance, don’t appear to have sorting and filtering controls available. Rest assured, they do exist; you just have to dig a bit to [...]
VLOOKUP is great for returning information from a database, but one of the limitations is that the return information is static. What if the user wishes to look for certain data one day but different data another day? This would require either two different sets of VLOOKUP functions or the functions would need to be reprogrammed. In the database below, the user would wish to return address information in one scenario, but return financial information in another scenario. Suppose there are times when the user requires a mixture of the two; that would require a third set of VLOOKUP functions. This could [...]
Excel PivotTables are one of the greatest tools in the spreadsheet user’s toolkit. However, there is one tiny bit of functionality that appears to be missing: the ability of pivot tables to automatically update when information in the source data changes. Most user’s see this as a glaring lack of functionality. There is, however a very good reason why pivot tables do not automatically update. […]
Admit it; you’ve done this more than once. You open an Office application like Word or Excel and type out your next great novel or number-crunching masterpiece. Then, in a state of haste, you start closing windows and accidentally close the one window you intended to leave open. One second later you realize, this was the one windows you shouldn’t have closed. All is lost… or is it? […]
Admit it, you’ve used Excel functions without any idea how they work. Someone said, “Click here for this. Click here for that.” You’ve dutifully followed orders, blindly clicking on cells with no real clue as to why. It’s time to lift the veil of mystery and understand why these functions are so demanding of such data. Here is just one of many options for understanding function logic: This first part has nothing to do with obtaining function information, but it’s a great time saving trick. If you like to type your functions directly in the cell; press your EQUAL button [=] [...]
If you are new to Excel, you no doubt have already discovered the need to take a series of numbers created by formulas and convert them to fixed values. In other words, replace the questions with the answers. With no idea how to accomplish this, beginners usually spend great stretches of time retyping the numbers into the answer cells to make them “permanent”. Eventually, someone who feels your pain turns you on to the technique of highlighting all of your formula cells, clicking Copy, then in the same cells clicking Paste Special…, Paste Link. You could now not be more ecstatic. [...]
If you are an Excel user who LOVES working from the keyboard as much as possible (i.e. data entry, navigation, feature activation, etc...) then you simply MUST know of this little gem of a keyboard shortcut. Although we have no scientific data to back up this claim, our hunch is that the COMMA STYLE is the most popular style in the Number Styles library. To save time when applying the COMMA STYLE to cells, use the keyboard shortcut CTRL-SHIFT-1 This will apply the COMMA STYLE to the selected cells. If you want to experiment (what we call "having fun"), try the [...]
http://youtu.be/AHJIcqSFhzQ In this Microsoft Excel PowerPivot tutorial, you'll see how to use Microsoft Excel PowerPivot to import data from multiple data sources, link the data based on common fields and finally create a PivotTable and PivotChart to quickly analyze the data. This content is from our live, instructor-led Microsoft PowerPivot Training class.
http://youtu.be/kl7oBI2qn_Y?list=PLwLw21RsChcuPF4HitxlMXOaJtXCtMwIx In this Microsoft Excel tutorial, you'll see how to format cells in Microsoft Excel using the Format Painter tool. Learn more about Microsoft Excel in our Microsoft Excel Training courses.
Keyboard shortcuts may seem like a throwback to the olden days, but they can seriously ramp up your productivity once you start using them. Especially for tasks you do on a repeated basis. Here are some of the most common Microsoft Excel keyboard shortcuts. F7 - Check spelling F11 - Insert a chart F12 - Save As CTRL+O - File Open CTRL+N - File New CTRL+P - Print CTRL+; - Insert today's date CTRL+H - Find and replace ALT+ENTER - Start a new line in the same cell CTRL + HOME - Move to cell A1 CTRL+END - Move to the [...]