Perform a One Sample Proportion Test in Excel

This video covers how to perform a one sample test for proportion. This type of test used to estimate the proportion of a population. It compares the proportion to a target or reference value . Unfortunately Excel doesn't have a function in the Data Analysis Tool-pack to do this in a few steps, but it's not that hard if you lay out the calculations in a column and then re-use if needed.

Perform Year over Year Analysis with a Pivot Table

If you're familiar with Pivot Table, it's a great analysis tool. One common type of analysis is to compare the year over year (or month over month) change or growth of items. Maybe it the comparison of the actual number value or you could want the percentage change. One option is to have your data in the pivot table ready and make the calculations outside of the table (e.g., in the next column). But if you do it that way and make changes in the Pivot Table, the outside cell calculation could not work because they might be reference different cells now. This video will look at a simple data set of car sales and see how to compare the difference and percent differences over two years and make comparisons by date (months) and even by the car make to emphasis the flexibility of Pivot Table when you make changes. You don't need to calculate the change outside of the Pivot; instead let Excel do it for you. Then all you need to to it to plug your final table into a report or presentation.

Quick Charts, Totals or Conditional Formats with Quick Analysis Tool

If you're having trouble thinking about what kind of chart, formatting or calculation to help analyze your data, you can get some idea or insight from Excel by using the Quick Analysis Tool. It can give you a sample of the kind of visuals or formatting that can be applied to your dat

Create a Geo Map Chart - Google Sheets

If you've got data to map, you might want to using the geo chart maps in Google Sheets. As long as you have some type of value that can be mapped like city, state or country, you can assign colors or markers to your data on a geographic map. All you need are two columns; one for the locale and the other to represent some quantitative value. The catch is that there are just to geo map options to use in Google sheets. Also you can't map latitude and longitude. However there is a workaround to that which involves some script (don't worry it's a copy/paste) and later on I'll even so you how to incorporate google sheet data with the google maps application.

Use the Excel XLOOKUP Function

The XLOOKUP function is an Office 365 function that is sure to replace the other lookup functions like VLOOKUP, INDEX/MATCH, or HLOOKUP. It's so much more versatile and take the similar argument structure as the other functions and more. You can do various lookup variations like looking up to the left or looking up values from a bottoms up approach...and all this with just one function.

Google Sheets - Track Stocks with GOOGLEFINANCE

If you like to track your stock investments, Google Sheets has a function called GOOGLEFINANCE that helps you get delayed (~20 minute) stock price (or volume) information on numerous stocks in your portfolio. It can event track mutual funds and currency. You also have the option of getting historical information on those various stocks so you can do some anaylsis or comparisons of your portfolio. In this video I'll show you how to use the many ways of GOOGLEFINANCE to get this information and even show you how to create a small in cell chart to visualize the stock prices from 30 days or even 365 days to the current date.

Convert Numbers to Roman Numerals

Converting numbers to Roman numeral is quite easy with the ROMAN function. But I'll spice this up with some other tricks to get a series of numbers. If you have Office 365, you can use the SEQUENCE function to get a matrix of numbers in a 10x10 grid. If you don't have Microsoft Office 365, I'll so another method to get this matrix. Also if you're interested in how to convert from Roman numerals to number, it's also just as easy with the ARABIC function.