Ebooks Amazon Free

FREE ALL EBOOKS ON AMAZON AND COURSES ON UDEMY- What books and courses do you need? We have them FREE for you...

Showing posts with label Atlas Excel. Show all posts
Showing posts with label Atlas Excel. Show all posts

30 January, 2020

Linking a Drawing Object to a Cell

Linking a Drawing Object to a Cell
Here’s how you can link a cell’s value to be displayed inside a drawing object. In this example, a Text Box is being used, but this method will also work for Rectangles and other draw-able Forms shapes.
In Excel version 2003, find the Text Box icon on the Drawing toolbar by clicking View > Toolbars > Drawing to show that toolbar, then click onto the Text Box icon as shown.
For Excel versions 2007 or after, click onto the Insert tab, click the Shapes button, and click onto the Text Box icon as shown.
As you see in the following pictures, draw the shape onto your worksheet, and select it. In the formula bar, enter a formula such as =A1, or whichever cell whose value you want the shape to show.
Whether the linked cell (A1 in this example) is meant for manual data entry, or it holds a formula, the shape will also show that cell’s contents.
#evba #etipfree #kingexcel
📤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

28 January, 2020

"Did You Know…”

"Did You Know…”
Did you know:
You can insert a new worksheet by pressing Shift+F11. Immediately thereafter, to continue inserting as many new worksheets as you want, press the F4 key.
Did you know:
You can enter any value in a cell, then select next cell below it, and reproduce that value by pressing Ctrl+' (Ctrl + apostrophe). Note that the value itself is reproduced but not the formatting.
Did you know:
Did you delete a sheet by mistake? You can restore it by immediately closing the workbook without saving it. When you reopen the workbook, your sheet will be restored.
Did you know:
Editing shortcuts on your keyboard…
• Copy – Ctrl+C
• Cut – Ctrl+X
• Paste with option to repeat – Ctrl+V
• Paste without option to repeat – Enter
#evba #etipfree #kingexcel
📤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

27 January, 2020

Forcing Data Entry to Include a Specified Character

Forcing Data Entry to Include a Specified Character
Data Validation is an excellent way to control data entry to meet a certain condition.
In this example, suppose you want to insure that a specific character is included in a text entry for a range of cells. In the next picture, you want to enter email addresses so you want to make sure the “@” character is included. The following steps show how this can be done.
Step 1
Select the range of interest, and from your keyboard press Alt+D+L to show the Data Validation dialog box.
Step 2
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.
Step 3
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =ISNUMBER(FIND("@",B2))
Step 4
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.
#evba #etipfree #kingexcel
📤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

26 January, 2020

Entering an Ampersand in Your Header or Footer

Entering an Ampersand in Your Header or Footer
If you’ve ever wondered why a custom header or footer does not show an ampersand (&) when you want to show it, there’s a small trick to making that happen. All it takes is two ampersands in succession, in order to show a single one.
For example, suppose you want a custom footer to be This & That. When setting up your custom footer, you need to actually enter This && That.
Here are the steps.
Step 1
Step 2
In the Page Setup dialog box:
• Click onto the Header/Footer tab.
• Click the Custom Header or Custom Footer button. In this example I clicked Custom Footer.
Step 3
In the Footer dialog box, in the Center Section field, I entered This && That. Notice the two ampersand characters in immediate succession. Then, click the OK button.
Step 4
You will return to the Page Setup dialog to confirm the look of your custom footer text. I circled in red what it looks like in this example. Simply click the OK button to confirm your custom footer text.
#evba #etipfree #kingexcel
📤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

24 January, 2020

Formatting Large Numbers as Decimalized Gigabytes

Formatting Large Numbers as Decimalized Gigabytes
When you work with numbers so large that Excel puts them in Scientific Notation format, you might want to format those numbers for a more meaningful look.
For example, in the Before and After comparison pictures, the large numbers represent the size of storage space kilobytes on computer hard drives. You’d like to see those numbers in a format that’s easy to comprehend, such as in gigabytes with one decimal and a “GB” suffix.
To make this happen, first, select the range of numbers, and right click the selection. From the popup menu, click to select Format Cells.
Next, in the Format Cells dialog box:
• Click onto the Number tab.
• From the Category list, select Custom.
• In the Type field, enter #,##0.0,,,"GB"
• Click the OK button.
#evba #etipfree #kingexcel
📤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

