Search

Articles
31
Dec

9 Amazing Excel Tips for CPAs in 2022
  • David Ringstrom
  • 768
There is no denying that accounting, tax, and finance professionals make good use of excel spreadsheets. If you are CPA, one of the most important skills to master is Microsoft Excel.Microsoft Excel tool is essential to our ability to manage complex data sets and communicate large amounts of information to clients and colleagues. And, it is a fact that most of us only ever use 10 percent of what Excel has to offer. Thinking of why I'm saying this statement?Financial data is what you deal with as an accountant, financer, or tax preparer, and you need to be good at managing and analyzing data.Isn't it right? Yes, it is! But how?Microsoft Excel is the tool you need! You can become a pro in Excel even if you only use basic functions or create pivot tables, workbooks with many references, and macros. Here are a few outstanding excel tips you can follow.Inserting a line break within a cellMaking your presentation of data clear is imperative when using data for communication. However, Excel falls short in this area. You can give your data some breathing room by inserting a line break within a cell. Your clients or colleagues will appreciate it when you do this.ALT + ENTER for PCOPTION + ENTER for MACManipulating rows and columns It is a waste of time manipulating rows and columns through dropdown menus. Instead, use these two shortcuts to quickly select the required portion.Select a row with shift + spacebarSelect a column with ctrl + spacebarBoth are for PC and Mac as well.Apply Currency FormatThere are a lot of formatting shortcuts you can use, but when it comes to handling money, you should use Ctrl + $.CTRL + $ (SHIFT + 4)You can use it on PC and Mac as well.To edit within the cell:Making changes to a cell by double-clicking is frustrating. In most cases, typing over the entire cell results in no change.Use:F2 in PCControl + U in MacSave yourself from the trouble!Archive information in multi tables worksheetsInformation can be archived in multi-tabled worksheets. If you have a lot of data, you must be able to navigate through it quickly. A workbook's tabs can be cycled through with a quick press of the key combination Ctrl + Tab.Move from Tab to tab using ctrl + Tab on PCMove from Tab to tab with option + Tab in macInsert an auto sum formulaAutoSum is a simple formula that saves a lot of time. To use this formula on the Mac, click Command + Shift + T (Command + Alt + =).Alt + = For PcCommand + Shift + T For MacApply The Countif FormulaGet to the bottom of your data with the handy =COUNTIF formula. Using this formula, you can find out how often something happens. Let’s take an example:Say you'd like to know how frequently your company's sales total exceeds $2,000 each day. This code works with a list of days in column A and a total in column B: = COUNTIF (B1:B31,">2000").Toggle Between Formulas And ValuesIf you are working with nested formulas in Excel, it is very easy to lose track of where you are in the process. However, you can figure out where you made a mistake by pressing Ctrl + * in any selected cell. Then, press this combination again to return to the values view.WITH CTRL + ~This is for both PC & MACSaving Your SanityThere is no doubt that this is the most significant function on this list, and most users are familiar with it. Those of you who use Excel know how prone it is to freezing. The chances of crashing increase as your workload increases, your deadline gets tighter, and the amount of work you've done since your last save.CTRL + S for PCCOMMAND + S for MACIt would be a great favor if you saved your work frequently. You do so already.Do you want to learn more about these tips or know more about other excel tips? You can attend live webinars or register and subscribe to attend paid or free study materials only at Excel Accountant.
  • Read More
12
Nov

Use Excel Macros 4.0 Often? You could be vulnerable to cyber Attacks
  • David Ringstrom
  • 99
