Tutorials For Excel everyday with EVBA.info part 15[Free PDF ebook] - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Sunday, November 10, 2019

Tutorials For Excel everyday with EVBA.info part 15[Free PDF ebook]

Tutorials For Excel everyday with EVBA.info part 15[Free PDF ebook]



EVBA’s Tutorials For Excel: Selecting a Large Range Without Scrolling or Dragging.

Here is a handy shortcut tip when you need to select a large range. In the example, I selected a small range of B2:H5 so the pictures can fit onto this web page for demonstration purposes.
But what if you need to select a large range from B2 to, say, HM62000. It would be a burdensome task to scroll down all those rows, and across all those columns, while dragging your mouse from B2:HM62000. There’s a faster and easier way to select a large range of cells.



Tom’s Tutorials For Excel: Comparing Lists For Identical Match
Here’s an example with the formula =AND(A1:A10=B1:B10) that returns TRUE when all pairs of cells match in a two-column list, and FALSE when at least one pair is different in any way.
This is an array formula, where you need to enter it with Ctrl+Shift+Enter, not just with Enter. If you are new to array formulas, see my video and explanation of arrays here.
Tom’s Tutorials For Excel: Repeating Your Last Action FAST! The Magic of the F4 Key.
Take a look at this gem: The F4 key is a little-used but highly efficient shortcut for speeding up your Excel tasks, when you need to repeat the same action once, twice, or dozens of times.



In Excel (and other Office apps as well), many tasks can be instantly completed by hitting the F4 key for as many times as you want to repeat your last action. The following pictures (formatting ranges, inserting new worksheets, and deleting rows) are just three examples that are repeatable with F4.

First example — Formatting ranges


Second example — Inserting worksheets


Third example — Deleting rows
Tom’s Tutorials For Excel: Filling Series For Linear and Growth
Here is an example of building a table of projected sales and income, using three Fill methods of Series, Linear, and Growth.

TIP:


First Example: Fill Series (add a year number to each filled cell)
• Enter the first year of your data, example 2010 in cell A4.
• Select cell A4 and drag its fill handle down while pressing the right mouse button.
• Release the right mouse button, and from the popup menu, select Fill Series.



Second Example: Series, Growth (project your Unit Sales to double every year)
• Enter your first item of data in the Unit Sales column, example 31 in cell B4.
• Select cell B4 and drag its fill handle down while pressing the right mouse button.
• Release the right mouse button, and from the popup menu, select Series.
• In the Series dialog box, select Growth (as Type).
• I entered 2 for Step value, projecting each year’s Unit Sales to double.
• Click OK.



Third Example: Series, Linear (add a specific static number to its previous number)
• Enter your first item of data in the Unit Price column, example $49 in cell C4.
• Select cell C4 and drag its fill handle down while pressing the right mouse button.
• Release the right mouse button, and from the popup menu, select Series.
• In the Series dialog box, select Linear (as Type).
• I entered 3 for Step value, because I expect to raise my Unit Price by $3 every year.
• Click OK.



Final Result !!
Tom’s Tutorials For Excel: Extracting the Last Word in a Cell
When you want to extract the last word in a cell, be it a last name or whatever the word or character is that follows the last space in a string, this formula can do that, as seen in the picture.
I purposely broke the formula into two lines so it will display on this web page for all browsers, but you would enter it as one continuous statement, just as any formula.
=MID(A2,1+FIND("~",SUBSTITUTE(A2," ","~",
LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),255)


Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes
Here’s how you can assign one macro to multiple drawing shapes on your worksheet, and have them all behave uniquely, depending on which shape was clicked.



In the pictured example, four Oval drawing shapes were placed on the worksheet, keeping their default names of “Oval 1”, “Oval 2”, “Oval 3”, and “Oval 4” but changing their captions to “Quarter 1” and so on.. The same macro (posted below) was assigned to each Oval shape. When an Oval is clicked, its color becomes red for easier identification, and the other 3 Oval shapes become yellow, using RGB. Also, depending on the column location of the clicked Oval, the associated range of cells is selected.
Sub OvalColor()
Dim intOval As Integer
With ActiveSheet
For intOval = 1 To 4
.Shapes("Oval " & intOval).Fill.ForeColor.RGB = RGB(255, 255, 0)
Next intOval
With .Shapes(Application.Caller)
.Fill.ForeColor.RGB = RGB(255, 0, 0)
With .TopLeftCell
Range(Cells(5, .Column), Cells(22, .Column + 2)).Select
End With
End With
End With
End Sub

Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates
The YEARFRAC function returns a decimalized number as a fraction of year(s) that represents the count of whole days betweeen the start date (“Hire Date” in the pictured example), and the end date (today’s date in cell F1).
In its simplest form, YEARFRAC takes two primary arguments of start date and end date.
The formula in cell C4 and copied down is =yearfrac(B4,$F$1).

Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative
I previously posted this manual example, and this programmed example for converting formula references from relative to absolute. The manual example showed how the row and / or column references can be kept or omitted. The programmed example showed how to convert all relative row and column references to absolute.
This time, here’s a fast and easy way to convert all absolute references to relative references, by eliminating the “$” character from your formulas.



Tom’s Tutorials For Excel: Shortcuts to Select the Next, Many, or All Sheets
Here are a few shortcut tips for selecting worksheets.
Pressing the Ctrl+PageDown keys will select the next (to the right) visible sheet.


Pressing the Ctrl+PageUp keys will select the previous (to the left) visible sheet.


To select individual sheets as a group, click their tab while pressing the Ctrl key.


All worksheets can be selected as a group by either of two ways:
• Select the first sheet tab, then click the last sheet tab while pressing the Shift key.
or
• Right-click on any sheet tab, and left-click on Select All Sheets.

📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment