Showing posts with label Excel tips. Show all posts

11 August, 2020

Excel Number to Words Formula

Ever wanted to turn a number to words like 123,456 to One hundred twenty-three thousand four hundred fifty-six? You can use this elegant Excel formula to get convert number to words.

Excel Number to Words Formula

Below I have provided number to words Excel formula. It assumes you have input number in cell A1.

Note: This function can convert numbers up to 999,999 into words.

Excel 2020: Quickly Convert Formulas to Values

eama Excel tips ,

Excel 2020: Quickly Convert Formulas to Values

I always say there are five ways to do anything in Excel. Converting live formulas to values is a task that has far more than five ways. But I will bet that I can teach you two ways that are faster than what you are using now.

The goal is to convert the formulas in column D to values.

Cells D2:D14 have a formula. The range is selected.

You are probably using one of the ways shown below.

Here are some ways to paste values: 1. Ctrl+C, open the Paste dropdown, click on the icon for Values. 2. Ctrl+C, Alt+E S V Enter. 3. Ctrl+C, Ctrl+Alt+V, V, Enter. 4. Ctrl+C, Ctrl+V, Press and release Ctrl, then press V. 5. Ctrl+C, Alt, H, V, V. 6. Right-click, choose Copy. Right-click, choose Values from the Paste Options row.

For Those Who Prefer Using the Mouse

If you prefer to use the mouse, nothing is faster than this trick I learned from Dave in Columbus, Indiana. You don’t even have to copy the cells using this technique:

Select the data.
Go to the right edge of the selection box.
Hold down the right mouse button while you drag the box to the right.
Keep holding down the right mouse button while you drag the box back to the original location.
When you release the right mouse button, in the menu that pops up, select Copy Here As Values Only.

How does anyone ever randomly discover right-click, drag right, drag left, let go? It is not something that you would ever accidentally do.

It turns out the menu is called the Alternate Drag-and-Drop menu. You get this menu any time you right-drag a selection somewhere.

When you release the right mouse button, there are 11 choices: Move Here, Copy Here, Copy Here As Values Only, Copy Here as Formats Only, Link Here, Create Hyperlink Here, Shift Down & Copy, Shift Right and Copy, Shift Down and Move, Shift Right and Move, or Cancel.

In this case, you want the values to cover the original formulas, so you have to drag right and then back to the left.

For Those Who Prefer Using Keyboard Shortcuts

I love keyboard shortcuts. I can Ctrl+C, Alt+E, S, V, Enter faster than you can blink. But starting in Excel 2010, there is a faster way. Look at the bottom row of your keyboard. To the left of the Spacebar, you usually have Ctrl, Windows, Alt. To the right of the spacebar is Alt, Something, and Ctrl.

What is that key between the right Alt and the right Ctrl? It has a picture of a mouse pointer and a pop-up menu. Its official name is the Application key. I’ve heard it called the Program key, the Menu key, the Context Menu key, and the Right-Click key. I don’t care what you call it, but here is a picture of it:

A photograph of the Application key on the keyboard. It looks like a mouse pointer over a context menu.

Here is the fastest keyboard shortcut for copying and pasting values. Press Ctrl+C. Press and release the Program/Application/Right-Click key. Press V.

Again, this only works in Excel 2010 or newer.

And, if you have a Lenovo laptop, it is likely that you don’t even have this key. On a keyboard without this key, you can press Shift+F10 instead.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

Excel Tip and Tricks

eama Excel tips ,

Excel Tip and Tricks
#evba #eama #etipfree

How to create area chart with up / down colors?Area chart with positive / negative colors

eama Excel tips ,

How to create area chart with up / down colors?Area chart with positive / negative colors

Ever wanted to make an area chart with up down colors, something like this? Then this tip is for you.

Area chart with two colors for up & down values - Excel charting trick

How to create area chart with up / down colors?

Simple. You need,

Data with positive & negative values.
optional: A cup of coffee or a cold beverage of your choice.

Start by creating a regular area chart. You will get this:

Now, select the are and go to format settings (use shortcut CTRL+1).

Go to fill color for the area and change it to gradient fill.

Set it to one of the default gradient fills, you will see a screen like this.

The last step: Setting two color gradient

We are nearly there. Take a victory sip out of that coffee cup or cold beverage. First calculate the mid point for our gradient stops. This will be =maximum up value / (maximum up value + maximum absolute down value)

For example, if your data has +5 has maximum positive value and -5 as maximum negative value, then the gradient stop will be 50% since 5 divided by (5+5) is 50%.