Calculating Dates for Last Day of Current, Previous, and Future Months

Calculating Dates for Last Day of Current, Previous, and Future Months
As pictured below, here are formulas that return the date for the last day of…
• Current month: =DATE(YEAR(NOW()),MONTH(NOW())+1,0)
• Last month: =DATE(YEAR(NOW()),MONTH(NOW()), 0)
• Next month: =DATE(YEAR(NOW()),MONTH(NOW())+2,0)
#evba #etipfree #kingexcel
📤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

23 January, 2020

Finding the First and Last Days of the Week and Month

Finding the First and Last Days of the Week and Month
Here are formulas to return various dates of first and last days of a given month.
First day’s date of that month: =DATE(YEAR(B1),MONTH(B1),1)
Last day’s date of that month: =DATE(YEAR(B1),MONTH(B1)+1,0)
First Monday date of that month:
=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))
Last Friday date of that month:
=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)
First day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+3,1)
Last day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+4,0)
#evba #etipfree #kingexcel
📤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

Converting an Elapsed Time to a Decimal Number

Converting an Elapsed Time to a Decimal Number
Some employers pay their employees based on work time that is measured in decimals as portions of an hour. For example, if an employee works a 7-hour and 45-minute day, the employer pays that person for 7.75 hours of work time.
Here is how to represent elapsed work times in decimalized fashion.
Step 1
• Enter and copy as needed the numeric formula, example =HOUR(C3-B3)+(MINUTE(C3-B3)/60).
• Select the formula range and right-click that selection.
• From the right-click popup menu select Format Cells.
SelectRange_Formula_RightClick_FormatCell
Step 2
In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list, select Number.
• In the Decimal Places field, select your desired setting. I selected 2 decimal places.
• In the Negative Numbers field, I selected the black font style -1234.10.
• Confirm your format selections by clicking the OK button.
#evba #etipfree #kingexcel
📤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

22 January, 2020

Entering a Function’s Arguments Tooltip in a Cell

Entering a Function’s Arguments Tooltip in a Cell
You can press Ctrl+Shift+A in mid-entry of your worksheet functions to show their tooltips’ argument text directly in your cell, and type your function arguments right over those text tips.
Step 1
Start by entering the function name, followed by the open parentheses character.
The function’s arguments tooltip appears as usual.
Step 2
Press Ctrl+Shift+A.
Step 3
The function’s arguments tooltip text now appears in the cell. The tooltip itself is still visible.
Step 4
I dragged the tooltip away from the cell in this example.
Notice I continued to enter my VLOOKUP function arguments in the cell, directly over the tip text.
#evba #etipfree #kingexcel
📤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

20 January, 2020

Copying Formulas While Keeping Their Relative and Absolute References

Copying Formulas While Keeping Their Relative and Absolute References
Here’s how you can copy a set of formulas and paste them elsewhere, while keeping the original formulas unaffected and keeping the relative and absolute references unchanged.
Before the copy and paste.
After the copy and paste. Notice the same formula in cell G2 as in cell B7.
Select the range and press Ctrl+H to show the Find and Replace dialog box.
In the Find what field, enter the = character.
In the Replace with field enter some unused character, for example the # character.
Select the formula cells, right-click the selection and click Copy.
Right-click the upper right corner destination cell, and click Paste.
Select the entire used range and press Ctrl+H again to show the Find and Replace dialog box.
In the Find what field, enter the # character to reinstate the formulas.
In the Replace with field enter re-enter the = character.
#evba #etipfree #kingexcel
📤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

Reverse Lookup of nth Highest and nth Lowest Numbers