Since 1992 Macros have been a part of Excel. Excel 4.0 macro or XLM 4.0 macro as it is otherwise known is a piece of its program that helps automates repetitive tasks in your spreadsheets. But, it is unfortunately a covert backdoor which has been often used for malware delivery. It has been increasingly used by hackers to store hidden excel malware as perpetual techniques of attack. What adds to the vulnerability of Excel 4.0 macros is it being an essential component of Excel’s core capability. Macros are so much embedded into regular business applications that they are very less likely to be disabled. Malware authors often exploit this fact and attempt sneaking malicious payload via macro codes into Excel sheets and deliver it mostly as email attachments. The threats around .IQY files .IQY files are not Excel spreadsheets, but rather text files associated with Excel. These text files store web query instructions for retrieving data from Microsoft Excel. Thus, Excel will launch if you open a .IQY file from an email attachment. These files are configured to download a PowerShell script that can in turn discreetly download malware. Start of the trend of Macro Attacks It was discovered by a number of cyber criminals towards the end of January 2020, and they launched a barrage of attacks (beside other techniques) during Mid-February targeting accountants. An infected sheet would land in your inbox, which contained malicious commands hidden in a formula. Upon downloading, the victim would be asked to click the ‘Enable Editing’ button. That triggered the malicious macro. Following the first attack, the threat actors continued to leverage this evasion technique to create more attacks that peaked between May to July 2020. The Stealth Technique There is a loophole in the form of Macros hidden in Excel Sheets. That is, the sheet is not readily accessible via the Excel UI and cannot be revealed without the help of an external tool. The hidden macros can be triggered through web queries or can be downloaded when a specific formula is executed. This loophole has been leveraged repeatedly to deliver malicious payloads via file uploads or email attachments and exploit system vulnerabilities to create new attack vectors. The cyber criminals leveraged the fear-based social engineering ploys, most commonly impersonating John Hopkins Center, sending emails with a subject “WHO COVID-19 SITUATION REPORT”. The attached Excel files contained a hidden malicious macro that downloaded and ran NetSupport Manager RAT — an administration tool that allowed remote access. The victim was compelled to open and facilitated the attackers to gain remote access and run commands on compromised devices. In effect, the virus would even email itself to everyone within an affected user's Outlook contact list in some cases. This technique was so abused that Microsoft had to issue a public warning against it. How to protect against Malicious Macro Files With the rise of the Internet malware creators moving on from pedestrian Office documents to other methods for distributing viruses and malware, Microsoft has added security precautions to Excel. Using Protected View in Excel to safely open documents of uncertain provenance is the best way to protect yourself from such malware. Depending upon your settings, files you open from the Internet or email attachment may automatically launch into Protected View. When this mode is enabled you can safely view the spreadsheet, but you won’t be able to edit it until you click Enable Content. Similarly, any sort of external data connections are also disabled while the workbook is displayed in Protected View. Migrating to VBA Being aware of these exploits, Microsoft has been encouraging users to shift to Visual Basic for Applications (VBA). The Antimalware Scan Interface (AMSI) paired with VBA can provide deep scrutinization of the macros’ behaviors in VBA, enabling the system to scan for suspicious macros and other malicious activities at runtime. Integrate AMSI with MS Office Antimalware Scan Interface (AMSI)'s integration with Office 365 applications enables runtime scanning of macros, exposing malicious intent even with heavy obfuscation. This latest improvement to Office 365 allows modern endpoint security platforms like Windows Defender ATP to defeat macro-based threats. The best possible way to keep yourself protected is to avoid downloading anything that comes from an untrusted source. In addition to that, consult a Cyber security or an Excel Expert from time to time to keep yourself updated with the latest trending security updates in the business world.
  • Read More
29
Oct

Excel at Excel with 13 awesome tips!
  • David Ringstrom
  • 99