Take another sip and set the gradient stops as shown below.

Create 4 gradient stops (most of the default gradient settings have 4 stops)
Set stop 1 & 2 to positive color (say blue)
Stop 3 & 4 to negative color (say red)
Adjust the position of 2 & 3 to the gradient stop calculation you have done earlier.
Make sure Gradient is linear with 90 degrees

gradient stop settings for area chart with up down colors

Done. Your area chart with positive / negative colors is ready. Admire its beauty while finishing your beverage.

Watch video tutorial: Area chart with positive / negative colors

If you are not sure about the whole gradient color trick, check out this video. I explain clearly the idea with few examples (plus there is a bonus trick in there).

Download Example File

Please click here to download a sample file with up down colors for area charts. Play with the data, examine the chart settings to understand this better.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

Excel 2020: Discover New Functions by Using fx

eama Excel tips ,

Excel 2020: Discover New Functions by Using fx

There are 400+ functions in Excel. Instead of taking 450 pages to describe every function, I am going to teach you how to find the function that you need.

The Excel 2007 formulas tab introduced a huge fx Insert Function icon. But you don’t need to use the one on the Formulas tab; the same icon has been to the left of the formula bar ever since I can remember.

A simple model to calculate a car payment. The principal of 25995 is in B1. The Term of 60 months in B2. The interest rate of 5.25% in B3. The payment cell of B5 is selected. You can either choose Insert Function on the Formulas tab, or click the small script fx to the left of the formula bar.

If you are trying to figure out how to calculate a loan payment, the Insert Function dialog will help. Click the icon next to the formula bar. In the Search for a Function box, type what you are trying to do. Click Go. The Select a Function box shows functions related to your search term. Click on a function in that box to see the description at the bottom of the dialog.

In the Insert Functions dialog, use the search box and type Loan Payments. Click Go. Choices such as PMT, PV, IPMT, PPMT, RATE will appear. Click on any search result and a description of the function appears at the bottom of the dialog. Currently PMT is selected and it will Calculates the payment for a loan based on constant payments and a constant interest rate.

When you find the correct function and click OK, Excel takes you into the Function Arguments dialog. This is an amazing tool when you are new to a function. As you click in each argument box, help appears at the bottom of the window, with specifics on that argument.

The Function Arguments dialog for PMT has boxes for the five arguments: Rate, NPER, PV, FV, and Type. The labels for the first three arguments are bold, indicating they are required. As you click into the rate box, help at the bottom gives an example of 6%/4 for quarterly payments at 6% interest.

Personally, I could never get the PMT function to work correctly because I always forgot that the rate had to be the interest rate per period. Instead of pointing to the 5.25% in B3, you have to point to B3/12. Below, the help for Nper explains that it is the total number of payments for the loan, also known as the term, from B2.

With the cursor in Rate, click on B3 and then type /12. Tab to the Nper box, and the help will show Nper is the total number of payments for the loan. Click on B2. Also helpful: to the right of each box is the intermediate result. For example, B3/12 is 0.004375.

PV is the loan amount. Since I never write a check for negative $493, I want the answer from PMT to be positive instead of negative. That is why I always use –B1 for the PV argument. If you use B1 instead, you will get the correct $493.54065 answer, but it will appear as negative in your worksheet. Think of the original $25,995 as money leaving the bank; that is why the PV is negative.

For the present value argument, type a minus sign and then click on B1. At this point, the three required arguments are entered, so the dialog box will show you the calculated answer of $493.

Notice in the above figure that three argument names are bold. These are the required arguments.
Once you finish the required arguments, the Function Arguments dialog shows you the answer in two places. I always use this as a sanity check. Does this answer sound like a typical car payment?

