Microsoft has introduced more features in Excel 2013, which are new to users. Some of them are Flash fill, timeline feature, Lookup function, Statistical Function and more. These features allow the user to work with data more effectively and efficiently.

Enhance your career prospects with Simplilearn's in-depth Excel courses. Acquire the expertise to handle complex data and make informed decisions!

Let's discuss those MS excel tips and tricks one by one.

1. Fill your entry quickly via Flash Fill

Excel 2013 has come up with a special feature, Flash Fill, which can automatically fill the columns/rows by detecting the pattern followed in excel.

Flash fill in excel 2013 Example:

Let’s try to enter the Column “Name” as First Name + Last Name in a second column.

One way is to manually type the name of each person, while the simpler alternative is to use Flash fill. Flash Fill will automatically detect the pattern and apply this to the rest of the column. Press enter and have those entries in seconds.

2. Calculate Date and Days to a second in Excel 2013

Calculating dates in excel is really easy now with help of these two functions,

A. DAYS Function

This will determine the number of days between the indicated two dates.

Format: DAYS (start_date, end_date)

Example:

Enter the dates and apply formula over the column where you wish to see the outcome.

Start date and end date: Must be entered. These are the two dates between which you want to know the number of days.

Start date and days

The following example illustrates how we can effectively use this functionality for each column of a row.

B. ISOWEEKNUM Function

Format: ISOWEEKNUM (date)

Returns the number of weeks in the specific year that the mentioned date occurs.

Example: In this example, the function returns the number of weeks (14) in the specific year (2015) up to the date that’s been provided (4/1/2015).

3. Excel’s 2013 Look-up Function

FORMULATEXT

Returns a formula as a text string.

Format: FORMULATEXT (reference)

Feature: where the reference argument can be a reference to a cell in an Excel worksheet or on a different worksheet or even another workbook that is currently open.

Example: In the below example, we have listed out the expense amount, and in (A7), we apply the “SUM” formula and the formula is then used as follows:

4. Excel 2013 makes statistical functions easy

A. BINOM.DIST.RANGE function

Returns the probability of a trial result using a binomial distribution.

Format: BINOM.DIST.RANGE (trials, probability_s, number_s, [number_s2])

  • Trials: The number of independent trials. Must be greater than or equal to 0
  • Probability_s: The probability of success in each trial. Must be greater than or equal to 0 and less than or equal to 1.
  • Number_s: The number of successes in trials. Must be greater than or equal to 0 and less than or equal to Trials.
  • Number_s2: Optional: If provided, returns the probability that the number of successful trials will fall between Number_s and number_s2. Must be greater than or equal to Number_s and less than or equal to Trials.

Equation Used:

N is Trials, p is Probability_s, s is Number_s, s2 is Number_s2, and k is the iteration variable.

Note:

  • If any arguments are outside of their constraints, BINOM.DIST.RANGE returns the #NUM! Error value.
  • If any arguments are non-numeric values, BINOM.DIST.RANGE returns the #VALUE! Error value.
  • Numeric arguments are truncated to integers.

Example:

Enter the trial, probability and number of success.

The above example will return the binomial distribution based on the probability of 68 successes in 90 trials and a 75% probability of success. Apply the formula in column C1.

B. PERMUTATION A

Determines the total number of possible permutations for an indicated number.

Syntax: PERMUTATIONA (number, number-chosen)

Where Number: An integer that describes the total number of objects.

Number Chosen: Describes the number of objects in each permutation.

Example:

In this example, let’s find permutation (3, 2), where 3 is the total number of objects and 2 describes the number of objects in each permutation.

Suppose the 3 objects are (2, 3, 4); using permutation A with 2. The different permutations are:

  1. 2,2
  2. 2,3
  3. 2,4
  4. 3,2
  5. 3,3
  6. 3,4
  7. 4,2
  8. 4,3
  9. 4,4

Hence, the result is 9.

5. Efficient use of Financial function in excel 2013

PDURATION Function

Determines the number of periods required for an investment to reach a specified future value.

Format: PDURATION (rate, pv, fv)

Where Rate: Per period rate interest

Pv: Present value of investment

Fv: the required future value of the investment

Example:

Let’s list out the rate, present value (pv) and future value (fv) in the excel sheet.

Apply formula in column C.

Or just enter the numbers like below in the formula.

Result: It would take 32.99 years.

RRI Function in excel 2013:

Returns the equivalent interest rate.

Format: RRI (nper, pv, fv)

Where, nper: Number of periods for the investment

Pv: Present value of the investment

Fv: Future value of the investment

Example:

Result: the interest rate will be 1.6%.

Simplilearn provides you a comprehensive introduction to the basics of Excel 2013 application and customizing excel options. It will give an introduction to creating and managing worksheets and workbooks. Demos are provided to show how to work with Excel application and get hands-on experience in Excel 2013.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Post Graduate Program in Data Analytics

Cohort Starts: 11 Oct, 2024

8 months$ 3,500
Applied AI & Data Science

Cohort Starts: 15 Oct, 2024

14 weeks$ 2,624
Caltech Post Graduate Program in Data Science

Cohort Starts: 21 Oct, 2024

11 Months$ 4,500
Professional Certificate Program in Data Engineering

Cohort Starts: 21 Oct, 2024

32 weeks$ 3,850
Professional Certificate in Data Analytics and Generative AI

Cohort Starts: 24 Oct, 2024

5 months$ 4,500
Post Graduate Program in Data Science

Cohort Starts: 28 Oct, 2024

11 months$ 3,800
Data Scientist11 months$ 1,449
Data Analyst11 months$ 1,449

Get Free Certifications with free video courses

  • Introduction to Data Analytics Course

    Data Science & Business Analytics

    Introduction to Data Analytics Course

    3 hours4.6277.5K learners
  • Business Analysis Basics

    Business and Leadership

    Business Analysis Basics

    2 hours4.674.5K learners
prevNext

Learn from Industry Experts with free Masterclasses

  • Path to $100k+ Salary Career in Data Analytics: Build & Grow Your Career

    Data Science & Business Analytics

    Path to $100k+ Salary Career in Data Analytics: Build & Grow Your Career

    16th Oct, Wednesday6:30 PM IST
  • How Can You Master the Art of Data Analysis: Uncover the Path to Career Advancement

    Data Science & Business Analytics

    How Can You Master the Art of Data Analysis: Uncover the Path to Career Advancement

    4th Aug, Friday9:00 PM IST
  • Develop Your Career in Data Analytics with Purdue University Professional Certificate

    Data Science & Business Analytics

    Develop Your Career in Data Analytics with Purdue University Professional Certificate

    30th Mar, Thursday9:00 PM IST
prevNext