Search

Articles
25
May

Essential Excel Skills to Help You Stand Out In Your Next Interview
  • Larry Bin, CPA
  • 1438
Ask any accountant in any field which software they use on a daily basis, and the most common answer will be Microsoft Excel. The spreadsheet tool is perfect for anyone working in the finance and accounting fields due to its versatility and specific financial applications.At this point, it’s expected that you are proficient in Microsoft Excel even in an entry-level position, which was part of the AICPA’s decision to include Excel as the spreadsheet tool in the CPA Exam. Whether you’re just starting out or have been in the field for some time, being able to efficiently navigate through a data set in an Excel file and use formulas to make sense of the data should be one of your top priorities. To help you become an Excel user, we’ve put together a listing of the top 10 Excel skills you need to know if you’re going to excel in your accounting career1. Copy and PasteThis may seem like a rudimentary skill, but knowing how to copy and paste correctly can actually save you a fair amount of time. Excel has a variety of paste special options; you can paste formatting, paste the formula, paste the values, paste the validation, transpose, and various other options. If you don’t know how to use these correctly, you may spend time typing out individual formulas and values, or going through and changing the formatting of individual cells when you can simply utilize paste special.2. Sort and FilterKnowing how to accurately sort and filter is the gateway to data analytics. The sort feature allows you to sort a specific set of data by a specific column. The filter allows you to only see data lines that adhere to your filter or filters. This is perfect for accountants who work with large data sets and need to narrow them down to extract the correct information.3. Using the “Formulas” TabWhen you first start using Excel, the number of formula options available for data analysis can be overwhelming. Excel helps you to figure out what you need to use via the Formulas tab on the toolbar. This tab lets you search through every Excel function, as well as show you the most used formulas. It explains what a specific formula is used for and even walks you through how to set the formula up. It’s a great resource when you’re first learning the ins and outs of Excel formulas and is even useful once you jump into advanced formulas.4. Cell Reference FormattingCell referencing is referring to a specific cell in another cell or formula. Knowing how to reference cells correctly ensure your formulas and derived numbers make sense on your Excel spreadsheet. There are four types of cell references:A1 – Keep both row and column moveableA$1 – Keep row constant and column changeable. This helps when you want to drag a formula down a column.$A1 – Keep column constant and row changeable. This helps when you want to drag a formula across a row$A$1 – Keep both row and column constant. This helps when you want a formula to always reference a certain cell.5. Using F4 to Repeat the Last ActionThis is a nice timesaver that allows you to repeat the last action you took in a new cell or group of cells. For example, if you highlighted a cell yellow, you can select another cell and select F4 to highlight that cell yellow. As an accountant, you’ll constantly be updating the format of cells, adding formulas, and generally working toward the most readable and understandable format within your Excel sheet. This shortcut will help you quickly change things in your sheet without having to use your mouse or toolbar.6. Conditional FormattingThis allows you to set specific formats for the value within a cell. For example, if you’re trying to see if you have any blank numbers in a sales tax column, you can add conditional formatting to the column to say “if cell is empty, make cell color red.” Anomalies will easily stand out and you’ll quickly identify if something is wrong in your sales tax column. Conditional formatting can be used for a variety of purposes and knowing how to use it will help you quickly identify specific values in a data set.7. Graphs and ChartsVisually analyzing data is very helpful for accountants, especially if they’re presented with large data sets. While a table can show a vast array of data, a graph and chart can show what the data actually means; trends may surface that wasn’t easily identifiable in a table format. Accountants who are proficient in using graphs and charts in Excel can visually communicate data both internally within the organization and to clients in a more effective way.8. VLOOKUPWhen I started my first job in public accounting, it became increasingly clear that I needed to learn how to use a VLOOKUP. This formula helps you find data in a range of cells by row. For example, say you have a large data sheet with names and CPA Exam scores on it. You can use a VLOOKUP to find a specific score for a specific candidate on a specific exam. The basic formula looks at one field, but you can nest VLOOKUPs into SUMIF formulas or add field criteria to make them even more useful. This formula is especially helpful for analyzing large data sets, which is done in a variety of accounting positions, from data analysts to auditors.9. Pivot TablesOften considered an advanced Excel skill (but used often by accountants), pivot tables take your data and organize it for you so you can organize it in a variety of data tables. You can sum specific data, put specific line items in rows or columns, and only include the data you need. Pivot tables allow you to take a huge sheet of data and turn it into a readable format that allows you to draw conclusions from the data.10. Keyboard ShortcutsI was always jealous of colleagues who could use keyboard shortcuts; they could navigate between sheets, re-format cells, and add formulas without lifting a finger to put on the mouse. The best part about keyboard shortcuts is they increase your efficiency and reduce the time it takes you to do a task in Excel. As accountants, we work in Excel pretty consistently every day. Using shortcuts will help you cut down on the time it takes to complete tasks while also making you the envy of all of your friends.If you haven’t jumped into mastering MS Excel skills, now is the perfect time to start. Excel Accountant offers a wide range of NASBA Approved Excel Courses, which can be purchased as a standalone course. Either way, Excel is a powerful tool, and learning it will be invaluable to your career. Taking a course now will help you become an asset in any accounting profession as you continue or start your accounting career.
  • Read More
