Search
04
Aug

How to Separate First Name & Last Name from Full Name in Excel

You will often receive spreadsheets with usernames. Sometimes these spreadsheets have the first name and the second name in different columns. However, spreadsheets usually contain the Full name in a single column. So what do you do then? Simply follow the options mentioned below to split the user name as first name and last name into two columns.

Get First Name And Last Name Through The GUI

Level of difficulty: LOW

Excel’s Convert Text to Column

Excel’s parsing concept helps you to split and spread text from one column to multiple columns. We can parse full name into first name and last name using Excel’s text to columns wizard. Follow the steps mentioned below to understand how Text to Columns works:

Step 1: Select the column that contains the full name. Instead of selecting an entire column you can also select a range but make sure that you are not selecting text from multiple columns. Additionally, ensure that there are empty columns to the right of the selected range or column. This is an important step as text on adjacent columns will be overwritten if there are no empty columns.


Step 2: On your Excel ribbon, select Data and then click the option “Text to Columns”. You will now see the Convert Text to Columns Wizard.


Step 3: The first step in this wizard is to select the data type. You have two options. First is the Delimited and the second is Fixed Width. Select Delimited if your data is separated by characters such as commas,  tabs, any other special characters or symbols. Select the option Fixed width if the data in your column is separated by fixed-width spaces.


Step 4: If you have chosen Delimited, then you would be able to choose the delimiters on the next screen. Available options are as follows:

1) Tab

2) Semicolon

3) Comma

4) Space

5) Other

If you select option 5 ‘Other’ it would allow you to type a character in a text box.

If you have chosen the option Fixed Width rather than Delimiter, then you would be able to set Column Breaks. To insert Columns breaks in the data preview window, click on the desired position. To delete a column break, double click on it. To move a column break, you can click and drag it to the new position.


Step 5: The third screen would allow you to set the data type for the new column that would be populated with the data which is created at the end of this wizard. The default option is General which converts numeric values to numbers, date values to dates, and all remaining values to text. Other available options are Text and Date. You also have an option that does not import the data to a column.


Step 6: Press Finish and you will be able to see the First Name and the Last Name in new columns.

Get the First Name And Last Name Using Formulas

Level of difficulty: LOW

Excel’s Convert Text to Column

There are no direct and specific formulas in Excel to convert the Full name into first name and last name. However, by combining multiple excel formulas we can achieve this.

To get the first name

Using LEFT and SEARCH formulas you can build a formula to extract the first name. Below is the syntax

 Syntax : = LEFT(CELL ADDRESS, SEARCH("", CELL ADDRESS)-1)


Do replace the CELL ADDRESS in the above syntax with the actual value as shown in the example below:

=LEFT(CELL ADDRESS, SEARCH("",CELL ADDRESS)-1)


According to this example, the full name is present in Cell B2. When we type this formula in the Cell C2, it would generate the first name.


To get the last name


Using RIGHT, LEN, SEARCH formulas you can build a formula to extract the last name. Below is the syntax


Syntax : =RIGHT(CELL ADDRESS,LEN(ADDRESS) -SEARCH(" ",CELL ADDRESS))



Do replace the CELL ADDRESS in the above syntax with the actual value as shown in the example below:

=RIGHT(B2,LEN(B2)-SEARCH("",B2))

According to this example, the full name is present in Cell B2. When we type this formula in Cell D2, it would generate the Last name.


How does it work?


The formula searches for space or comma in the cell’s text. If space or comma is found, all the text found on its left is extracted and displayed as the first name. The last name is displayed by extracting all text to the right of the space or comma.


Get the First Name And Last Name Using Macro

Level of difficulty: MEDIUM

Using a macro, you can create user-defined functions. You can then use these functions in your Excel sheets. This will help you attain your goal using a single function. The cell containing the full name should be passed as the parameter to these user-defined functions.


Read More
13
Jul

Underrated Excel Functionality Which We May not Use Daily

Excel is incredibly adaptable and these prompts it to the most well-known software available in the market today. This inescapable use is all the more frequently than not prone to prompt circumstances where you need to discover some of the underrated functions used in Excel. In this article, we walk you through the top 5 underrated excel functionality which we may not use daily. Here are the top 5 underrated excel functionality


1. Paste Special