Reverse Lookup of nth Highest and nth Lowest Numbers
Here are several examples rolled into one screen shot that show how to:
• Return the minimum and maximum numbers in a list.
• Return the 2nd, 3rd, and nth highest and lowest numbers in a list.
• Lookup in reverse (to the left) of the aforementioned numbers in a table.
The formula in cell G2 is =INDEX(A2:A27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell H2 is =INDEX(B2:B27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell I2 is =INDEX(C2:C27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell J2 is =MIN(D2:D27)
The formula in cell G3 is =INDEX(A2:A27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell H3 is =INDEX(B2:B27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell I3 is =INDEX(C2:C27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell J3 is =SMALL(D2:D27,2)
The formula in cell G4 is =INDEX(A2:A27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell H4 is =INDEX(B2:B27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell I4 is =INDEX(C2:C27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell J4 is =SMALL(D2:D27,3)
The formula in cell G7 is =INDEX(A2:A27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell H7 is =INDEX(B2:B27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell I7 is =INDEX(C2:C27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell J7 is =MAX(D2:D27)
The formula in cell G8 is =INDEX(A2:A27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell H8 is =INDEX(B2:B27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell I8 is =INDEX(C2:C27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell J8 is =LARGE(D2:D27,2)
The formula in cell G9 is =INDEX(A2:A27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell H9 is =INDEX(B2:B27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell I9 is =INDEX(C2:C27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell J9 is =LARGE(D2:D27,3)
#evba #etipfree #kingexcel
📤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

Doing a Lookup for Last Number or Last Text in a List

Doing a Lookup for Last Number or Last Text in a List
Here is how you can look up items in one column, based on the last cell in a different column of that list which contains a number or text.
The formula in cell E2 is =INDEX(A3:C17,MATCH(9.99999999999999E+307,A3:A17,1),3).
It returns “Black Onyx” because that is what is in cell C15, corresponding to the fact that cell A15 contains the last number (a time, but still a number) in column A of that list.
The formula in cell F2 is =INDEX(A3:D14,MATCH(REPT("z",255),A3:A14,1),4).
It returns “4:04” because that is what is in cell D14, corresponding to the fact that cell A14 contains the last text item in column A.
#evba #etipfree #kingexcel
📤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

Going To the Precedent Cell with a Keyboard Shortcut

Going To the Precedent Cell with a Keyboard Shortcut
Here’s a cool tip for the keyboard shortcut Ctrl+[ which takes you from the formula cell you are on, to the precedent cell (if there is one) of that formula.
In this first example, the active cell is C5 which displays what is in precedent cell G2.
Pressing Ctrl+[ takes you to cell G2.


It also works for going to a precedent cell on a different worksheet.
In this next example, active cell E3 on Sheet1 displays what is in cell A4 of Sheet3.
Pressing Ctrl+[ takes you to cell A4 of Sheet3.


This also works for going to the precedent cell of a different workbook. In this final example, active cell B3 on Sheet2 of Book1 displays what is in cell H6 on Sheet1 of Book2.
Pressing Ctrl+[ takes you to Book2 (which does not need to be open at the time — Excel will open it for you), Sheet1, cell H6.
#evba #etipfree #kingexcel
📤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

19 January, 2020

Showing Your Column Headers as Letters or Numbers

Conditional Formatting examples for repeated items in a list.

Conditional Formatting examples for repeated items in a list.
The Conditional formatting rule for unique entries in range A3:A14 is
=COUNTIF($A$3:$A$14,A3)=1
The Conditional formatting rule for non-unique entries in range B3:B14 is
=COUNTIF($B$3:$B$14,B3)>1
The Conditional formatting rule for repeated entries in range C3:C14 is
=COUNTIF($C$3:C3,C3)>1
#evba #etipfree #kingexcel
📤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

15 January, 2020

Pasting a Formulas Static Value in Cell Below

Pasting a Formulas Static Value in Cell Below
You probably know that if you select a cell below a cell that contains a value or formula, when you press Ctrl+' (the Ctrl and apostrophe keys), you can replicate that value or formula.
If the cell of interest contains a formula, and you only want to replicate that cell’s value but not its formula, you can press Ctrl+Shift+' (the Ctrl and Shift and apostrophe keys).
The following pictures illustrate this functionality.




#evba #etipfree #kingexcel
📤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