Microsoft Excel is an indispensable tool for all accounting and tax professionals. Even though you may be aware of the basics of Excel, having some quick shortcuts can be quite handy. It can save you considerable time and rocket your efficiency. We have gathered 13 tips that will prove helpful to you. 1. Auto-fill cells The power of Excel goes beyond just shortcuts. Its agility is based on its hyper functionality to follow what you type, for instance in the column to the right of your data. It then gathers suggestions to fill the rest of your cells after you have typed some examples. This saves you from the onerous task of info-typing. You need to hit Enter to accept Excel’s suggestions. 2. Spreadsheet Shortcuts There are plenty of formatting options when it comes to spreadsheets. It enables you to add special formatting without much effort. You can use the format painter function to mirror that formatting on other cells. You simply need to double-click on the format painter icon to lock the function and then single-click on any part of the spreadsheet that you want to format. Hit Escape to unlock the cursor. There are some nifty shortcuts if you have to deal with large data. Give these a shot: Format numbers to include two decimal places: Ctrl+Shift+1 Format as time: Ctrl+Shift+2 Format as date: Ctrl+Shift+3 Format as currency: Ctrl+Shift+4 Format as percentage: Ctrl+Shift+5 Format in scientific/exponential form: Ctrl+Shift+6 3. Highlight Data using Sparklines As some of you may already know, Sparklines come as a built-in feature. With the help of Sparklines you can display small charts inside individual cells such as- line charts, bar charts, simple win/loss charts and so on. To create one, select the range of numbers you want to display, go to the Insert menu and select the chart option you want. Then, choose a location range along a single row/column in the same worksheet. With Sparklines, you can display trends in your data in a compact and neat manner. 4. Splice data easily You can filter data with ease with this function. To do so, select any range in a table or PivotTable, and then go to Insert > “Slicer,” in the top right corner. Then, select the column you want to filter by. 5. Manipulate data with pivot tables Pivot tables are a great tool when dealing with intricate and large data. Not only do they allow you to analyze data, but you can also detect patterns and make comparisons with these interactive tables. To make a pivot table, simply go to the Data menu and select PivotChart Wizard or PivotTable. The wizard helps you select the data in your PivotChart and format it in a convenient way. 6. Awe‘sum’! A feature that brought respite to every Excel user. It gives you the total of a data set without the need for an equation. You need to simply click anywhere in the tables and press Ctrl+Shift+T. You will be furnished with the total of the total row that you created. 7. Check formulas and results with ease When dealing with a large spreadsheet, you may want to check some formulas swiftly. To rapidly shift between the cell data and formula, press Ctrl+(~) keystroke. 8. Use the status bar without a formula You can check the statistics of data in a table without typing any formula. All you need to do is select the cells you wish to see the stats on. Look at the bottom right of your window and you will find instant stats of the same. 9. Hide zero values Often, users wish to hide zero values when dealing with huge data sets. Fortunately, Excel offers the option to hide zero values. Go to ‘File’ and choose ‘Options’. There, go to Advanced and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”) 10. Excel templates All versions of MS Excel come with many templates. To create an amortization table with a template, right-click on a blank worksheet. Gain access to it with ‘Insert’. Under ‘Spreadsheet Solutions’, there are several templates, including amortization schedule. Excel also offers you the option to make your own template by saving a worksheet as an Excel template. 11. Instant Chart To make an instant chart, just select the cells you want to include and press Alt+F1. 12. Find the worksheet you want You can get a list of the most recently used workbooks by going to ‘File’ or ‘Office Button’, depending on the version you use. There, you will find ‘Recent’. This will save you time which would have otherwise spent looking for the worksheet you want. Furthermore, in the ‘Advanced’ section, go to ‘Options’ and you can alter your view as well as adjust the number of recent documents shown. You may want to change the number to 50 for more convenience. 13. Create tables in a jiffy First, click anywhere in your data. Then press Ctrl+T. This will produce an instant table displaying your data. These tips and shortcuts can definitely help you become a wizard of Excel. Share them with your colleagues and friends and help them up skill too!
  • Read More
22
Oct

Why do accountants love Excel?
  • David Ringstrom
  • 99
‘Excel is an accountant's best friend.’ That’s no surprise given how much accountants rely on Excel to get their job done! Technology has transformed every aspect of our lives and accounting too is not left untouched. Arrival of computers heralded a new dawn for accountants. That’s because before computers, accountants would do calculations manually- with pen, paper and a calculator. But now, thanks to Microsoft Excel, complex and large accounting work (processing data, calculating heavy algorithms, creating spreadsheets etc.) happen much rapidly. Besides accounting departments, Excel is a hot favorite even with marketing, HR and management departments. Even those who don’t need it have at least heard of it or used it in school. MS Excel is ubiquitous on almost all computers but for accounting professionals, it plays an active role in daily operations. Why do accountants swear by Excel? The job of any accountant in a small or big company/ firm involves making complex calculations, storing large financial data and comparing it. They need to report such information through accurate graphs, charts and spreadsheets, as desired by the employer or client. All of these tasks can be swiftly performed on Excel. It is especially used in budgeting and planning Let’s take a look at what makes MS Excel accountants’ all weather friend. Easy to manage There are numerous functions in Excel that make handling and storing massive amounts of data effortless. These functions can be easily learned online. Furthermore, there are several kinds of charts, graphs and templates available. You can easily access that stored data years later too. Not only this, you can also collate different data sets to compare or predict. This is why even financial analysts choose MS Excel for financial risk management and investment tracking as well. Such is the prowess of Excel that virtually any action with respect to financial consequences can be executed accurately. Market Leader The financial analyses you must have seen in the form of spreadsheets, graphs and charts were most likely done using Excel. Such is its market dominance that you will find a plethora of online courses that teach you how to use Excel more efficiently. They are delivered by experts live or pre-recorded on a free or paid basis. Universally known Given its varied applications, a large number of schools include Excel in their education curriculum. So that even before you get your first job, you are already familiar with this program. All it takes then is how much deeper you delve into the world of Excel functions, tips and tricks. Amazing, but affordable Excel is cheaper than its competitors, but this does not mean it lacks any features. In fact, its detailed functionality and ease of use are second to none. It is perhaps the most consummate spreadsheet software available. Marvelous Macros Excel offers great help for large and complex accounting. With ‘macros’ feature, you can create automated calculations and other actions. It takes care of doing all the heavy calculation, data entry or creating datasets with the click of a button. You can also save it and apply as and when you need.
  • Read More
