Showing posts with label Excel tips. Show all posts

14 December, 2020

VBA Case Study – #1 Sheet Saver and Sender Macro (18 steps in 10 seconds)

eama Code VBA , Excel tips

VBA Case Study – #1 Sheet Saver and Sender Macro (18 steps in 10 seconds)

Last updated on February 8, 2019

By EVBA.info

Summary: Why invest time learning VBA if it doesn’t make your life easier?

When I encounter a boring and repetitive task at work, I figure out how to get rid of it before it drives me insane.

I can either try – and fail – to convince my boss that the task is pointless, or I can impress my boss by writing a macro that will do the work for me. Guess what I do?

In this case study I explain how I used Excel VBA macros to do a repetitive 18 step task in just 10 seconds.

9 Ways To Add Leading Zeros In Excel Format As Text

eama Excel tips ,

9 Ways To Add Leading Zeros In Excel

Format As Text

There is an option to change the format of a range to text.

Doing this will treat any data you enter as text values, even if they are numbers. This will cause Excel to keep any leading zeros in your numbers.

Change-Format-to-Text 9 Ways To Add Leading Zeros In Excel

Change the format from General to Text.

30 Excel shortcut keys that make Excel user friendly

eama Excel tips ,

30 Excel shortcut keys that make Excel user friendly

If you are managing some big data, then Excel shortcuts are used a lot to easily change the data. In this article, we are going to learn about shortcuts on a similar simple that can easily jump, copy, paste and select data.

30 Important Excel Shortcut Keys

3 ways to calculate the correlation coefficient in Excel

eama Excel tips ,

3 ways to calculate the correlation coefficient in Excel

If you’ve ever learnt some statistics, then you’ve probably come across the correlation coefficient.

But can you calculate this in Excel?

Yes, you can!

Excel can be a great tool for a statistician when you know how to use it.

In this post, I’ll show you 3 ways to calculate the correlation coefficient in Excel.

EVBA.info

What Is A Correlation Coeffecient?

The correlation coefficient is also known as the Pearson Correlation Coefficient and it is a measurement of how related two variables are.

The calculation can have a value between 0 and 1.

A value of 0 indicates the two variables are highly unrelated and a value of 1 indicates they are highly related.

Height-and-Weight-Data 3 Ways to Calculate a Pearson's Correlation Coefficient in Excel

For example, you might have data on height (meters) and weight (kilograms) for a sample of people and want to know if these two variables are related.

Replicate Excel’s TRIM Function In Power Query

eama Excel tips ,

Replicate Excel’s TRIM Function In Power Query

In this post we’re going to learn how to build a custom query function in power query that will replicate Excel’s TRIM function!

If you find you’re always doing the same transformation or calculation, why not turn it into a function? This can simplify the steps in your query and allow you to re-use a solution.

Apply-Query-Function Replicate Excel's TRIM Function In Power Query

6 New Functions In Excel 2019 And Office 365 You Must Learn

eama Excel tips ,

6 New Functions In Excel 2019 And Office 365 You Must Learn

Microsoft Excel is a Powerful Tool, Period.

And once you are loaded with all the basics of Microsoft Excel, it’s important for you to master the functions.

You must have heard a number of times that the power of Excel lies in its inbuilt functions and flexibility to create formulas as per your need and requirement.

One thing which I love the most about Microsoft TEAM that they keep on introducing new functions from time to time.

For Example: In the latest version of Excel i.e 2019/Office 365, they have introduced 6 New functions which can change the way you deal with the data.

Today in this post, I’d love to share those “6 New Excel Functions Introduced in Excel 2019/365“.

So let’s explore them one by one.

EVBA.INFO

Video Tutorial

Free Stripe analytics and KPI's like you've never seen in Excel before

eama Excel tips ,

GIVE YOUR STRIPE PAYMENTS DATA SUPER POWERS!

Free Stripe analytics and KPI's like you've never seen in Excel before.DOWNLOAD

Dashboard

Monthly Recurring Revenue (MRR)

Stripe Analytics Monthly Recurring Revenue Chart

Net Revenue

Active Customers

And More...

Stripe Analytics For Subscription & SaaS Businesses

Gain deeper insight into your MRR, Net Revenue, Fees, Average Revenue Per Customer and Number of Active Customers from Stripe.

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

5 ways you can easily convert your Excel file into a PDF

eama Excel tips ,

5 ways you can easily convert your Excel file into a PDF

Excel and PDF are two very popular file types.

There’s a good chance you’ve probably needed to convert your Excel file into a PDF.

In this post, I’ll show you 5 ways you can easily convert your Excel file into a PDF.

Video Tutorial

Excel Number to Words Formula

eama Excel tips ,

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

14 December, 2020

VBA Case Study – #1 Sheet Saver and Sender Macro (18 steps in 10 seconds)

09 December, 2020

9 Ways To Add Leading Zeros In Excel

Format As Text

13 November, 2020

03 November, 2020

What Is A Correlation Coeffecient?

04 October, 2020

Replicate Excel’s TRIM Function In Power Query

28 September, 2020

6 New Functions In Excel 2019 And Office 365 You Must Learn

Video Tutorial

27 September, 2020

GIVE YOUR STRIPE PAYMENTS DATA SUPER POWERS!

Dashboard

Monthly Recurring Revenue (MRR)

Net Revenue

Active Customers

And More...

Stripe Analytics For Subscription & SaaS Businesses

21 September, 2020

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

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