Grabbing (ie copying) data from one cell and pasting it into another is one of the most common actions in Excel. But there’s a lot that gets copied in a regular grab (formatting, value, formula, comments, etc) and sometimes you don’t want to copy all of it. That’s where Paste Special comes in.

To access Paste Special, go to the toolbar (top left on the Home tab under Clipboard), right-click or  use the shortcut Ctrl-Alt-V.

Once the Paste Special box is open you have loads of options to choose from. Here are my top three:

  1. Paste Values
  2. Paste Values and Number Formats
  3. Transpose

Paste Values paste the value of the cell you have copied instead of copying the formula. By default Excel copies over a formula, but sometimes you just want the end result. Paste Values does that for you.

The shortcut for Paste Values is Ctrl-Alt-V-V (then hit enter).

Paste Values and Number Formats is the same as Paste Values but this time it brings over the format of the the original cell. This is particularly useful when you are copying over dates or a currency but you don’t want to copy the formula from the original cell.

The shortcut for Paste Values and Number Formats is Ctrl-Alt-V-U (then hit enter).

Transpose allows you to flip rows and columns around in seconds. Turn a row of numbers vertical or vice-versa by simply copying and then using Paste Special – Transpose.

The shortcut for Transpose is Ctrl-Alt-V-E (then hit enter).



 2. Go To Special

 Like Paste Special, this hidden gem opens up a treasure trove of Excel goodies. Go To Special is located on the Home ribbon in the Editing section under “Find & Select” or you can use the shortcut – Ctrl-G followed by Alt-S.

 Go To Special allows you to select types of cell en-mass. You can use this function to select cells that have comments, formulas, numbers, text, errors and many more. Once selected you can then apply formatting, use it to find errors, or highlight certain things.

Your options on Go To Special

To give a practical example, we’ll focus on Go To Special, Blanks.

Go To Special Blanks allows you to select all the blank cells in a selected area. Say you have a huge column of data with intermittent blank cells that you want to format a different color or delete. You’d select that column, Go To Special, select Blanks and all the empty cells would be highlighted. Right-click on one and select “Delete” and the blank cells will disappear.

The shortcut for Go To Special, Blanks is Ctrl-G followed by Alt-S-K (then just hit enter).


 

3. Flash Fill

Excel developed a mind of its own in 2013. Say you have two columns of names (first and last) and you want to put them together. Type it in manually for the first row and Excel will work out what you mean and do it for the rest. That’s Flash Fill, or Excel black magic as some people say…

Flash Fill in action

Please be aware that this functionality was only introduced in Excel 2013, so is included in Excel 2013 and 2016. If it’s not working for you in those versions then make sure it is switched on in Advanced Options (File > Options > Advanced). Or you can activate it manually from the Data tab or by using Ctrl-E.



 4. Use of Apostrophe

Sometimes you type what looks to Excel like the start of a formula. If the first character is +, -, = etc, Excel treats the cell differently and starts looking for cells to refer to. This can be disorienting. Alternatively, you might be listing numbers that start with zero, like a stock keeping unit – 0000345 – etc. In this case, Excel will remove all the leading zeros giving you 345. How annoying.

That’s where our next Excel hidden gem comes in. The apostrophe (‘) is Excel’s solution to displaying data that Excel would otherwise treat differently.

With and without an apostrophe – see the difference?

One word of warning when using the apostrophe in this way: if you are entering a number, Excel will store that number as text so you won’t be able to run sums etc. on it.



 5. F2

The last hidden gem is so simple you’ll be kicking yourself if you didn’t know it. F2 activates the cell you are in for editing. So no more double-clicking then setting the cursor to the end of the text. F2 takes you straight there.



Read More
08
Jul

Why Excel is Important for Accounting Professionals ?

Microsoft Office Excel was designed to support accounting functions such as budgeting, preparing financial statements and creating balance sheets. It comes with basic spreadsheet functionality and many functions for performing complex mathematical calculations. It also supports many add-ons for activities such as modeling and financial forecasting, and seamlessly integrates with external data to allow you to import and export banking information and financial data to and from other accounting software platforms. It helps accountants in : 

Budgeting and Statements