12
Oct

Why Does Investment Accounting Needs More Than Excel?
  • David RingStrom
  • 342
Excel is widely used by accountants daily. But that does not mean it is perfect! We have a whole another blog explaining pros and cons of Excel. Excel is used to store and analyze large sums of data. Its flexibility, agility and usability have made it popular. But the bottom-line is, Excel isn’t a specialized accounting program! There are some nagging issues when it comes to using Excel for accounting purposes: Lack of verification mechanism to assess accuracyNo double entryPoor numerical organization Limitation in validating the data Despite being the go-to tool for spreadsheets, it leaves a lot to be desired. Using it for complex accounting requires manual entry from many files and too many workarounds. Even though it offers pivot tables and cross-tab functions that aid organizing and processing data, it doesn’t help much if a number comes up wrong. It becomes cumbersome to track the wrong number and its underlying information. To put it succinctly, a spreadsheet is a list. It depends totally on manual input. As is the case with all things manual, it is prone to errors. Worse still, there aren’t efficient tools to locate the mistake. When/ if there is an issue, you will have to look for the right line in the right tab in the right spreadsheet. This will consume too much of your essential time. Additionally, it does not offer robust security controls or multi-level user verifications to ensure a secure interface. A specialized software is the need of the hour for accounting work. Such a software is designed to work with databases and organize information to help you process and manage data efficiently. These accounting software utilize the processing power of your computer and perform complex calculations and data manipulation with stunning accuracy and agility. They also accommodate direct feeds from numerous third-party systems to allow automated download of information, thereby, eliminating manual entry and vastly ameliorating the accuracy. What is more, they provide reporting and security options that Excel cannot compete with.A database driven accounting solution offers the following 3 advantages vis-a-vis accounting on MS Excel:Better recordsLocating, storing, documenting and reporting information should be as swift as possible to respond efficiently to client requests as well as meet complex regulations and tax requirements. That is imperative for effective accounting. Working on Excel requires you to sift through several tabs and files to find the information you want. With specialized accounting software driven by a database, you can do it effortlessly.Faster answersUndoubtedly, you know the importance of a quick response to a client’s query. With MS Excel, you may find yourself in a spot if you need to run a new report, locate the data in an existing report or sort the data differently. Here, a database software beats Excel hands down as it comes equipped with the tools to segregate the details and answer the query with all the facts and figures without breaking a sweat. This turnaround time will surely leave your clients awestruck and put you on a higher pedestal when it comes to customer satisfaction.Leverage compatibility with other technologiesLike every accounting firm, you too would be aware of the importance of easy export of data to software such as a general ledger, CRM, OMS or cash management system. This is vital to keep everyone involved on the same page. A database driven software works lucidly- it allows information to flow in as well as out using whatever form works best.Relying on Excel makes you work harder by executing unnecessary tasks that can be easily taken care of with efficient accounting software. Technology you employ for accounting should be compatible with existing processes to streamline work.
  • Read More
01
Oct

Pros & Cons of Excel for small business accounting
  • David Ringstrom
  • 299
