If you are stuck on a routine task in Excel, don't rush to do everything manually. In this guide, we have compiled solutions for the most common and unconventional tasks: from exporting data from a PDF to creating loan calculators and payment calendars.

1. Data Import and Export (Connecting with other software)

You don't have to rely on copy-pasting, which often breaks your formatting, to transfer data.

❓ How to export a table from PDF to Excel?

The most reliable method (available in Excel 2016 and later): go to the Data tab → Get Data → From File → From PDF. Excel will automatically detect the tables in the document and neatly transfer them into columns without losing formatting.

❓ How to export Google Sheets to Excel?

In your open Google Sheet, click File → Download → Microsoft Excel (.xlsx). The file will instantly save to your PC with all formulas intact and working.

❓ How to export data from Bitrix24 (or other CRMs) to Excel?

Go to the CRM section (Leads, Deals, or Contacts), click on the gear icon (list settings) in the top right corner, and select Export to Excel.

❓ How to export a table from Excel to Word or Access?

To Word: Select the table and press Ctrl+C. In Word, click the arrow under the "Paste" button and choose "Link & Keep Source Formatting" — this way, the data will update automatically. To Access: Open Access, go to the "External Data" tab → "New Data Source" → "From File" → "Excel".

2. Business Templates and Project Management

Excel is a perfect tool for management accounting if you know the basic principles.

❓ How to make a vacation schedule and production calendar?

Use the NETWORKDAYS function to automatically exclude weekends and holidays when calculating vacation days. To visualize the calendar, use Conditional Formatting with a rule to highlight specific dates.

❓ How to create a Cash Flow (CF) and P&L budget in Excel?

For P&L (Profit and Loss) and Cash Flow reports, it is best to use Pivot Tables. Create a flat registry table of all transactions (Date, Category, Amount, Type), and then on the "Insert" tab, select "PivotTable" to automatically group the data by months and categories.

❓ How to create a loan calculator?

Use the PMT function. The syntax is: =PMT(rate, nper, pv). It will automatically calculate your monthly annuity payment based on the interest rate, number of periods, and total loan amount.

❓ How to make a Kanban board or Planner in Excel?

Create columns like "To Do," "In Progress," and "Done." Use the Data Validation tool to create dropdown lists in cells for changing task statuses, and color-code them using Conditional Formatting.

3. Math, Statistics, and Charts

❓ How to calculate a cube root and do other complex math?

To find the cube root, raise the number to a fractional power. The formula is: =A1^(1/3).

❓ How to create a step chart and a vector chart?

For a step chart, use a standard line chart, but in your data source, duplicate each X-axis data point to create the "step" effect (a change in value without a change in time).

❓ How to do an ABC XYZ analysis?

ABC analysis is done by sorting revenue from highest to lowest and calculating the cumulative total (using the SUM function with a locked absolute reference for the first cell). XYZ analysis requires calculating the coefficient of variation: the formula is =STDEV() / AVERAGE().

ℹ️ Pro Tip for Analysts

If you need to analyze a Pivot Table deeply, enable the Power Pivot add-in. Go to File → Options → Add-ins → COM Add-ins to activate it. This allows you to create relationships (Joins) between tables, just like in full-fledged SQL databases.