Excel 2013 Lessons

https://docs.google.com/document/d/1-84hRWF7McxBP-Ij4YmwOHTgVCCkoQqHiW6_3sbuI8Y/edit?usp=drivesdk

Click on link above for my FREE ebook

Excel Made Easy Excel Made Easy By Roger Mullins VOL.1 I will be using the Excel 2013 version, or if it is not available in 2013 I will put the year it is available. ALL FUNCTIONS WORK IN GOOGLE SHEETS and GOOGLE SHEETS IS FREE. It really does look like a lot,but don’t worry I will have you writing like a ...

FILTER Function

You are going to love this one. It is a FILTER function and, in this example, it is going to create an Inventory in one Cell Array and in the Next Cell Array it will be In Stock and the third it will be Out Of Stock. See the image for the In Stock Block and for the Out Of Stock the only thing that changes in that formula is the > greater than sing to the = equal sign.

Created a small set of items like in the Inventory block complete with the quantity. You can use any product or quantity and you can edit the products and quantities later to meet your needs and changes in your actual inventory in the other 2 blocks will update automatically.

For the In Stock (you can see the formula in the FORMULA BAR) notice that I have put the formula in cell E5. We start out with =FILTER( then what cell array or range your information is in which is B5:C9 next don’t forget the comma after C9. Then only the range where the quantity is, which is C5:C9. Next we add the greater than symbol > then 0 (zero). Now all that is left to do is close the formula with the Closed Parentheses ) and click Enter. Now the information in all the cells that are greater than zero will be there and the ones that have no quantity will be left out.

Your Formula in cell E5 should look like this:
=FILTER(B5:C9,C5:C9>0)

3; For the Out of Stock like in the In Stock section I start out with =FILTER( then the same cell array or range your information is in which is B5:C9 again don’t forget the comma after C9. Like the previous image, only the range where the quantity is, which is C5:C9. Next, we add the Equal sign = instead of the greater than sign, then 0 (zero). Now all that is left to do is close the formula with the Closed Parentheses ) and click Enter. Now the information in all the cells that are equal to zero will be there and the ones that have a quantity will be left out.

Your Formula in cell H5 should look like this:
=FILTER(B5:C9,C5:C9=0)

Now when you make any changes to your Inventory block the other two will automatically reflect the new changes.

Want to make your columns and rows expand automatically?

1, On your spreadsheet at the bottom left-hand corner, right click on Sheet Number or name if you renamed it.

2. Then click on "View Code".

3. At the top of the page where you see General click the down arrow in the right-hand corner of the box and then select "Worksheet"

4. Between the Private Sub and the End Sub type in the following code:

Cells.EntireColumn.Autofit
Cells.EntireRow.Autofit

The Macro editor should look like the image below.

Now you can close the macro editor by clicking the x at the top right-hand corner like normal.

5. Save Workbook at Macro Excel Macro-Enabled Workbook.

Now type whatever you want in a single cell and the cell will automatically expand to fit your text.
ENJOY

The book is now well informed. If you would like a copy of this book you can get it via Google Docs for a low price of $25.00 per year. Message me if you are interested. The book will automatically expand when new chapters are created. Also to get to the chapter or lesson you want, all you have to do is touch it in the table of contents and it will take you straight to it. The best part is with this version you can copy and paste formulas then modify it to meet your needs. I have a regular job so I will read your when I get the chance. Thank you.