This one topic really covered three things: how to calculate a loan payment, how to use the fx icon to discover new functions, and how to use the Function Arguments dialog to get help on any function. If you are in a situation where you remember the function name but still want to use the Function Arguments dialog, type =PMT( with the opening parenthesis and then press Ctrl+A.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

List of top 10 Excel formulas for people in IT

eama Excel tips ,

List of top 10 Excel formulas for people in IT

Are you AND(“In IT”, “Use Excel”)? Then this article is for you. Here is my list of top 10 Excel formulas for people in IT. As software miners use Excel differently than other folks, this article highlights important use cases for you. These examples will particularly help project managers, IT analysts, testing people and business analysts.

Don’t forget to download the example workbook.

Video - Top 10 Excel formulas for IT people

Top 10 reasons to learn Excel formulas

eama Excel tips ,

Top 10 reasons to learn Excel formulas

If you've ever wondered whether learning Excel formulas is worth your time, this list is for you!

Formulas are the heart of spreadsheets, so your skill with them can help you in many ways. Read below for 10 reasons you should take time to increase your skill with formulas.

1. Formulas are important in many jobs. In a recent survey we conducted about formulas, over 90% of respondents said that Excel formulas were "important", "very important", or "vital" to their job.

2. Formulas are a powerful way to capture and save a working solution (examples). They let you repeat the solution again whenever you like, always with perfect accuracy. They are far better than your faulty memory.

3. Formulas help you translate your vision into reality. Ever felt like you can explain what you want to do in plain English, but you have no idea how to do it in Excel? Formulas let you build the analysis that's in your head.

4. Your understanding of formulas helps you you design better spreadsheets. You can organize data in a way that takes advantage of formulas, which drastically reduces errors and troubleshooting. (To be fair, Pivot Tables also help you do the same thing.)

5. Hardly anyone is really good with Excel formulas, so being good with formulas is a chance to differentiate yourself by providing more value and productivity. And all businesses love productivity.

Resources: Function guide | 500 formula examples | time-saving tips

6. Being good with formulas allows you to build elegant solutions that avoid complexity. Nothing in Excel is more dangerous that unnecessary complexity — it makes it impossible to understand if a spreadsheet is really working correctly.

7. Because the business world relies on spreadsheets to make decisions, being good with formulas keeps you close to the action.

8. Formulas help you quickly visualize data. When you combine formulas with conditional formatting, you can instantly "see" important trends, insights, and relationships.

9. Skill with formulas allows you to deploy a full set of razor-sharp tools when solving problems. Without formula skills, you are forced to use a dull saw on every job, no matter how large or tedious.

10. Formulas let you get home on time. When you understand how to use formulas, you can do your work faster and spend more time with friends and family. Priceless.

Did I miss something important? Add your thoughts below.

Author

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

How to set a default template in Excel

eama Excel tips ,

How to set a default template in Excel

Do you find yourself creating new workbooks in Excel, then making the same changes to every one? Maybe you like to change font size, zoom percent, or the default row height?

If so, you can save yourself time and trouble by setting a default template for Excel to use each time you create a new workbook. As long as you name the template correctly, and put it in the correct location, Excel will use your custom template to create all new workbooks.

Note: biggest challenge with this tip is figuring out the right location for the template file. This can be maddeningly complex, depending on which platform and version of Excel you use. If you get frustrated and can't make things work, you can set your own startup folder manually, as described below.

Settings that can be saved in a template

A template can hold many custom options. Here are a few examples of settings that can saved in a workbook template:

Font formatting and styles
Display options and zoom settings
Page setup and print options
Column widths and row heights
Page formats and print area settings for each sheet
The number (and type) of sheets in new workbooks
Placeholder text (titles, column headers, etc.)
Data validation settings
Macros, hyperlinks and ActiveX controls
Workbook calculation options

Remember: these setting only apply to new workbooks created after a custom template file is installed.

The process

Open a new blank workbook and customize the options as you like
Save the workbook as an Excel template with the name "book" (Excel will add .xltx) **
Move the template to the startup folder used by Excel
Quit and relaunch Excel to be sure settings are fresh
Test to be sure Excel is using the template when new workbooks are created

** Based on comments below, it seems the name of your workbook must be localized for your version of Excel. For example, if you're using the Czech version, you need to to use "Sešit" instead of "book".

Common startup folder locations

Whenever Excel is launched, it establishes what is called a "startup folder", which is named XLSTART. The key is to put your template file into this folder so that Excel will find it. Unfortunately, the exact location of XLSTART varies according the versions of Excel and Windows you use. Here are some common locations:

C:\Program Files\Microsoft Office\OFFICEx\XLSTART
C:\Users\user\AppData\Microsoft\Excel\XLSTART
C:\Users\user\AppData\Roaming\Microsoft\Excel\XLSTART

Can't find XLSTART?

If you can't find the startup folder for excel (XLSTART), you can use the VBA editor to confirm Excel's start up path:

Run Excel
Open the VBA editor (Alt + F11)
Open the immediate window (Control + G)
Type: ? application.StartupPath in the window
Press Enter

The startup path will appear below the command. Once you've confirmed the location of XLSTART, drop in your template file.

Use the VBA immediate window to confirm startup path

Replace ugly IFs with MAX or MIN

eama Excel tips ,

Replace ugly IFs with MAX or MIN

In this article, I want to show you how you can sometimes replace a more complicated IF formula with a more elegant MIN or MAX formula.

This is a very simple tip that really demonstrates how you can leverage Excel's formulas to create clever and compact solutions to everyday problems.

To illustrate, let's look at two examples.

A free lunch with MAX

Let's say you have a $50 credit at a restaurant. It's a one-time use credit that expires tomorrow, so you take your friend to dinner today. You split a salad, a pizza, and a couple of beers. When it comes time to apply the credit to the bill, you might calculate the balance like this:


balance= total-credit

Simple formula. But what happens when the credit is greater than the total?

If that happens, you'll see a negative balance:

CONCAT & TEXTJOIN

eama Excel tips ,

CONCAT & TEXTJOIN

I've been playing around with the TEXTJOIN and CONCAT functions this week. These are both new functions in Excel 2016, introduced in the Office 365 subscription service.

Both of these functions let you join (concatenate) text in different cells together. TEXTJOIN lets you join values with a delimiter of your choice, and has an option to ignore empty values. CONCAT simply mashes all values together without options.

What's nice about both of these functions is that they can handle cell ranges.

That means you can do things like this:


=TEXTJOIN(" ",TRUE,B4:H4) // J4
=CONCAT(B7:H7) // J7

Maybe you're old enough to recognize that number? :)

The ability to handle ranges is cool, because it makes it trivial to concatenate a large collection of cells with a simple formula - something that required annoying workarounds previously.

But I'm also intrigued about how this might be useful *inside* other formulas. In most programming languages, its common to split values into arrays, and join values back together again, after some kind of processing. For example, VBA has SPLIT and JOIN, and PHP has EXPLODE and IMPLODE, etc.

Inside an Excel formula, it's not too hard to split values into an array:


=MID("apple",{1,2,3,4,5},1) // returns {"a","p","p","l","e"}

But once you have {"a","p","p","l","e"}, how you can you put it back together again?

Turns out, CONCAT and TEXTJOIN will let you do it, which solves a problem that's bugged me for a long time:


=CONCAT({"a","p","p","l","e"}) // returns "apple"
=TEXTJOIN("",TRUE,{"a","p","p","l","e"}) // returns "apple"

Why does it matter?

To be honest, I'm not entirely how useful this is, since I've just started fiddling around with these functions. However, I think this might open the door to some interesting formulas that process values by looping through looping arrays. Here are few ideas you might find interesting:

1. Uppercase text


=TEXTJOIN("",TRUE,(CHAR(CODE(MID(A1,{1,2,3,4,5},1))-32)))

The example above will uppercase "apple" > "APPLE" in A1. It's a silly example, since you can do the same thing more easily with the UPPER function. But I think it shows nicely how you can loop through each character, make changes, then bring it all back together again with TEXTJOIN.

2. Strip non-numeric characters

Stripping non-numeric characters with the TEXTJOIN function


{=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,""))}