19
May

Must Have Excel Skills for Accounting & Finance Professionals
  • David H. Ringstrom, CPA
  • 2489
If you work in the accounting or finance fields, you are almost guaranteed to spend some time in Microsoft Excel. This quintessential spreadsheet program has remained relevant for decades, mainly due to its flexibility, versatility, and user-friendly design. Excel can also provide data-driven insights that can help facilitate business decisions and client recommendations. Plus, it has calculation capabilities built-in, making it extremely convenient in a wide range of scenarios. However, to work as an accounting or finance professional, you do need to understand some of Excel’s more advanced capabilities. If you want to ensure career success, here are a few Excel skills you must have.Formulas and FunctionsExcel’s mathematical capabilities are what helped the program rise to prominence in the fields of accounting and finance. Having a firm grasp of the available formulas and functions is a must for accounting and finance professionals. If you aren’t comfortable with them, it’s wise to increase your skill level to help you advance your career.Pivot Table AnalysisPivot tables and pivot charts allow accounting and finance professionals to gain meaningful insights from data. They can access multiple information sources and support a deep dive into the data. Being able to perform a pivot table analysis should be seen as an essential Excel skill for anyone working in a finance-oriented role.Charts and VisualizationsAt times, trying to derive meaning from rows and columns full of numbers is a challenge, particularly if you are dealing with a substantial amount of data. Charts and visualizations help make the information more accessible by creating a graphical presentation of the data. This not only helps with analysis but also with showing the data to another party, especially those who may not be as familiar with financial concepts.Conditional FormattingWith conditional formatting, you can adjust the look of particular cells based on whether they meet certain conditions, making it easier to track critical details or examine the spreadsheets for specific information. For example, outgoing payments (represented by negative amounts on a balance sheet) could be displayed in red, meaning the number is negative. Similarly, approaching due dates can be color-coded or bolded to draw attention to the cells.Ultimately, all the Excel skills listed above should be considered must-haves for any accounting or finance professional. If you would like to know more or are seeking new employment opportunities, the team at The Squires Group can help. Contact us to discuss your career goals with one of our knowledgeable staff members today and see how our accounting and finance industry expertise can benefit you.
  • Read More
11
May

How to Separate First Name & Last Name from Full Name in Excel
  • David Ringstrom
  • 788
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 GUILevel of difficulty: LOWExcel’s Convert Text to ColumnExcel’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) Tab2) Semicolon3) Comma4) Space5) OtherIf 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 FormulasLevel of difficulty: LOWExcel’s Convert Text to ColumnThere 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 nameUsing LEFT and SEARCH formulas you can build a formula to extract the first name. Below is the syntaxSyntax : = 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 nameUsing RIGHT, LEN, SEARCH formulas you can build a formula to extract the last name. Below is the syntaxSyntax : =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 MacroLevel of difficulty: MEDIUMUsing 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
03
May

Underrated Excel Functionality Which We May not Use Daily
  • David Ringstrom, CPA
  • 1943
Excel is incredibly adaptable and these prompt 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 functionality1. Paste SpecialGrabbing (i.e 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:Paste ValuesPaste Values and Number FormatsTransposePaste Values paste the value of the cell you have copied instead of copying the formula. By default Excel copies over 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 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 SpecialLike 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 to format, use it to find errors, or highlight certain things.Your options on Go To SpecialTo 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 FillExcel 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 actionPlease 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 ApostropheSometimes 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. F2The 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
27
Apr

Why Excel is Important for Accounting Professionals ?
  • Fedrica Hanks, Accountant
  • 1587
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 StatementsMicrosoft 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.SpreadsheetsPerforming 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 DataYou 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.IntegrationExcel 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
19
Apr

Maximize QuickBooks: Excel Analysis
  • David Ringstrom, CPA
  • 438
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 "
  • Read More

Connect with Us

Copyright © 2024 Excel Accountant. All Rights Reserved.