A lot of businesses worldwide, especially the newly launched ones rely heavily on the ensuing accounting process. To them Excel is a tried and tested software that is trusted by accountants and is very extensively used day in, day out. This is a blog on Excel, where we will take a thorough look at MS Excel and examine both sides of the coin, its benefits and the underlying limitations. Ranging from basic bookkeeping to storing financial data such as sales, revenue, purchases and expenses Excel finds its usage in almost every day to day accounting activity in an office. It can also be used to store other employee level accounting data. Needless to say, it makes lives much easier with its wealth of formulae and macros to execute complex calculations and present the data in the form of charts & graphs. Owing to its versatility, it is always expected from seasoned as well as budding accounting professionals to have expertise in Excel. The popularity and importance of Excel can be gauged from the sheer number of courses that are available online. They are delivered by experts who train people to use Excel more efficiently and helps you polish your skills.First, let’s see why Excel is used widely for small business accounting. Convenience in Organizing Data It allows users to conveniently store data in spreadsheets in a structured format. With time, Excel has evolved so much that now it can provide the option of using pie charts and graphical representations to show stored data and accounting results. Macros and Formulae As an accountant, you might have certainly used the Excel’s ‘macros’ feature. It makes updating a spreadsheet a lot easier. You can record your calculations using this function and apply them elsewhere simply with a click. This is a life saver when dealing with repetitive calculations, for instance updating your report every month with financial data changes. You don’t need to change the whole report every time with this wonderful function. Excel macros and formulae reduce the need to intervene manually. Yet another nifty feature is ‘what-if’ which is effective for forecasting budgets based on user preferences. Forecast Sheets Even better than the ‘what-if’ function is ‘forecast sheets’ which enable the users to estimate the upcoming trends by drawing upon historical data from over the past years. It can easily predict current or future cash flow volume. Customizable Book-Keeping Templates Excel offers the ability to create book-keeping templates for your unique requirements of receipts, invoices, and other reports that you need quite often. It saves considerable time & efforts as these templates can be reused as per your needs. Vast Array of Templates Microsoft Excel comes equipped with a huge library of invoices, ledgers, reconciliation, budgeting, and other accounting trackers. You can incorporate them as per your needs with a click. Compare Datasets with Ease You can make comparisons between different data sets quickly and easily. Excel makes it hassle free to compare financial datasets between two periods to calculate income such as analyzing year-on-year and month-on-month data. Moreover, you can compare customer base across geographies in case of a retail or reseller small business and analyze several factors like age, purchasing preferences, spending capacities, etc. This will surely give you a closer view on how your company/ organization is performing. Affordable A small or recently established business will find it difficult to manage the price of a specialized accounting software. MS Excel comes to their rescue as it comes bundled in MS office package. It does not need annual renewal, and works on several different platforms without even needing internet connection. Sounds like an ideal software after reading the benefits of Excel? Sadly, nothing in the world is perfect and Excel has its limitations too.There are some challenges that come with using ExcelCan be Complicated As you can surmise from the myriad of online courses, webinars and e-books delivered by a plethora of instructors, using Excel optimally requires one to master several formulae and syntaxes. Executing the macros function perfectly takes one to gain a certain level of expertise which many find cumbersome. Not only that, an error in data or method will ruin the whole report. Difficult to Collate Data Typically, a firm receives data from numerous sources, often from third-party vendors too. Tracing and assembling data can be time consuming. Worse, if information provided by an individual is inaccurate, then the whole consolidated report can be compromised. Security Some of your spreadsheets may contain financial information that you would want to keep protected. For such sensitive data, Excel does not provide enough security measures. Challenging to Collaborate Collaborating with people separated by geography and departments can be inconvenient especially when working on planning, forecasting, and budgeting reports simultaneously. Typically, a spreadsheet ends up being attached and forwarded to many recipients over email. This curtails freedom and agility. When time is of the essence, this can prove a severe handicap to decision making. Not Ideal for Big Businesses Diversified reporting requirements are not easily met by Excel. We have a whole other article to explain just that. For more automated and complex accounting, it comes up short in functionality Regardless of where you work, accounting, business, accounting business, or business accounting, you have to take into account all of these pros and cons. Maybe Excel is not as sophisticated or high end, and thus it cannot match up to a specialized, automated accounting software, it is however probably the most sought after tool that forms the foundation of creating every financial report that you can think of. Such software provides scalability, robustness, dynamism and will leave you with more time on hand. On the other hand, if you can afford the cost of such accounting software, it will be more reliable than MS Excel.
  • Read More

Connect with Us

Copyright © 2024 Excel Accountant. All Rights Reserved.