Note: this is an array function - use control + shift + enter.

This example will strip all non-numeric characters in A1. So, for example, you can take a phone number like "(801)-654-4466" and turn it into "8016544466", which can then be formatted using a custom number format. You can do this same thing with the SUBSTITUTE function, but it's more work. If your curious, here is a detailed explanation of how this formula works.

11 August, 2020

Excel Number to Words Formula

Excel 2020: Quickly Convert Formulas to Values

For Those Who Prefer Using the Mouse

For Those Who Prefer Using Keyboard Shortcuts

08 August, 2020

03 August, 2020

How to create area chart with up / down colors?Area chart with positive / negative colors

How to create area chart with up / down colors?

The last step: Setting two color gradient

Watch video tutorial: Area chart with positive / negative colors

Download Example File

01 August, 2020

Excel 2020: Discover New Functions by Using fx

23 July, 2020

Video - Top 10 Excel formulas for IT people

19 July, 2020

Top 10 reasons to learn Excel formulas

15 July, 2020

How to set a default template in Excel

Settings that can be saved in a template

The process

Common startup folder locations

Can't find XLSTART?

12 July, 2020

Replace ugly IFs with MAX or MIN

A free lunch with MAX

10 July, 2020

Why does it matter?

Popular Posts

Best ebooks today

Labels

Facebook

Total Pageviews

Search This Blog

Translate

↓ Main

Popular Posts

Recent

Now

Featured

Public

Blog Archive

Popular Posts

Recent Post

Subscribe

Tags

About me

Recent Comments