Microsoft Office Excel ships with templates for creating budgets, cash-flow statements and profit-and-loss statements, which are some of the most basic documents used in accounting. In addition, you can download more complex budgeting and statement templates from the Office website, or purchase specialized templates from third-party vendors and install these in the application.

Spreadsheets

Performing line calculations is a basic accounting task, and Excel spreadsheets are designed to contain data in a tabular format that supports both in-line and summation calculations, replacing the need for ticker tape and special accounting calculators. The data in the spreadsheet is reusable and storable, making Excel more flexible than an accounting calculator for performing simple calculations and summations.

External Data

You can import data from many different data sources into Excel. This is especially useful for accounting as you can pull sales data, banking data and invoices from many sources into one central workbook to support your accounting activities. The data can be stored in different databases and file formats prior to importing, allowing you to access data from many different areas of your business without having to do additional data entry.

Integration

Excel integrates with many popular accounting software applications. For example, you can use the wizards that ship with your preferred accounting software package to map Excel spreadsheets to your accounting data so you can perform push and pull data operations from both Excel and your accounting package on demand.

Read More
22
Jun

Maximize QuickBooks: Excel Analysis

When it’s time to crunch numbers in QuickBooks, most users rely on reports within the software, while others turn to analytics programs. In either case, you may still sometimes need to analyze your accounting data in new ways. Pivot tables can make quick work of figuring out patterns or issues with your data that might be hard to discover otherwise. Pivot tables allow you to create instant reports and/or charts by dragging and dropping fields with your mouse. 

This means you can quickly rearrange even enormous reports into interactive, yet compact summaries.

It’s almost a given that small to mid-sized businesses (SMBs) use QuickBooks to handle their accounting.  The combination of easy-to-use and power is hard to beat.

For some businesses, the built-in reports are all the information they ever need.  For others, additional analysis is needed to do things like aggregate based on item type, location, address, or customer.  One of our early users wanted to see the total amount of each product type they shipped to each state.  Nothing crazy, but not something that QuickBooks just gives you.

Doing this sort of analysis in Excel is certainly possible.  Generally, it involves some copying and pasting followed by a pivot table.  To see a great how-to for doing it with Excel, check out this blog by David Ringstrom:

Attend this on-demand webcast "EXCEL ACCOUNTANT: QUICKBOOKS ANALYSIS PART 1" on the Excel Accountant platform where Excel and QuickBooks expert David Ringstrom, CPA, shares valuable tips to help you overcome the limitations of QuickBook's internal reports. David teaches several techniques, including how to combine two reports into one, create pivot tables from QuickBooks data, access memorized QuickBooks reports, and more.


Link: 
https://www.excelaccountant.com/selfstudy/excel-accountant-quickbooks-analysis-part-1-1593171198

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Microsoft 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Who Would Be Interested in This Course:

Practitioners who want to streamline their reports by accessing QuickBooks data using Excel.

Topics Covered in this webcast:

  • Learning QuickBooks techniques that may help you avoid the need to analyze data in Excel.
  • Creating one-click access to memorized QuickBooks reports.
  • Learning why in many cases you should export reports intended for spreadsheet analysis to a .CSV file instead of an Excel workbook.
  • Bypassing filter drop-down lists by filtering based on cell contents with a single keystroke or mouse click.
  • Using Excel’s PivotTable feature to quickly identify anomalies within QuickBooks data.
  • Tracking customer longevity by creating a report that shows the first and last invoice dates by customer.
  • Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
  • Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula.
  • Employing the Text to Columns feature to transform text-based dates into numeric values that Excel recognizes as dates.
Read More
17
Jun

Excel Formulas For Beginners

Excel formulas make calculating numbers and making sense of large amounts of data simple. By knowing a few key formulas, you can do a variety of actions in Excel that will increase your productivity and decrease the risk of making calculation mistakes. We’ve put together some Excel formulas you need to get started.

There are a lot of complicated formulas out there, but a great formula doesn’t have to be complex. In fact, some of the simplest formulas are the most useful and will help you maximize the capabilities of Excel.

1. SUM() Function

=SUM is a great basic formula to know, especially because it allows you to add up numbers in different ways. Excel easily performs this formula for you, but there are a few tricks to =SUM that provide even more functionality for adding data.

First, =SUM can add up entire rows of numbers or just certain cells within a row. Here is what that looks like:


=SUM(A2:A9) adds up values in cells A2 through A9


=SUM(A2, A9) adds up values in cells A2 and A9


But you can take =SUM it one step further and combine it with other math functions too. In this example, =SUM (A2:A9)/5 adds up values in cells A2 through A9, then divides the sum by 5.


2. MAX() & MIN() Function

If you have a spreadsheet with a lot of numbers, this is a useful formula. With =MAX you can immediately find the largest number in your data set, and with =MIN you can find the smallest.

Use =MAX(SELECT CELLS:SELECT CELLS).

=MAX(A2:A9)

=MIN (A2:A9)


3. IF() Function

With this formula, Excel will tell you if a certain condition is met. For example, you might need to know which values in column A are larger than 3. Using the =IF formula, you can get Excel to quickly auto-populate a “yes” for each cell that is larger than 3, and a “no” for each value that is smaller than 3. IF this one is not in your top 10 Excel formulas, you are missing out!

=IF(A2>=3, “Yes”, “No”)


4. TRIM() Function

If you copy and paste data into a spreadsheet, there is a chance that the pasted data will be messy. That means it might have extra spaces or hidden characters, and these will mess up formulas because Excel needs data to be clean, without those extra spaces.

=TRIM cleans up pasted data so that it is Excel friendly.

In the example below, the animals in cells A3, A6, A8, A10 and A 12 have extra spaces at the beginning. Using =TRIM(SELECT A CELL) will fix it.

You can see to the left how the formula has been used in Column D and the extra spaces have disappeared.


5. CONCATENATE() Function

=CONCATENATE is a useful formula that takes values from multiple cells and combines them into the same cell. This formula saves time and frustration when you need to combine information in multiple cells into one cell. Instead of doing it manually, =CONCATENATE can do it, in half the time and half the clicks.

In this example, Column A has first names and Column B has last names. Using =CONCATENATE(SELECT CELL, SELECT CELL) those cells can easily be combined, which is reflected in Column D, where the full names are now in the same cell. Think of all the copying/pasting this formula just saved!


6. TODAY() Function

You might find yourself needing to time stamp a spreadsheet each time it is viewed.

Instead of typing in the date manually, use =TODAY(). That’s right – you don’t even have to put a value in the parenthesis, and each time the spreadsheet is opened it will update with the current date.


7. PROPER() Function

Excel isn’t just for data analysis; it is also a good platform for organizing and sorting information. When typing large amounts of text into Excel, =PROPER is a great formula to have in your pocket because it converts a cell of text to proper case, where the first letter of each word is capitalized, and the rest of the letters are lowercase.

In the example, quite a few names in Column A are not capitalized. Instead of clicking in each cell, deleting the first letter of each name and typing in a capitalised letter (which is a lot of extra clicks and time), =PROPER does it instantly, as you can see in Column D.

Use =Proper(SELECT A CELL) for this formula.


8. ODD() Function

If you’re working with data that has a lot of decimals, this formula comes in handy. =EVEN rounds a number up to the nearest even number, and =ODD rounds a number up to the nearest odd number. If you’re working with negative numbers, these formulas still work, rounding down to the nearest even or odd number.

In this example, Column D is using the =EVEN formula and column E is using the =ODD formula.

Use =EVEN(SELECT A CELL) and =ODD(SELECT A CELL) for this formula.


9. END OF MONTH() Function

=EOMONTH can be used to find the last day of the current month, or upcoming months. Instead of going back and forth between a calendar and the spreadsheet, use =EOMONTH(START DATE, 0). Take this formula a step further and calculate the next month by adding =EOMONTH(start-date, 1).

Notice in the example how future months can be calculated by increasing the number at the end of the formula.

One more thing about this formula: when entering the start date, be sure to use the DATE function (2019,1,8) is January 8, 2019 so that the formula works currently. If the formula returns a #NUM! error, chances are that the date is not in the correct format.


10. RANDOM NUMBER GENERATOR() Function

A spreadsheet can easily be used to select random numbers using =RANDBETWEEN(SELECT VALUES).

Use this formula to choose numbers within data in the spreadsheet, as shown in the example to the left.

Another way to use this formula is to choose a winner from a list of 100 names, by having excel choose the winning row.

Here are some Excel formulas you must know, including some simple formulas that don’t have anything to with data analysis, in case you’re using Excel to organize information too.



Read More
20
May

How to become an Excel Pro?

Without doubt, an Excel spreadsheet is one of the most advanced tools for working with raw data—and one of the most feared.  The application looks complicated, way too advanced, and like something that would take hours to figure out.

I wouldn’t be surprised if upon hearing that you had to start using MS Excel, your heart started to pound. Is there any way to make Microsoft Excel less scary and intimidating? Yes. By learning a few spreadsheet tricks, you can bring Excel down to your level and start looking at the application in a different light.  We rounded up some of the simplest yet powerful MS Excel spreadsheet tips you can start using on your data.

Use MS Excel Format Painter

  1. Select the cell with the formatting you wish to replicate
  2. Go to the Home menu and click on the Format Painter. Excel will display a paintbrush next to the cursor.
  3. Using Excel Format Painter While that paintbrush is visible, click to apply all of the attributes from that cell to any other.
  4. To format a range of cells, double-click the Format Painter during step 1. This will keep the formatting active indefinitely. Use the ESC button to deactivate it when you’re done.

Select Entire Spreadsheet Columns or Rows

  1. Another quick tip– use the CTRL and SHIFT buttons to select entire rows and columns.
  2. Click on the first cell of the data sequence you want to select.
  3. Hold down CTRL + SHIFT
  4. Then use the arrow keys to get all the data either above, below or adjacent to the cell you’re in.
  5. You can also use CTRL + SHIFT + *   to select your entire data set.

Import Data Into Excel Correctly

  1. The benefit of using is Excel is that you can combine different types of data from all kinds of sources.  The trick is importing that data properly so you can create Excel drop down lists or pivot tables from it.
  2. Don’t copy-paste complex data sets. Instead, use the options from the Get External Data option under the Data tab. There are specific options for different sources. So use the appropriate option for your data:
  3. Importing Data to Excel

Enter The Same Data Into Multiple Cells

At one point, you may find yourself needing to enter the same data into a number of different cells. Your natural instinct would be to copy-paste over and over again. But there’s a quicker way:

  1. Select all the cells where you need the same data filled in (use CTRL + click to select individual cells that are spread across the worksheet)
  2. In the very last cell you select, type in your data
  3. Use CTRL+ENTER.  The data will be filled in for each cell you selected.

Display Excel Spreadsheet Formulas

Jumping into a spreadsheet created by someone else? Don’t worry. You can easily orient yourself and find out which formulas were used. To do this, use the Show Formulas button. Or you can use CTRL + `  on your keyboard. This will give you a view of all formulas used in the workbook.

Freeze Excel Rows And Columns

  1. This is a personal favorite of mine when it comes to viewing lengthy spreadsheets. Once you scroll past the first 20 rows, the first row with the column labels annoyingly disappear from view and you begin to lose track of how the data was organized. 
  2. Freezing Excel Columns Rows
  3. To keep them visible, use the Freeze Panes feature under the View menu. You can opt to freeze the top row or, if you have a spreadsheet with numerous columns, you can opt to freeze the first column.

Enter Data Patterns Instantly

  1. One great feature in Excel is that it can automatically recognize data patterns. But what’s even better is that Excel will let you enter those data patterns to other cells. 
  2. Simply enter your information in two cells to establish your pattern.
  3. Highlight the cells. There will be a small square in the bottom right hand corner of the last cell. Excel Data Patterns
  4. Place your cursor over this square until it becomes a black cross. 
  5. Then click and drag it with your mouse down to populate the cells within a columnEntering Spreadsheet Data Patterns

Hide Spreadsheet Rows and Columns

In some cases, you may have information in rows or columns that are for your eyes only and no one else’s. Isolate these cells from your work area (and prying eyes) by hiding them:

  1. Select the first column or row in the range you want to hide.
  2. Go to Format under the Home menu.
  3. Select Hide & Unhide>Hide Rows or Hide Columns. Hiding Excel Rows Columns
  4. To unhide them, click on the first row or column that occur just before and after the hidden range. Repeat steps 2 and 3, but select Unhide Rows or Unhide Columns.
Read More

Connect with Us

Copyright © 2021 Excel Accountant. All Rights Reserved.